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 source synchronization issue: Connection string conflict?

I was NOT using data source till now. I was directly using Ole DB Connection. Everything works as expected.

Today, I created a new Data Source based on the existing Ole Db connection.

I changed my tasks to use this Data Source.

Now, everytime I open the dtsx file, a screen pops-up with title "Synchronize Connection Strings". It reads: "This package contains at least one connection which is based on a data source. The connection strings for the connection and the data source listed below are not identical. Connection string of the connection will be updated to reflect those on the data source.

Connection : XYZ

Data Source: XYZ

Old Connection String: machine_name\instance_name; User ID=sa; Initial Catalog = MyDB; Provider=SQLOLEDB.1

New Connection String: Provider=SQLOLEDB.1; Data Source = machine_name\instance_name; Persist Security Info = true; Password=; User ID=; Initial Catalog = MyDB

I press the OK button, but the screen repeats if I close the dtsx file and re-open it. The underlying error is:

Error 1 Validation error. Data Flow Task - MyTaskName: OLE DB Destination - mytable [214]: The AcquireConnection method call to the connection manager "machine_name\instance_name.MyDB.sa" failed with error code 0xC0202009. mypackage.dtsx 0 0
Any idea?looks like a bug... can you open one, please?

thanks|||Sorry, I did not look at this post and therefore your reply till today (when I thought about using Data Sources again).

How do I submit this bug?

Data Source passwords in Config Files

Anybody find a sound approach to being able to store DataSource connection information in a configuration and be able to access a Data Source that requires a password for login?

Storing it as a configuraiton in the DB is not an option, as you require the pw to access the DB that would store it.

Can security be applied to an environment variable? Registry?

Anyone successfully use the registry to store configuration values?I'm not sure what your criterion for sound is, so I don't know if this meets it :), but you can store credentials (username & password) in a configuration file, and then make an environment variable which gives its path, and then configure a Package Configuration in the SSIS package to use the environment variable.
Of course you have to configure the environment variable for each machine (and for each user, if two users on one machine will use different credentials -- therefore, different configuration files).
Then, the credentials being now in one or more files, secure access to these files via traditional NTFS ACLs (file pemissions).
|||

I guess by sound, I meant, any way that works... when i store the pw in the config the .xml file represents it like the following:

- <Configuration ConfiguredType="Property" Path="\Package.Connections [MDB].Properties[Password]" ValueType="String">

<ConfiguredValue />

</Configuration>

This results in the error:
Error 1 Validation error. Data Flow Task: OLE DB Source [1]: The AcquireConnection method call to the connection manager "MDB" failed with error code 0xC0202009. Package2.dtsx 0 0

Have you been able to do this?.. am I missing something? I am also storing the connection string, initial catalog, and username in the config file.

|||When the file is generated by Visual Studio, it will not write the pw. I was able to get it to work by modifying the file directly.. duh.|||I'm assuming that you're not on a domain and so cannot use integrated authentication?
K|||Yes, Joe, We've been using the solution I explained above, with different configurations for different developers on the same machine (so they can use different login names, so the processes are distinguishable in SQL Server Enterprise Manager).
But, we've been using SQL Server logons (not integrated), because we're using a SQL Server 2000 backend. What you quoted sounds like you're using an mdb backend, which we've not tried.
To set up a new configuration with a new set of credentials, I first add the environment variable for that user, pointing it to where the config file is going to be. Then I put a config file there -- I take an existing one, and using a text editor, change the logon name & password (stored in plaintext in the file).
The very first time we did this, SSIS generated the config file for us; since then, we've been hand editing it for each machine and/or credential combination.

|||Here is an edited copy of our config file. Everything beginning with Zz is something that I edited before posting (along with the nifty DEADBEEF guid).
Also I added the carriage returns and whitespaces -- the raw config file is a whitespaceless mess.
******************

<?xml version="1.0"?>
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="ZzMachineName\ZzUserName" GeneratedFromPackageName="Zz Our Nifty Package" GeneratedFromPackageID="{DEADBEEF-AAAA-BBBB-CCCC-DEADBEEFBEEF}" GeneratedDate="7/26/2005 4:25:01 PM"/>
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="\Package.Connections[ZZ_OUR_CONN_NAME].Properties[Password]" ValueType="String">
<ConfiguredValue>ZzBigSecretPassword</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[ZZ_OUR_CONN_NAME].Properties[UserName]" ValueType="String">
<ConfiguredValue>ZzLogonNameForThisDeveloperHere</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[ZZ_OUR_NOTHER_CONN].Properties[Password]" ValueType="String">
<ConfiguredValue>ZzBigSecretPassword</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[ZZ_OUR_NOTHER_CONN].Properties[UserName]" ValueType="String">
<ConfiguredValue>ZzLogonNameForThisDeveloperHere</ConfiguredValue>
</Configuration>
</DTSConfiguration>

Data Source file on Reporting Services server question

In the Reporting Services Manager, when the user selects the folder where the
reports reside there is a file that contains the connection string
information along with a user name and password (Data Source).
Is there a way to keep the file from being selected by the user ?
Thanks.There is a setting in the properties of the data source to "Hide in List
View". Unfortunately the users can just click on the "Show Details"
option and see the datasource. Depending on the permissions you gave
them they will only be able to view the information.
I sent an question out some time ago seeing if there was away to remove
the "Show Details" for some users but no one replied.
Mike wrote:
> In the Reporting Services Manager, when the user selects the folder where the
> reports reside there is a file that contains the connection string
> information along with a user name and password (Data Source).
> Is there a way to keep the file from being selected by the user ?
>
> Thanks.|||You can use a datasource in another location. It's a faff because you
have to change it manually for each deployed report affected. Also
everytime you deploy the report it will create the datasources in the
deployment folder if it can, so you have to remove it each time.
Having said that, this would essentially cure your problem.
Chris
Bon733 wrote:
> There is a setting in the properties of the data source to "Hide in
> List View". Unfortunately the users can just click on the "Show
> Details" option and see the datasource. Depending on the permissions
> you gave them they will only be able to view the information.
> I sent an question out some time ago seeing if there was away to
> remove the "Show Details" for some users but no one replied.
> Mike wrote:
> > In the Reporting Services Manager, when the user selects the folder
> > where the reports reside there is a file that contains the
> > connection string information along with a user name and password
> > (Data Source).
> >
> > Is there a way to keep the file from being selected by the user ?
> >
> >
> > Thanks.

Data source connection to SAS via SS05

Hi all, does anyone have a sample (or knows of one on the web, maybe screenshots) of how to connect via SQL Server 2005 to a SAS box? The SAS machine is Unix, I am able to connect via Enterprise Guide from my local machine - which is also runing SS05 Developer. I'm investingating the possibility of building a cube of off a SAS dataset.

Thanks in advance.

Mark

Two things to keep in mind when building a cube. First, you have to have a driver with which SSAS can connect to the data source and through which SSAS can build objects within the DSV. Next, you need to have a cartridge with which SSAS can construct query statements to pull data from the data source.

You might be able to present SAS objects through SQL Server views using the OPENROWSET function. (This may be where you are already headed.) So, is there a way to connect SQL Server Database Engine to SAS using OPENROWSET?

One other thing, SAS has their own OLAP solution. Is that an option for you?

B.

|||

Ho Bryan,

I went in a different direction (FTP text files for awhile), but am re-visiting this now. We don't have the SAS OLAP Server in house, so unfortunately that isn't an option. However, is there a SAS/MSFT web-site where cartriges can be obtained from? I'm able to connect to the SAS box and can see the SAS datasets, but, I can't render them. Its literally the last step that's blowing up, and I believe it may be cartrige driven.

Thanks,

Mark

|||

Sorry to say I'm not aware of any cartridges other than those provided by default. Worst case, can you perform an OPENROWSET query through SQL Server? If so, you might be able to construct a query in SQL Server using OPENROWSET against SAS which would faciliate processing. Still, no guarantees that would work.

B.

Data source connection to SAS via SS05

Hi all, does anyone have a sample (or knows of one on the web, maybe screenshots) of how to connect via SQL Server 2005 to a SAS box? The SAS machine is Unix, I am able to connect via Enterprise Guide from my local machine - which is also runing SS05 Developer. I'm investingating the possibility of building a cube of off a SAS dataset.

Thanks in advance.

Mark

Two things to keep in mind when building a cube. First, you have to have a driver with which SSAS can connect to the data source and through which SSAS can build objects within the DSV. Next, you need to have a cartridge with which SSAS can construct query statements to pull data from the data source.

You might be able to present SAS objects through SQL Server views using the OPENROWSET function. (This may be where you are already headed.) So, is there a way to connect SQL Server Database Engine to SAS using OPENROWSET?

One other thing, SAS has their own OLAP solution. Is that an option for you?

B.

|||

Ho Bryan,

I went in a different direction (FTP text files for awhile), but am re-visiting this now. We don't have the SAS OLAP Server in house, so unfortunately that isn't an option. However, is there a SAS/MSFT web-site where cartriges can be obtained from? I'm able to connect to the SAS box and can see the SAS datasets, but, I can't render them. Its literally the last step that's blowing up, and I believe it may be cartrige driven.

Thanks,

Mark

|||

Sorry to say I'm not aware of any cartridges other than those provided by default. Worst case, can you perform an OPENROWSET query through SQL Server? If so, you might be able to construct a query in SQL Server using OPENROWSET against SAS which would faciliate processing. Still, no guarantees that would work.

B.

Data Source Connection String

Is there any way to find out what the data souce connection string is at
runtime?
I have a report that is sent out to various sites, each with a common data
schema, but various connection strings. The report uses custom code in the
report rdl, so each time I deploy the report to a new site, I have to reset
the custom code connection string to match the individual site I am deploying
it to.
I would like to be able to have it dynamically query the connection string
so I can just pass it in to my code as a variable. Any ideas?
MichaelI think the report only knows about the data source name (if using shared ds)
What we have is a bunch of reports using the same data source.
We then deploy to different folders, as long as you are not "overwriting
data source" then you can just configure the different data sources in report
manager.
So 1 report gets deployed to 2 or more folders, each folder having its own
datasource. (the config manager is not intuitive)
We find this easier to manage 2 different regions using the same reports on
2 different databases like this.
We even have it so different sub regions have different sub folders using
linked reports with their sub region 'hardcoded' as a linked report param -
they are not even aware of the other sub regions' reports existing due to
security setup
You could always used linked reports to point back at the different folders
if the users complain.
"Michael C" wrote:
> Is there any way to find out what the data souce connection string is at
> runtime?
> I have a report that is sent out to various sites, each with a common data
> schema, but various connection strings. The report uses custom code in the
> report rdl, so each time I deploy the report to a new site, I have to reset
> the custom code connection string to match the individual site I am deploying
> it to.
> I would like to be able to have it dynamically query the connection string
> so I can just pass it in to my code as a variable. Any ideas?
> Michael|||Adolf,
I too am trying to deploy my solution like you described, one folder for
each region with it's own datasource. My problem is how do you get the
reports to use the correct datasource? For example, my VS2005 solution has a
shared data source which I use to build my reports. When the reports are
deployed, the reports went to Region1 directory and the datasource went to
Data Sources directory. I manually added a datasource in the Region1
directory with the same name as the one in the Data Sources directory, but
the reports aren't using it. How do I get them to use that one instead. I
can't find anything that explains how to do this. Thank you.
"adolf garlic" wrote:
> I think the report only knows about the data source name (if using shared ds)
> What we have is a bunch of reports using the same data source.
> We then deploy to different folders, as long as you are not "overwriting
> data source" then you can just configure the different data sources in report
> manager.
> So 1 report gets deployed to 2 or more folders, each folder having its own
> datasource. (the config manager is not intuitive)
> We find this easier to manage 2 different regions using the same reports on
> 2 different databases like this.
> We even have it so different sub regions have different sub folders using
> linked reports with their sub region 'hardcoded' as a linked report param -
> they are not even aware of the other sub regions' reports existing due to
> security setup
> You could always used linked reports to point back at the different folders
> if the users complain.
> "Michael C" wrote:
> > Is there any way to find out what the data souce connection string is at
> > runtime?
> >
> > I have a report that is sent out to various sites, each with a common data
> > schema, but various connection strings. The report uses custom code in the
> > report rdl, so each time I deploy the report to a new site, I have to reset
> > the custom code connection string to match the individual site I am deploying
> > it to.
> >
> > I would like to be able to have it dynamically query the connection string
> > so I can just pass it in to my code as a variable. Any ideas?
> >
> > Michaelsql

data source connection problem

This is a multi-part message in MIME format.
--=_NextPart_000_001C_01C47406.81D2ACF0
Content-Type: text/plain;
charset="iso-8859-9"
Content-Transfer-Encoding: quoted-printable
i have a problem that alerts
"The report server cannot process the report. The data source connection = information has been deleted. (rsInvalidDataSourceReference)"
what must i do? in .NET, connection and data receving is correct. but in = my project, i couldn'r open the report.
--=_NextPart_000_001C_01C47406.81D2ACF0
Content-Type: text/html;
charset="iso-8859-9"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
i have a problem that = alerts
"The report server cannot = process the report. The data source connection information has been deleted. (rsInvalidDataSourceReference)"

what must i do? in .NET, connection = and data receving is correct. but in my project, i couldn'r open the report.
--=_NextPart_000_001C_01C47406.81D2ACF0--This is a multi-part message in MIME format.
--=_NextPart_000_0041_01C473CF.AFEA2A00
Content-Type: text/plain;
charset="iso-8859-9"
Content-Transfer-Encoding: quoted-printable
First create a new data source, then click on three dots next to the =query and change the data source to the new one. Hope this helps.
Regards,
Cem "erdi" <erdincugurlu@.hotmail.com> wrote in message =news:eOskyz%23cEHA.3560@.TK2MSFTNGP10.phx.gbl...
i have a problem that alerts
"The report server cannot process the report. The data source =connection information has been deleted. (rsInvalidDataSourceReference)"
what must i do? in .NET, connection and data receving is correct. but =in my project, i couldn'r open the report.
--=_NextPart_000_0041_01C473CF.AFEA2A00
Content-Type: text/html;
charset="iso-8859-9"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
First create a new data source, then =click on three dots next to the query and change the data source to the new one. Hope =this helps.
Regards,
Cem
"erdi" = wrote in message news:eOskyz%23cEHA.=3560@.TK2MSFTNGP10.phx.gbl...
i have a problem that =alerts
"The report server cannot =process the report. The data source connection information has been deleted. (rsInvalidDataSourceReference)"

what must i do? in .NET, =connection and data receving is correct. but in my project, i couldn'r open the report.

---

--=_NextPart_000_0041_01C473CF.AFEA2A00--|||This is a multi-part message in MIME format.
--=_NextPart_000_0010_01C47493.EED668E0
Content-Type: text/plain;
charset="iso-8859-9"
Content-Transfer-Encoding: quoted-printable
cem bey, te=FEekk=FCrler...
bir sorum daha olacak...subreport kullanmak istiyorum da, An error occurred while executing the subreport 'subreport1': An error =has occurred during report processing.
Cannot create a connection to data source ".....name......"
gibi bir sorunla kar=FE=FDla=FE=FDyorum...datasource u yenilememe =ra=F0men bir sonu=E7 alamad=FDm..
"Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message =news:eWB%232E$cEHA.3728@.TK2MSFTNGP09.phx.gbl...
First create a new data source, then click on three dots next to the =query and change the data source to the new one. Hope this helps.
Regards,
Cem
"erdi" <erdincugurlu@.hotmail.com> wrote in message =news:eOskyz%23cEHA.3560@.TK2MSFTNGP10.phx.gbl...
i have a problem that alerts
"The report server cannot process the report. The data source =connection information has been deleted. (rsInvalidDataSourceReference)"
what must i do? in .NET, connection and data receving is correct. =but in my project, i couldn'r open the report.
--=_NextPart_000_0010_01C47493.EED668E0
Content-Type: text/html;
charset="iso-8859-9"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

cem bey, =te=FEekk=FCrler...
bir sorum daha olacak...subreport =kullanmak istiyorum da,
An error occurred while executing the subreport =‘subreport1’: An error has occurred during report processing.
Cannot create a connection to data source ".....name......"
gibi bir sorunla kar=FE=FDla=FE=FDyorum...datasource =u yenilememe ra=F0men bir sonu=E7 alamad=FDm..
"Cem Demircioglu" =wrote in message news:eWB%232E$cEHA.=3728@.TK2MSFTNGP09.phx.gbl...

First create a new data source, then =click on three dots next to the query and change the data source to the new =one. Hope this helps.

Regards,
Cem

"erdi" = wrote in message news:eOskyz%23cEHA.=3560@.TK2MSFTNGP10.phx.gbl...
i have a problem that =alerts
"The report server =cannot process the report. The data source connection information has been deleted. = (rsInvalidDataSourceReference)"

what must i do? in .NET, =connection and data receving is correct. but in my project, i couldn'r open the report.

---

--=_NextPart_000_0010_01C47493.EED668E0--

data source connection error...

Guess this was already posted somewhere...
My connection string to connect to sybase: DRIVER={Sybase ASE ODBC
Driver};SRVR=<server_name>;DB=<db_name>
Data source type : ODBC
Works on the Designer but not on the server.
I get a system error 126 - specified driver could not be loaded.
?If its of interest to anybody, this problem was resolved once the MDAC
components were upgraded to 2.8.
"NU" <vidhuvaradan@.rediffmail.com> wrote in message
news:eW$MlA9ZEHA.3596@.tk2msftngp13.phx.gbl...
> Guess this was already posted somewhere...
> My connection string to connect to sybase: DRIVER={Sybase ASE ODBC
> Driver};SRVR=<server_name>;DB=<db_name>
> Data source type : ODBC
> Works on the Designer but not on the server.
> I get a system error 126 - specified driver could not be loaded.
> ?
>
>

Data Source and Connection Manager

Hi all,

I don't really understand the difference between the "data source" and the "connection manager" in a SSIS solution.

I tried to create a data source (if I understood correctly, I will ba able to share that connection between all packages in the solution), then I create a new connection manager inside a package. But when I make a modification in the data source, nothing change in the connection manager ?!!?

What I think is that when I'm making a new connection manager, what's really happen is a copy of the data source, is that correct ? If it is I dont't understand the avantage of making a new data source ....

Thanks for your response

In my personal opinion, there is no advantage of data sources. They were created by the Analysis Services team and the Integration Services thought tthey looked like something nice they could use. Rmemeber that they were designed for SSAS though.

In my opinion - don't use them.

-Jamie

|||

jh0483 wrote:

Hi all,

What I think is that when I'm making a new connection manager, what's really happen is a copy of the data source, is that correct ?

That's correct.

I don't use them either. You can run into additional issues when the properties either from the connection manager or from the data source changes; you will start receiving annoying notifications/warnings that your data source and connection manager are not sync every time you open the package.

|||

I think of it like this. You will always a have a connection, but you can attribute the connection as being linked to the data source. See the DataSourceID of a connection. This property maintains the link between the physical package related structure and the ephemeral design-time only data source.

Much like the others I never found it offered much. The design-time management of connections can be achieved through the same methods as you would use for the deployed run-time stuff, i.e. configurations.

|||But for me there is a big advantage : if you have many packages sharing the same connections and you have to change the connection, then you just have to change it in one place. If you use only connection manager, then you have to check all your packages.|||

jh0483 wrote:

But for me there is a big advantage : if you have many packages sharing the same connections and you have to change the connection, then you just have to change it in one place.

Have you ever used or at least looked into configurations? They allow you to manage connections through a single point, across multiple packlages and even solutions. They also have the huge benefit of working at runtime, and are a very valuable tool when it comes to deploying your packages.

Use both if you want, but without configurations you will have to make changes as packages get deployed between environments. Data sources do not solve that for you in any effecitive manner. Configurations do, and for my money work just as well when devloping packages.

|||Of course I use configurations ...

But still in your configuration file, if you have 5 packages with 5 connection manager you will have to configure 5 time the connection, which is not the case with the Data source.|||

Ok, good so you hopefully see the benefit of configurations. Your point seemed to be stating that there was a benefit of data sources that was not available with configurations, that was what I disagreed with.

If you have 5 packages and 5 connections, that are the same, presumably linked to the same Data Source to give you that change once, update all effect. This is not solely a benefit of data sources. You can get the same effect by using the same configuration for all five connections. If the connection string changes you update the one configuration store. Whenever the 5 packages are now opened the configuration applies the change, and without the annoying dialog you get with a data source warning you or the discrepancy between the updated data source and soon to be updated connection.

The benefit for me of configurations over data sources is that I can use that same mechanism to change connection strings simply as packages move between development, testing and production for example. Using data sources would require you to open the package in the designer and actually modify the package, albeit with minimal user input since the data source does the hard work, but still having to change packages as part of a deployment process is just wrong in my opinion.

Is your point that that each time you add a distinct connection to a package you need to set that connection as being controlled by a configuration? Data sources will avoid that I agree. Still for me I will always use configurations to solve the deployment issue, so I will incur that cost anyway. It is minimal since there is virtually no reason to have more than one connection object per connection string as SSIS will by default create multiple instances of that connection. This is of course a change from the DTS world if you used that previously, where it was actually good practice to duplicate connections.

Do you see any other benefits of data sources over configurations for connection management?

|||

I am using data connections ( in each package) which is referencing to one of the datasource.... i used this becuase i thought there would be one object i.e. Data Source (for creating connection with SQL sever or ne other database) and that object reference would be used in each package via Connection manager, and that makes it memory efficient because u would not have more objects (connection objects) in memory as connection manager is just a reference to that Data Source Object... connection string can be set up dynamicaly to point that connection object to a database and if connection string changes at run time even then there would be only one object (just pointing to differet database)

can ne one tell me isnt that right? as i hv read on this thread that it copies connection from data source to connection manager .... can ne one tell me ne article in that issue?

ne comments?

regards,

Anas

|||

Zadoras wrote:

I am using data connections ( in each package) which is referencing to one of the datasource.... i used this becuase i thought there would be one object i.e. Data Source (for creating connection with SQL sever or ne other database) and that object reference would be used in each package via Connection manager, and that makes it memory efficient because u would not have more objects (connection objects) in memory as connection manager is just a reference to that Data Source Object... connection string can be set up dynamicaly to point that connection object to a database and if connection string changes at run time even then there would be only one object (just pointing to differet database)

can ne one tell me isnt that right? as i hv read on this thread that it copies connection from data source to connection manager .... can ne one tell me ne article in that issue?

ne comments?

regards,

Anas

ne comments please...

Data Source and Connection Manager

Hi all,

I don't really understand the difference between the "data source" and the "connection manager" in a SSIS solution.

I tried to create a data source (if I understood correctly, I will ba able to share that connection between all packages in the solution), then I create a new connection manager inside a package. But when I make a modification in the data source, nothing change in the connection manager ?!!?

What I think is that when I'm making a new connection manager, what's really happen is a copy of the data source, is that correct ? If it is I dont't understand the avantage of making a new data source ....

Thanks for your response

In my personal opinion, there is no advantage of data sources. They were created by the Analysis Services team and the Integration Services thought tthey looked like something nice they could use. Rmemeber that they were designed for SSAS though.

In my opinion - don't use them.

-Jamie

|||

jh0483 wrote:

Hi all,

What I think is that when I'm making a new connection manager, what's really happen is a copy of the data source, is that correct ?

That's correct.

I don't use them either. You can run into additional issues when the properties either from the connection manager or from the data source changes; you will start receiving annoying notifications/warnings that your data source and connection manager are not sync every time you open the package.

|||

I think of it like this. You will always a have a connection, but you can attribute the connection as being linked to the data source. See the DataSourceID of a connection. This property maintains the link between the physical package related structure and the ephemeral design-time only data source.

Much like the others I never found it offered much. The design-time management of connections can be achieved through the same methods as you would use for the deployed run-time stuff, i.e. configurations.

|||But for me there is a big advantage : if you have many packages sharing the same connections and you have to change the connection, then you just have to change it in one place. If you use only connection manager, then you have to check all your packages.|||

jh0483 wrote:

But for me there is a big advantage : if you have many packages sharing the same connections and you have to change the connection, then you just have to change it in one place.

Have you ever used or at least looked into configurations? They allow you to manage connections through a single point, across multiple packlages and even solutions. They also have the huge benefit of working at runtime, and are a very valuable tool when it comes to deploying your packages.

Use both if you want, but without configurations you will have to make changes as packages get deployed between environments. Data sources do not solve that for you in any effecitive manner. Configurations do, and for my money work just as well when devloping packages.

|||Of course I use configurations ...

But still in your configuration file, if you have 5 packages with 5 connection manager you will have to configure 5 time the connection, which is not the case with the Data source.|||

Ok, good so you hopefully see the benefit of configurations. Your point seemed to be stating that there was a benefit of data sources that was not available with configurations, that was what I disagreed with.

If you have 5 packages and 5 connections, that are the same, presumably linked to the same Data Source to give you that change once, update all effect. This is not solely a benefit of data sources. You can get the same effect by using the same configuration for all five connections. If the connection string changes you update the one configuration store. Whenever the 5 packages are now opened the configuration applies the change, and without the annoying dialog you get with a data source warning you or the discrepancy between the updated data source and soon to be updated connection.

The benefit for me of configurations over data sources is that I can use that same mechanism to change connection strings simply as packages move between development, testing and production for example. Using data sources would require you to open the package in the designer and actually modify the package, albeit with minimal user input since the data source does the hard work, but still having to change packages as part of a deployment process is just wrong in my opinion.

Is your point that that each time you add a distinct connection to a package you need to set that connection as being controlled by a configuration? Data sources will avoid that I agree. Still for me I will always use configurations to solve the deployment issue, so I will incur that cost anyway. It is minimal since there is virtually no reason to have more than one connection object per connection string as SSIS will by default create multiple instances of that connection. This is of course a change from the DTS world if you used that previously, where it was actually good practice to duplicate connections.

Do you see any other benefits of data sources over configurations for connection management?

|||

I am using data connections ( in each package) which is referencing to one of the datasource.... i used this becuase i thought there would be one object i.e. Data Source (for creating connection with SQL sever or ne other database) and that object reference would be used in each package via Connection manager, and that makes it memory efficient because u would not have more objects (connection objects) in memory as connection manager is just a reference to that Data Source Object... connection string can be set up dynamicaly to point that connection object to a database and if connection string changes at run time even then there would be only one object (just pointing to differet database)

can ne one tell me isnt that right? as i hv read on this thread that it copies connection from data source to connection manager .... can ne one tell me ne article in that issue?

ne comments?

regards,

Anas

|||

Zadoras wrote:

I am using data connections ( in each package) which is referencing to one of the datasource.... i used this becuase i thought there would be one object i.e. Data Source (for creating connection with SQL sever or ne other database) and that object reference would be used in each package via Connection manager, and that makes it memory efficient because u would not have more objects (connection objects) in memory as connection manager is just a reference to that Data Source Object... connection string can be set up dynamicaly to point that connection object to a database and if connection string changes at run time even then there would be only one object (just pointing to differet database)

can ne one tell me isnt that right? as i hv read on this thread that it copies connection from data source to connection manager .... can ne one tell me ne article in that issue?

ne comments?

regards,

Anas

ne comments please...

Data Source and Connection Manager

Hi all,

I don't really understand the difference between the "data source" and the "connection manager" in a SSIS solution.

I tried to create a data source (if I understood correctly, I will ba able to share that connection between all packages in the solution), then I create a new connection manager inside a package. But when I make a modification in the data source, nothing change in the connection manager ?!!?

What I think is that when I'm making a new connection manager, what's really happen is a copy of the data source, is that correct ? If it is I dont't understand the avantage of making a new data source ....

Thanks for your response

In my personal opinion, there is no advantage of data sources. They were created by the Analysis Services team and the Integration Services thought tthey looked like something nice they could use. Rmemeber that they were designed for SSAS though.

In my opinion - don't use them.

-Jamie

|||

jh0483 wrote:

Hi all,

What I think is that when I'm making a new connection manager, what's really happen is a copy of the data source, is that correct ?

That's correct.

I don't use them either. You can run into additional issues when the properties either from the connection manager or from the data source changes; you will start receiving annoying notifications/warnings that your data source and connection manager are not sync every time you open the package.

|||

I think of it like this. You will always a have a connection, but you can attribute the connection as being linked to the data source. See the DataSourceID of a connection. This property maintains the link between the physical package related structure and the ephemeral design-time only data source.

Much like the others I never found it offered much. The design-time management of connections can be achieved through the same methods as you would use for the deployed run-time stuff, i.e. configurations.

|||But for me there is a big advantage : if you have many packages sharing the same connections and you have to change the connection, then you just have to change it in one place. If you use only connection manager, then you have to check all your packages.|||

jh0483 wrote:

But for me there is a big advantage : if you have many packages sharing the same connections and you have to change the connection, then you just have to change it in one place.

Have you ever used or at least looked into configurations? They allow you to manage connections through a single point, across multiple packlages and even solutions. They also have the huge benefit of working at runtime, and are a very valuable tool when it comes to deploying your packages.

Use both if you want, but without configurations you will have to make changes as packages get deployed between environments. Data sources do not solve that for you in any effecitive manner. Configurations do, and for my money work just as well when devloping packages.

|||Of course I use configurations ...

But still in your configuration file, if you have 5 packages with 5 connection manager you will have to configure 5 time the connection, which is not the case with the Data source.|||

Ok, good so you hopefully see the benefit of configurations. Your point seemed to be stating that there was a benefit of data sources that was not available with configurations, that was what I disagreed with.

If you have 5 packages and 5 connections, that are the same, presumably linked to the same Data Source to give you that change once, update all effect. This is not solely a benefit of data sources. You can get the same effect by using the same configuration for all five connections. If the connection string changes you update the one configuration store. Whenever the 5 packages are now opened the configuration applies the change, and without the annoying dialog you get with a data source warning you or the discrepancy between the updated data source and soon to be updated connection.

The benefit for me of configurations over data sources is that I can use that same mechanism to change connection strings simply as packages move between development, testing and production for example. Using data sources would require you to open the package in the designer and actually modify the package, albeit with minimal user input since the data source does the hard work, but still having to change packages as part of a deployment process is just wrong in my opinion.

Is your point that that each time you add a distinct connection to a package you need to set that connection as being controlled by a configuration? Data sources will avoid that I agree. Still for me I will always use configurations to solve the deployment issue, so I will incur that cost anyway. It is minimal since there is virtually no reason to have more than one connection object per connection string as SSIS will by default create multiple instances of that connection. This is of course a change from the DTS world if you used that previously, where it was actually good practice to duplicate connections.

Do you see any other benefits of data sources over configurations for connection management?

|||

I am using data connections ( in each package) which is referencing to one of the datasource.... i used this becuase i thought there would be one object i.e. Data Source (for creating connection with SQL sever or ne other database) and that object reference would be used in each package via Connection manager, and that makes it memory efficient because u would not have more objects (connection objects) in memory as connection manager is just a reference to that Data Source Object... connection string can be set up dynamicaly to point that connection object to a database and if connection string changes at run time even then there would be only one object (just pointing to differet database)

can ne one tell me isnt that right? as i hv read on this thread that it copies connection from data source to connection manager .... can ne one tell me ne article in that issue?

ne comments?

regards,

Anas

|||

Zadoras wrote:

I am using data connections ( in each package) which is referencing to one of the datasource.... i used this becuase i thought there would be one object i.e. Data Source (for creating connection with SQL sever or ne other database) and that object reference would be used in each package via Connection manager, and that makes it memory efficient because u would not have more objects (connection objects) in memory as connection manager is just a reference to that Data Source Object... connection string can be set up dynamicaly to point that connection object to a database and if connection string changes at run time even then there would be only one object (just pointing to differet database)

can ne one tell me isnt that right? as i hv read on this thread that it copies connection from data source to connection manager .... can ne one tell me ne article in that issue?

ne comments?

regards,

Anas

ne comments please...

data shown in form is #deleted

when user leave's the application for while connection to server stops
and data shown in form is #deleted
how to prevent it?
sam
You didn't indicate what application you are using or how
you are accessing the SQL Server data.
But it can be caused by several things - generally related
to indexing - such as using a float as the index or as part
of the index or having nulls as values in part of the index.
There were similar to this when using Bigints with some apps
which wouldn't map the data type correctly. That's generally
fixed in service packs by now depending on what you are
using. If you are using Access, make sure you have the
latest Jet service pack.
ODBC is key-set driven and fetches are generally done in two
steps based upon the unique index of the table where first
it grabs the index and then it goes back, looks for the
index and gets the rest of the row based on the index. If
it can't find the index or gets 'confused' on the index in
the second step (floats can confuse it as floats are
approximate data types), it will assume the record has been
deleted.
-Sue
On Mon, 12 Dec 2005 12:33:35 +0200, "Sam"
<focus10@.zahav.net.il> wrote:

>when user leave's the application for while connection to server stops
>and data shown in form is #deleted
>how to prevent it?
>sam
>
|||In addition to Sue's comments, if you are using Access, you should add a
timestamp field to all of your Access linked tables. Access uses the
timestamp field to determine if the data has changed since it was last
fetched.
"Sam" <focus10@.zahav.net.il> wrote in message
news:O2mNQew$FHA.2036@.TK2MSFTNGP14.phx.gbl...
> when user leave's the application for while connection to server stops
> and data shown in form is #deleted
> how to prevent it?
> sam
>

data shown in form is #deleted

when user leave's the application for while connection to server stops
and data shown in form is #deleted
how to prevent it?
samYou didn't indicate what application you are using or how
you are accessing the SQL Server data.
But it can be caused by several things - generally related
to indexing - such as using a float as the index or as part
of the index or having nulls as values in part of the index.
There were similar to this when using Bigints with some apps
which wouldn't map the data type correctly. That's generally
fixed in service packs by now depending on what you are
using. If you are using Access, make sure you have the
latest Jet service pack.
ODBC is key-set driven and fetches are generally done in two
steps based upon the unique index of the table where first
it grabs the index and then it goes back, looks for the
index and gets the rest of the row based on the index. If
it can't find the index or gets 'confused' on the index in
the second step (floats can confuse it as floats are
approximate data types), it will assume the record has been
deleted.
-Sue
On Mon, 12 Dec 2005 12:33:35 +0200, "Sam"
<focus10@.zahav.net.il> wrote:

>when user leave's the application for while connection to server stops
>and data shown in form is #deleted
>how to prevent it?
>sam
>|||In addition to Sue's comments, if you are using Access, you should add a
timestamp field to all of your Access linked tables. Access uses the
timestamp field to determine if the data has changed since it was last
fetched.
"Sam" <focus10@.zahav.net.il> wrote in message
news:O2mNQew$FHA.2036@.TK2MSFTNGP14.phx.gbl...
> when user leave's the application for while connection to server stops
> and data shown in form is #deleted
> how to prevent it?
> sam
>

data reader source in data flow problem

hi all,

i have a package in ssis that needs to deliver data from outside servers with odbc connection. i have desined the package with dataflow object that includes inside a datareader source. the data reader source connect via ado.net odbc connection to the ouside servers and makes a query like: select * from x where y=? and then i pass the data to my sql server. my question is like the following:

how do i config the datasource reader or the dataflow so it will recognize an input value to my above query? i.e for example:

select * from x where y=5 (5 is a global variable that i have inside the package). i did not see anywhere where can i do it.

please help,

tomer

You can set an expression on the SQLCommand property. Similar to as explained here: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

The difference with the datareader source is that you cannot use a variable. Instead, set the expression on the SQLCommand property via the expressions of the parent data-flow.

-Jamie

|||

hi jamie,

thank you for your advice but i still did not understand completely.

the example u sent me is with ole db data source. lets say i have created the variable

and put there the select. now how do i config the data flow to use it?

can you give me another example?

thx,

Tomer

|||

Like I said, the Datareader Source does not allow you to use a variable as input. So, instead of putting the expression that builds your SQL statement into a variable, put it in an expression that sets the SQLCommand property of your Datareader Source. The interface to setting this expression is in the properties pane of the Data Flow task in which the Datareader Source resides.

-Jamie

|||

hi again Jamie,

i have put there:

"select * from x where y=" + @.[user::max_date]

and i get an error.

can u advice?

|||

Is max_date a Datetime variable? If so you need to cast it as a string in order to be able to concatenate it.

-Jamie

|||

Jamie,

thank you very much for your help - you realy helped me here and saved me lots of time.

tomer

|||

Hi Jamie,

yesterday everyyhing worked fine and suddenly i get in the package VS_BROKEN?

why can u advice me on that please?

tomer

|||

You have made a change in one of your components that means it won't work anymore. It should tell you which component.

-Jamie

|||

it says its failed executing the SQL command at the data reader source and i get validation error VS_BROKEN/ my sql is like yesterday:

"select * from x where y = " + [user::x]

when x is a string type. i must have changed somthing small without noticing...?

|||Are you missing the @. before the variable name (should be "@.[user::x]" ) in your sql command, or is that just a typo in your post?

Data Reader Source cannot be configured

Hi,

I am using a Ado Connection Manager to connect to a M S Access source.

But when I use this connection Manager in Data Reader Source, I am Not able to Configure Data reader Source. It gives exception "Cannot Acquire Managed Connection From Run Time Connection Manager".

Can anyone help on this.

Thanks

Dharmbir

Wenyang,

The .mdb Does not matter, If I create a ADO Connection Manager, Data Reader Does Not Support.

Thanks

Dharmbir

|||I have new Integration Services Project in Microsoft Visual Studio version 8.0.50727.42, under .NET framework version 2.0.50727. I'm trying to set up a simple replication from an ODBC data source to a new MSSQL 2005 database. I created two entries in the Connection Managers list and successfully tested the connection for each one.

I see the same "Cannot acquire a managed connection from the run-time connection manager." error after setting the Connection Manager in a DataReader Source object's Advanced Editor dialog box to either entry in Connection Managers. The DataReader Source appears to be the only Data Flow Source that will allow me to connect to an ODBC datasource.

Is there a log somewhere or a list of possible reasons for this inability to connect? Does Visual Studio allow for the creation of a package that uses a non-managed connection?

Also, I made a data source for each of my connection managers in the Data Sources folder of my project. Are they necessary?

Thanks for any help you can give us.|||

It works for me fine. There are a couple things to check

1) You used ".Net Provider for OleDb/Microsoft Jet 4.0 OLEDB Provider", correct?

2) When you created your ADO.Net connection, did you set username/passwd correctly - did "Test Connection" return success?

Thanks

Wenyang

|||

Hi Wenyang,

1.) I used ADO\Microsoft Jet 4.0 OLEDB Provider

2.) Test connection shows its passed.

Thanks

Dharmbir

|||

If picking which .mdb file does not matter the repro, please pass me a small sample mdb which failed for you and I'll try to see why we saw different results. Pls send to Wenyang.Hu at Microsoft.com

Thanks

Wenyang

Data Reader Source cannot be configured

Hi,

I am using a Ado Connection Manager to connect to a M S Access source.

But when I use this connection Manager in Data Reader Source, I am Not able to Configure Data reader Source. It gives exception "Cannot Acquire Managed Connection From Run Time Connection Manager".

Can anyone help on this.

Thanks

Dharmbir

It works for me fine. There are a couple things to check

1) You used ".Net Provider for OleDb/Microsoft Jet 4.0 OLEDB Provider", correct?

2) When you created your ADO.Net connection, did you set username/passwd correctly - did "Test Connection" return success?

Thanks

Wenyang

|||

Hi Wenyang,

1.) I used ADO\Microsoft Jet 4.0 OLEDB Provider

2.) Test connection shows its passed.

Thanks

Dharmbir

|||

If picking which .mdb file does not matter the repro, please pass me a small sample mdb which failed for you and I'll try to see why we saw different results. Pls send to Wenyang.Hu at Microsoft.com

Thanks

Wenyang

|||

Wenyang,

The .mdb Does not matter, If I create a ADO Connection Manager, Data Reader Does Not Support.

Thanks

Dharmbir

|||I have new Integration Services Project in Microsoft Visual Studio version 8.0.50727.42, under .NET framework version 2.0.50727. I'm trying to set up a simple replication from an ODBC data source to a new MSSQL 2005 database. I created two entries in the Connection Managers list and successfully tested the connection for each one.

I see the same "Cannot acquire a managed connection from the run-time connection manager." error after setting the Connection Manager in a DataReader Source object's Advanced Editor dialog box to either entry in Connection Managers. The DataReader Source appears to be the only Data Flow Source that will allow me to connect to an ODBC datasource.

Is there a log somewhere or a list of possible reasons for this inability to connect? Does Visual Studio allow for the creation of a package that uses a non-managed connection?

Also, I made a data source for each of my connection managers in the Data Sources folder of my project. Are they necessary?

Thanks for any help you can give us.sql

Data Reader Source cannot be configured

Hi,

I am using a Ado Connection Manager to connect to a M S Access source.

But when I use this connection Manager in Data Reader Source, I am Not able to Configure Data reader Source. It gives exception "Cannot Acquire Managed Connection From Run Time Connection Manager".

Can anyone help on this.

Thanks

Dharmbir

It works for me fine. There are a couple things to check

1) You used ".Net Provider for OleDb/Microsoft Jet 4.0 OLEDB Provider", correct?

2) When you created your ADO.Net connection, did you set username/passwd correctly - did "Test Connection" return success?

Thanks

Wenyang

|||

Hi Wenyang,

1.) I used ADO\Microsoft Jet 4.0 OLEDB Provider

2.) Test connection shows its passed.

Thanks

Dharmbir

|||

If picking which .mdb file does not matter the repro, please pass me a small sample mdb which failed for you and I'll try to see why we saw different results. Pls send to Wenyang.Hu at Microsoft.com

Thanks

Wenyang

|||

Wenyang,

The .mdb Does not matter, If I create a ADO Connection Manager, Data Reader Does Not Support.

Thanks

Dharmbir

|||I have new Integration Services Project in Microsoft Visual Studio version 8.0.50727.42, under .NET framework version 2.0.50727. I'm trying to set up a simple replication from an ODBC data source to a new MSSQL 2005 database. I created two entries in the Connection Managers list and successfully tested the connection for each one.

I see the same "Cannot acquire a managed connection from the run-time connection manager." error after setting the Connection Manager in a DataReader Source object's Advanced Editor dialog box to either entry in Connection Managers. The DataReader Source appears to be the only Data Flow Source that will allow me to connect to an ODBC datasource.

Is there a log somewhere or a list of possible reasons for this inability to connect? Does Visual Studio allow for the creation of a package that uses a non-managed connection?

Also, I made a data source for each of my connection managers in the Data Sources folder of my project. Are they necessary?

Thanks for any help you can give us.