Hi all, I need to set up a OLEDB connection manager and programmatically setting up its connectionstring. I'm working with SSIS 2008 an set up a new OLEDB connection from Connection Managers window with "SQL Server Native Client 10.0" Provider ang colled it SourceConnOLEDB. I tested connection by "Test Connection" button and works fine. Then, from Properties window, I set Expression for "ConnectionString" Property as Expression @[User::MyDinamicConnString], and then with "Evaluate Expression" button I obtain this Expression Value: Data Source=MYSERVERNAME;Initial Catalog=MYDB;Integrated Security=False;User ID=sa;Password=password; Otherwise, in a DataFlow task, select an OLEDB DataSource and set for it SourceConnOLEDB as source connection, but when I execute the SSIS package, it fails with the following error: TITLE: Microsoft Visual Studio ------------------------------ Error at MySSIS_001 [Connection manager "SourceConnOLEDB"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Error at Data Flow Task [OLEDB Source [400]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnOLEDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. ------------------------------ ADDITIONAL INFORMATION: Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap) ------------------------------ BUTTONS: OK ------------------------------ I tried the same settings with an ADO.NET connection and works fine, but I have to use an OLEDB connection because the dataflow task uses custom SQL statement with input parameters. Can you help me? Thank you so much!

does the value of the variable @[User::MyDinamicConnString] change before execution of the data flow task??

Hi Raj, I added the following information: I tried the same settings with an ADO.NET connection and works fine, but I have to use an OLEDB connection because the dataflow task uses custom SQL statement with input parameters. The variable @[User::MyDinamicConnString] correctly changes and with ADO.NET connection correctly switches on all DB connection String.

Use this connection string "Data Source=MYSERVERNAME;Initial Catalog=MYDB;Integrated Security=False;User ID=sa;Password=password;Provider=SQLNCLI.1" then try the OLEDB Connection

Use this connection string "Data Source=MYSERVERNAME;Initial Catalog=MYDB;Integrated Security=False;User ID=sa;Password=password;Provider=SQLNCLI.1" then try the OLEDB Connection It doesn't work. With "Provider=SQLNCLI.1" The returned error is: 0xC0209302 DTS_E_OLEDB_NOPROVIDER_ERROR SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR. The requested OLE DB provider %2 is not registered. Error code: 0x%1!8.8X!. If I try with "Provider=SQLNCLI10.1" have this error: 0xC0202009 DTS_E_OLEDBERROR SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x%1!8.8X!.

Thanks Onur. Although my problem was different, the first two lines in your reply inspired me to find a solution to my problem! Great stuff!All the best.