November 6, 2007 at 10:40 am
I have a dts package that is shipped with our product that is used to convert an old version DB to our current (completely new schema) DB. Both are SQL Server DBs. I need to convert this to SSIS. Each customer has there own servers and database names so they need to enter the connection information at runtime. The users need to be able to choose either SQL authentication or Windows Authentication. The package is called from a C# application. The C# application sets the Package variables and I have a ConnectionString expression that references those variables. The expression gets parsed correctly but the password gets stripped out of the connection string. When the package gets to the first data flow task I get the following error:
[OLE DB Destination [718]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DestDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Any help or suggestions on how I can programmatically set the connection string would be greatly appreciated. I cannot have the end users modifying XML files or modifying the dts package connections. It must be done through the application.
Thanks.
November 7, 2007 at 1:14 pm
Never mind. I found the problem. I needed to include ";Provider=SQLNCLI.1" at the end of the Expression and then it worked fine.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply