data sources for subscriptions, linked reports, how it can all wor

I think I must be missing something here...
I, as the developer, create Report1 and Report2: they use DataSouceA.
DataSourceA is set to use windows integrated security.
My on-demade reports run fine. The username (selected from the db, not from the Globals collection) is used to filter out options on the parameter screen, so users can only see data in their respective division.
Now: I want my USERS to be able to schedule reports.
I am able to schedule reports by taking Report1 and Report2 and either customizing the data source or pointing it to a new data source: BUT I AM DOING THAT FOR ALL USERS who are on the system!!! Now, anyone who goes to run Report1 or Report2 does not need to have any security to access the database, they are all using my id!
If I create a LINKED report, you do not seem to have the option of changing the data source...it is not even on the left menu anymore. '
So: I am confused. If I have to set up all reports to used a saved data source (lets just say it is my username and password), what is the sense of having any security? I must be missing something here.
What I want is the NT integrated security for on-demand. Then if a user has the ability to schedule reports, they would need to make a linked copy of the report, change the data source (and remember there is no way to copy a data source, so my users will have to ALL be trained on creating them - which seems a bit odd) to point to a datasource with stored credentials (preferrably their own login and password), and create a schedule.Unfortunately there is not a good solution for this in V1. Integrated
reports can not be scheduled. We are hoping to change this in a future
version.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"asc_rmc" <asc_rmc@.discussions.microsoft.com> wrote in message
news:9FD821AD-F312-473A-B20F-FD639B3D75FA@.microsoft.com...
> I think I must be missing something here...
> I, as the developer, create Report1 and Report2: they use DataSouceA.
> DataSourceA is set to use windows integrated security.
> My on-demade reports run fine. The username (selected from the db, not
from the Globals collection) is used to filter out options on the parameter
screen, so users can only see data in their respective division.
> Now: I want my USERS to be able to schedule reports.
> I am able to schedule reports by taking Report1 and Report2 and either
customizing the data source or pointing it to a new data source: BUT I AM
DOING THAT FOR ALL USERS who are on the system!!! Now, anyone who goes to
run Report1 or Report2 does not need to have any security to access the
database, they are all using my id!
> If I create a LINKED report, you do not seem to have the option of
changing the data source...it is not even on the left menu anymore. '
> So: I am confused. If I have to set up all reports to used a saved data
source (lets just say it is my username and password), what is the sense of
having any security? I must be missing something here.
> What I want is the NT integrated security for on-demand. Then if a user
has the ability to schedule reports, they would need to make a linked copy
of the report, change the data source (and remember there is no way to copy
a data source, so my users will have to ALL be trained on creating them -
which seems a bit odd) to point to a datasource with stored credentials
(preferrably their own login and password), and create a schedule.
>sql

Data sources and deployment of packages

I notice that when deploying a package that uses data sources behind its connection managers, the deployed package has the connection string built within the package that gets deployed.

From a previous post from Kirk - "What I'd suggest is that you create a Data source per machine and have all the connections reference it. You'll still need to create a connection manager in each package, but they will all reference the same data source. Moving to different machines is no problem because you can have a "machine resident" data source file that properly points to your "ETL Server"."

How are data sources defined for a machine? Is this simply via one of the configuaration options (i.e. an .xml file) that are loaded upon runtime by a package that is built to reference that config, which then trumps the connection string defined within the package?

ThanksDatasources are a design time feature. The connection manager references the DS and caches the connection string so that even if the datasource is no longer available, the connection will still be viable.

For cases where you need to deploy to a location where you will not be opening the package in the designer, and you need the connection string to change, you'll need to configure the connection manager in the package. The data source will only modify the connection in the designer.

The point of the earlier post was that if you're designing multiple packages and you wish to have all the connections in those packages pointing to the same server, you can create datasource and reference it by the DataSourceID in all the packages. If the server changes, you can modify one data source and it will modify all the connections for you. Next time you open the packages, they will pick up the new connection string. Moving the package to a different machine is also no problem so long as there is a data source available on the target machine with the same DataSourceID.

Configurations are your first line of defense for making packages location independent for easier deployment and your best option for packages that won't be opened in the designer. The DataSource approach is just a one off thing you can do in some cases.

K|||

>> Moving the package to a different machine is also no problem so long as there is a data source available on the target machine with the same DataSourceID.
>> Configurations are your first line of defense for making packages location independent for easier deployment and your best option for packages that won't be opened in the designer.

When I'm talking about deployment, I'm thinking test/prod etc where I'll never need to open it in Designer. That's what the dev env is for. Isn't it?

Given that, package configurations look like the "only option" and not just a "best option", and "only line of defence", not "first line of defense". Isn't that true? Basically, if I don't plan on opening my packages in Designer, then I have to go through the painful process of configuring each package separately and entering values for each package at deployment time. Is there an alternative?

Thanks,

RV.

|||

We are required to do a silent install in production at client sites, ie. no DBA or designer access. In this case, we develop in our environment and may would have to deploy each package with its connection strings. This may impact our design to minimize the number of packages.

Any thoughts?

|||

Use configurations. For each site you will need to adjust the configuration data store to hold the correct values for that site, and how you that depends on the store you use. You could write a wizard where the person running the install simply supplies the values and these are written to the correct location. Your packages will already be configured to read their information from configurations so everything should be sweet. Indirect configurations may be easier, and the values set by the install wizard.

Some links-

http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/10/31/2336.aspx

http://msdn2.microsoft.com/en-us/library/ms141682.aspx

http://www.sqljunkies.com/WebLog/knight_reign/archive/2004/12/07/5445.aspx

|||

I went through all this frustration myself and eventually settled on a solution that works very well. In a nutshell, I store my connection strings for all my connection managers in the database.

1) All my connection managers are of type OLEDB

2) Every package that has a connection string has package configurations enabled with a Configuration entry of type SQL Server that points to a Configuration table in the database. This entry is the way the connection manager gets the connection string at runtime. In the Configuration entry, it is the Configuration filter setting which will differentiate the different entries in the database pertaining to different connection strings.

3) My deployment scripts insert the correct database connection info into the configuration database for each connection. The connection strings are dependent on the environment I'm deploying to. The connection managers in the solution will at runtime grab the value from the database via the package configurations on the package the connection managers live on.

4) I deploy using dtsutil and make it silent. No need to not have it silent.

Data sources and deployment of packages

I notice that when deploying a package that uses data sources behind its connection managers, the deployed package has the connection string built within the package that gets deployed.

From a previous post from Kirk - "What I'd suggest is that you create a Data source per machine and have all the connections reference it. You'll still need to create a connection manager in each package, but they will all reference the same data source. Moving to different machines is no problem because you can have a "machine resident" data source file that properly points to your "ETL Server"."

How are data sources defined for a machine? Is this simply via one of the configuaration options (i.e. an .xml file) that are loaded upon runtime by a package that is built to reference that config, which then trumps the connection string defined within the package?

ThanksDatasources are a design time feature. The connection manager references the DS and caches the connection string so that even if the datasource is no longer available, the connection will still be viable.

For cases where you need to deploy to a location where you will not be opening the package in the designer, and you need the connection string to change, you'll need to configure the connection manager in the package. The data source will only modify the connection in the designer.

The point of the earlier post was that if you're designing multiple packages and you wish to have all the connections in those packages pointing to the same server, you can create datasource and reference it by the DataSourceID in all the packages. If the server changes, you can modify one data source and it will modify all the connections for you. Next time you open the packages, they will pick up the new connection string. Moving the package to a different machine is also no problem so long as there is a data source available on the target machine with the same DataSourceID.

Configurations are your first line of defense for making packages location independent for easier deployment and your best option for packages that won't be opened in the designer. The DataSource approach is just a one off thing you can do in some cases.

K|||

>> Moving the package to a different machine is also no problem so long as there is a data source available on the target machine with the same DataSourceID.
>> Configurations are your first line of defense for making packages location independent for easier deployment and your best option for packages that won't be opened in the designer.

When I'm talking about deployment, I'm thinking test/prod etc where I'll never need to open it in Designer. That's what the dev env is for. Isn't it?

Given that, package configurations look like the "only option" and not just a "best option", and "only line of defence", not "first line of defense". Isn't that true? Basically, if I don't plan on opening my packages in Designer, then I have to go through the painful process of configuring each package separately and entering values for each package at deployment time. Is there an alternative?

Thanks,

RV.

|||

We are required to do a silent install in production at client sites, ie. no DBA or designer access. In this case, we develop in our environment and may would have to deploy each package with its connection strings. This may impact our design to minimize the number of packages.

Any thoughts?

|||

Use configurations. For each site you will need to adjust the configuration data store to hold the correct values for that site, and how you that depends on the store you use. You could write a wizard where the person running the install simply supplies the values and these are written to the correct location. Your packages will already be configured to read their information from configurations so everything should be sweet. Indirect configurations may be easier, and the values set by the install wizard.

Some links-

http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/10/31/2336.aspx

http://msdn2.microsoft.com/en-us/library/ms141682.aspx

http://www.sqljunkies.com/WebLog/knight_reign/archive/2004/12/07/5445.aspx

|||

I went through all this frustration myself and eventually settled on a solution that works very well. In a nutshell, I store my connection strings for all my connection managers in the database.

1) All my connection managers are of type OLEDB

2) Every package that has a connection string has package configurations enabled with a Configuration entry of type SQL Server that points to a Configuration table in the database. This entry is the way the connection manager gets the connection string at runtime. In the Configuration entry, it is the Configuration filter setting which will differentiate the different entries in the database pertaining to different connection strings.

3) My deployment scripts insert the correct database connection info into the configuration database for each connection. The connection strings are dependent on the environment I'm deploying to. The connection managers in the solution will at runtime grab the value from the database via the package configurations on the package the connection managers live on.

4) I deploy using dtsutil and make it silent. No need to not have it silent.

Data Sources and Connection Managers

Greetings my SSIS friends

This is probably a silly question but what exactly is the purpose of creating Data Sources when it is possible to set up individual connection managers?

Thanks for your help in advance.

If you want to reuse connection managers across different packages, you can create data sources and choose new or existing data connections.

Data Sources

Why do the data sources appear in Report Manager when deploying reports. How can they be hidden from the users?

In the properties window - choose "Hide in list view" - it will not be seen in the list view.
If you want to actually secure them - change the permissions to the data source to not include your end users. Only report publishers need to have access to the datasources.

Tudor

Data Sources

Why do the data sources appear in Report Manager when deploying reports. How can they be hidden from the users?

In the properties window - choose "Hide in list view" - it will not be seen in the list view.
If you want to actually secure them - change the permissions to the data source to not include your end users. Only report publishers need to have access to the datasources.

Tudor

sql

Data Sources

I have created a report, and would like to use the data contained in that report to create another report. In essence, one report would be the dataset for another report. I can't find any indication that this is possible. Can anyone give me a suggestion?This is possible but not easy. First, you would need to write (or purchase)
a data provider that accepts XML data input from a URL.
Then you would use that data provider to get an export of the report from
the report server in XML format (using URL access).
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"RptSrvQuests" <RptSrvQuests@.discussions.microsoft.com> wrote in message
news:188E428D-4979-4ADE-B1B9-441E47011558@.microsoft.com...
> I have created a report, and would like to use the data contained in that
report to create another report. In essence, one report would be the
dataset for another report. I can't find any indication that this is
possible. Can anyone give me a suggestion?|||The best place to start is the section of the documentation titled
"Implementing a Data Processing Extension".
It is also available in MSDN:
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"RptSrvQuests" <RptSrvQuests@.discussions.microsoft.com> wrote in message
news:4B5F8B34-B05F-412E-9F19-62791C1CDE00@.microsoft.com...
> Can you provide an example of a data provider or what code it would be
written in? I would also like a more detailed description of how to carry
out the process. I'm still relatively new to RS, and need detailed
instructions on carrying out procedures.
> "Chris Hays [MSFT]" wrote:
> > This is possible but not easy. First, you would need to write (or
purchase)
> > a data provider that accepts XML data input from a URL.
> > Then you would use that data provider to get an export of the report
from
> > the report server in XML format (using URL access).
> >
> > --
> > My employer's lawyers require me to say:
> > "This posting is provided 'AS IS' with no warranties, and confers no
> > rights."
> >
> > "RptSrvQuests" <RptSrvQuests@.discussions.microsoft.com> wrote in message
> > news:188E428D-4979-4ADE-B1B9-441E47011558@.microsoft.com...
> > > I have created a report, and would like to use the data contained in
that
> > report to create another report. In essence, one report would be the
> > dataset for another report. I can't find any indication that this is
> > possible. Can anyone give me a suggestion?
> >
> >
> >

Data Sources

Is there anyway to use a Class File (VB.Net or C# class that generates a
DataSet) as the data source for a SQL Server 2005 Reporting Services report?
If there is, where are some examples or how-to's?
The only obvious way I see to generate a Data Source for a SQL report is a
sproc or dynamic query.
Thank You.Alex,
You want to use a Custom Data Processing Extension. You can even tie
the extension to an existing data layer.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
Andy Potter

Data source and data set confusion

Maybe I am not understanding the way that these things work, but a little help would be apperciated.

I want to set up my shared data sources and "write them in stone". To be honest, once a data source has been set up, there should be no reason the change it unless a login credential changes, or we change the name of a database; changes to data sources should be done by a senior admin (like me!). All fine so far? Good...

Are data sets associated with/bound exclusively to the report, or the data source? For example, if I tie down my data sources and find a way to make them unchangeable, I then want myself and my collegues to be able to create/modify/delete data sets associated with the shared data source. If I do this, and then deploy my report (with overwrite datasources set to false) will they then work?

If the above is possible, is there a way that I can tie down the data sources so that only a select few can modify them? This would be a minimum requisit for a reporting manager (like me) where we have many developers creating reports and writing (and over-writing!) shared datasources - and causing a report completely unconnected with their current work to fail.

I realise that my experience is limited, and that SQL Reporting Services is young, but it is such a powerful product I can see it being used extensively across many industries in the future - mainly because it kicks Crystal in to touch on so many points.

OK, well, witha little experimentation, I have managed to answer part of my question. The data set is associated with the report, so setting the properties of the solution, over write data sources to false, I can create reports with custom data sets - no problems. Now for the tricky bit...

Does anyone have any as to how I can tie down the data source? It is accessible via Visual Studio, and the solution properties are easily changeable. Also, once these shared data sources are tied down, how does one add them to a solution? I can only find a way to add new data source, and then make it shared.

I have logged on to the reporting services area in SSMS and can see the security part, but how do I associate individuals to new roles in there? They will be domain roles, btw.

Thoughts/suggestions anyone?

sql

Data Source access

Hi All,
I'm looking to create an ad-hoc query generator so that I can create a
report on the fly, but I would like to use RS Shared Data Sources for this.
I have run into a problem though, in that I can't retrieve the password for
the Shared Data Source. Is there any way to get the Data Source back as a
connection to the database in ADO?
Thanks in advacne,
CathieNo you cannot get the data source back as connection. But you could try to
implement a custom RS data extension and use it then in the shared data
source.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cathie" <cathie@.somewhere.com> wrote in message
news:eqY8s64SFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I'm looking to create an ad-hoc query generator so that I can create a
> report on the fly, but I would like to use RS Shared Data Sources for
> this. I have run into a problem though, in that I can't retrieve the
> password for the Shared Data Source. Is there any way to get the Data
> Source back as a connection to the database in ADO?
> Thanks in advacne,
> Cathie
>|||Hi Robert,
I'm sorry I don't understand how a custom RS data extension would help me.
I am want to be able to get any datasource's (within RS) tables, views,
stored procedures etc. How would a custom RS data extension help?
Cathie
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:OIBjdIgTFHA.1404@.TK2MSFTNGP09.phx.gbl...
> No you cannot get the data source back as connection. But you could try to
> implement a custom RS data extension and use it then in the shared data
> source.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Cathie" <cathie@.somewhere.com> wrote in message
> news:eqY8s64SFHA.2128@.TK2MSFTNGP14.phx.gbl...
>> Hi All,
>> I'm looking to create an ad-hoc query generator so that I can create a
>> report on the fly, but I would like to use RS Shared Data Sources for
>> this. I have run into a problem though, in that I can't retrieve the
>> password for the Shared Data Source. Is there any way to get the Data
>> Source back as a connection to the database in ADO?
>> Thanks in advacne,
>> Cathie
>|||If you go through a custom data extension, you would need to implement our
IDBConnection etc. interfaces. Hence, in the custom data extension you would
see the actual connection string etc. used to connect to the data source.
You could use this information to do then some adhoc query generation. What
I'm basically saying is that you cannot easily do the adhoc query generation
from within the report in RS 2000.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cathie" <cathie@.somewhere.com> wrote in message
news:e5GQ9F4TFHA.2712@.TK2MSFTNGP09.phx.gbl...
> Hi Robert,
> I'm sorry I don't understand how a custom RS data extension would help me.
> I am want to be able to get any datasource's (within RS) tables, views,
> stored procedures etc. How would a custom RS data extension help?
> Cathie
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:OIBjdIgTFHA.1404@.TK2MSFTNGP09.phx.gbl...
>> No you cannot get the data source back as connection. But you could try
>> to implement a custom RS data extension and use it then in the shared
>> data source.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Cathie" <cathie@.somewhere.com> wrote in message
>> news:eqY8s64SFHA.2128@.TK2MSFTNGP14.phx.gbl...
>> Hi All,
>> I'm looking to create an ad-hoc query generator so that I can create a
>> report on the fly, but I would like to use RS Shared Data Sources for
>> this. I have run into a problem though, in that I can't retrieve the
>> password for the Shared Data Source. Is there any way to get the Data
>> Source back as a connection to the database in ADO?
>> Thanks in advacne,
>> Cathie
>>
>

Data region in RS allows only 1 data source - virtual cube solutions?

I have a report requiring two different data sources be compared in one
data region. For example, I'm comparing a measure for a client against
a national average. The client measure comes from one cube and the
national average is stored in a sql database. RS won't allow two data
sources to be used in a data region unless you can use an aggregate
function on the second data source. I attempted to solve this by
creating a cube for the second data source (national averages) and then
a virtual cube to make one data source (i.e. one MDX statement from the
virtual cube per data region) for RS. The orginal MDX used for the
first data source required slicing by dimensions not included in the
national average cube and visa versa. I have been able to get the
correct answer for each cube before combining into the virtual cube,
but I can not get the measures correctly in the virtual if, for each
measure, I need to slice by a dimension that is not shared. I haven't
been able to find the information anywhere (and I've tried internet and
2 books), but because my measures are not correct I assume that a
virtual cube in only useful if the correct results are obtained by
slicing into only the shared dimensions? Are there any other solutions
to the rediculous RS limitation of only being able to provide one data
source to a data region? What we they thinking'oops, should have used spell check before submitting. it is a
ridiculous RS limitation.|||Hi Carrie,
I believe you can refer to more than one datasource from a data region such
as a table. Try putting in two data sources on a report and drag data from
them both into a table (note, you have to set the tables data source to one
data set).
see how that goes
<cwilliams@.choosehmc.com> wrote in message
news:1104767162.584780.225260@.z14g2000cwz.googlegroups.com...
> I have a report requiring two different data sources be compared in one
> data region. For example, I'm comparing a measure for a client against
> a national average. The client measure comes from one cube and the
> national average is stored in a sql database. RS won't allow two data
> sources to be used in a data region unless you can use an aggregate
> function on the second data source. I attempted to solve this by
> creating a cube for the second data source (national averages) and then
> a virtual cube to make one data source (i.e. one MDX statement from the
> virtual cube per data region) for RS. The orginal MDX used for the
> first data source required slicing by dimensions not included in the
> national average cube and visa versa. I have been able to get the
> correct answer for each cube before combining into the virtual cube,
> but I can not get the measures correctly in the virtual if, for each
> measure, I need to slice by a dimension that is not shared. I haven't
> been able to find the information anywhere (and I've tried internet and
> 2 books), but because my measures are not correct I assume that a
> virtual cube in only useful if the correct results are obtained by
> slicing into only the shared dimensions? Are there any other solutions
> to the rediculous RS limitation of only being able to provide one data
> source to a data region? What we they thinking'
>|||The answer is to create a calculated member using the ValidMeasure
function for each measure in the virtual cube. Just in case anyone is
interested...sql

Data Processing Extensions and Data Sources

Anyone know about DPE? Specifically, I want to know if it can help me to
make a data source dynamic in a multi-user environment.
I need to integrate SRS into my web app, which allows the user to select a
database from a list before running a report. Several people could try to
run the same report at the same time. If I use the API
"SetDataReportSources" then this switches the data source for all users of
the report. As noted before in this group, this is a limitation of SQL RS.
What I really want to know is: Can I develop a DPE that allows me to
dynamically set the data source at run-time? This is a requirement for my
application, and I want to avoid creating multiple copies of reports or
passing the data source in a stored procedure. Either of these work-arounds
involve more maintenance and hassle for the future. Can DPE allow me to make
a permanent solution?
I looked in the books online, and read about implementing a connection
class. If I made my own connection object, could I set it dynamically at run
time?
Hopefully,
MalikI'm trying to do the same thing as well but we havn't had the time or
resource yet to finish it off. Once I have a DPE running I would be happy to
share it on gotdotnet. Or if you get there first maybe you could to.
Try this link for further info. This is where I got started. I have aslo
uploaded some VB code to Gavin's page to complement the C# he has already.
It's still not as straight forward as you might hope but it will acomplish
what you are after.
http://weblogs.asp.net/gavinjoyce/archive/2004/01/29/64339.aspx?Pending=true
Regards
Toby,.
"Abdul Malik Said" <diplacusis@.hotmNOSPAMail.com> wrote in message
news:O4sJVKDZEHA.556@.tk2msftngp13.phx.gbl...
> Anyone know about DPE? Specifically, I want to know if it can help me to
> make a data source dynamic in a multi-user environment.
> I need to integrate SRS into my web app, which allows the user to select a
> database from a list before running a report. Several people could try to
> run the same report at the same time. If I use the API
> "SetDataReportSources" then this switches the data source for all users of
> the report. As noted before in this group, this is a limitation of SQL RS.
> What I really want to know is: Can I develop a DPE that allows me to
> dynamically set the data source at run-time? This is a requirement for my
> application, and I want to avoid creating multiple copies of reports or
> passing the data source in a stored procedure. Either of these
work-arounds
> involve more maintenance and hassle for the future. Can DPE allow me to
make
> a permanent solution?
> I looked in the books online, and read about implementing a connection
> class. If I made my own connection object, could I set it dynamically at
run
> time?
> Hopefully,
> Malik
>

Data mapping and Importing app for SQL server

Hello,

Our company often receives data from outside sources to add to our application. This data is usually provided to us in Excel, CSV, XML, etc. The files that we receive usually have different columns from the columns in our database, so we have to map these columns to our table structure to import.

I'm looking for an application that will easily allow me to load up the data file (whatever type it may be), expose the columns in the data file, allow me to map these columns in our SQL server, then import the data. I know that this can be done as DTS, however I'm looking for alternatives. Does anyone have any recommendations?

Thanks in advance.

I just dont have a solution to your post but i have the same problem. I just want to get notifications on reply event of this thread. Hopefully some one will answer you and i will benefit.

Data Flow Task Stuck

I have a simple data flow task setup...

2 ADO.NET connection managers, each referencing a DSN pointed to a Unidata database.

2 DataReader sources, each using a single ADO.NET connection managers, running a simple SELECT statement from a table.

I have a Union All transform setup to merge the data and write to a OLE DB Destination (SQL05 database)

When I run the package, each source will validate, but only one will execute. The other source will do nothing. The data source will be colored yellow, and will just sit there. The package will just sit, almost like it is waiting for input.

This behavior is not consistent, however. It varies which data source will hang, pretty much 50-50. About 25% of the time, both sources will execute, and all rows will be written to the destination.

Any help is appreciated.

thanks

Are the source connections pointing to the same database? I'm not familiar with Unidata, but it sounds like there might be some type of blocking issue, either in the database or in the ODBC driver. If you use two separate data flows, one per DataReader Source, does it consistently run successfully?|||No, they are not pointing to the same database, and I have tried independent data flows with the same results.

|||

When you tried independent data flows, did you add a precedence constraint so that they were executing sequentially rather than in parallel?

If the problem shows up when you are only running a single DataReader Source, I'd see if there are any known issues with the ODBC driver.

|||When I try them sequentially, it works correctly every time.

This is an acceptable workaround, but I am still curious to determine the reason for this strange behavio

|||

Corey M. wrote:

When I try them sequentially, it works correctly every time.

This is an acceptable workaround, but I am still curious to determine the reason for this strange behavio

My guess is that it's an ODBC driver issue. Perhaps table locking or something like that. Who knows. You'll have to check for any known issues with the ODBC driver provider.

|||

You may wish to try the flowsync transform, placing one FlowSync transform between each source adapter and the union all transform (for a total of two FlowSync transforms).

The FlowSync transform is a speed governor, so a particular source flow doesn't get too far ahead of the other one. For something like union all, one source outpacing another should not be an issue, but you could certainly give it a shot.

To install the transform, you'll need to copy the assembly (FlowSync.dll) into two places and add it to the toolbar.

1. For runtime purposes, copy the FlowSync.dll into the global assembly cache, which is "%windir%\assembly" or typically the directory c:\windows\assembly. For design-time purposes, copy the same FlowSync.dll into the Integration Service PipelineComponents directory, which is typically located at "%ProgramFiles%\Microsoft SQL Server\90\DTS\PipelineComponents", If you're running 64 bit, the design-time installation directory is typically located at "%ProgramFiles(x86)%\Microsoft SQL Server\90\DTS\PipelineComponents".

2. To add flowsync transform to the toolbar, open up BIDS, right click on the toolbox, select "Choose Items...", go to the tab SSIS Data Flow Items, and Select the FlowSync transform, which will then show up appear in the toolbox as an available transform.