SSIS Foreach Loop Container OLE DB- Problem with dynamic connection

2
votes

I am setting up a new datawarehouse (Server 1) and need to get data from 3 different databases on the same server (Server 2). Everything works good until until I try to make the connection dynamic.

I have created a Foreach Loop Container FLC including 4 variables

  • Servername
  • Database
  • User
  • Pw

In the FLC I have added a data flow task DFT, and in the DFT added a OLE DB Source enter image description here enter image description here enter image description here enter image description here

In the OLE DB Source connection properties I have added the the 1 variables as expression. Directly the connection goes to offline and OLE DB Source gives an error. Error message:

Error at Data Flow Task [OLE DB Source 5]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Test" failed with error code 0xC0014041. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Error at Data Flow Task [SSIS.Pipeline]: OLE DB Source failed validation and returned error code 0xC020801C.

Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

Error at Package: The connection manager "Test" will not acquire a connection because the connection manager OfflineMode property is TRUE. When the OfflineMode is TRUE, connections cannot be acquired.

(Microsoft.DataTransformationServices.VsIntegration)

1
ssisexpressionforeach-loop-container
If you are looking to change the provider check the following link: social.msdn.microsoft.com/Forums/sqlserver/en-US/… - Yahfoufi
The correct provider (SQL Server Native Client 11,0) is selected when I create the connection, but when I make it dynamic it gets destoyed and gives error - Mags
@Mags can you provide some screenshot of the data flow and foreach loop container and the connection expression? - Hadi
How are the variables populated? And is this mapped in the Foreach Loop? - userfl89
Anyone out there who can help:) - Mags

1 Answers

0
votes

First of all, you are using the following provider which is not adequate:

Microsoft OLE DB Provider for ODBC Drivers

Instead, select SQL Server Native Client 11 in the connection manager.

Next step, is to change the Data Flow Task and OLEDB Source Delay Validation property to True since if the variables defauly values are empty it will throw an error on the package validation phase (once executed)

Update 1 - ConnectionString property

Instead of assigning expressions to multiple connection manager properties, use the connectionstring expression:

"Data Source=" + @[User::SERVER] + ";User ID=" + @[User::USERNAME] + ";Password=" + @[User::PASSWORD] +";Initial Catalog=" + @[User::DATABASE] + ";Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;"