2

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)

Improve this question
5
  • If you are looking to change the provider check the following link: social.msdn.microsoft.com/Forums/sqlserver/en-US/… Commented May 8, 2019 at 11:39
  • 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 Commented May 8, 2019 at 13:43
  • @Mags can you provide some screenshot of the data flow and foreach loop container and the connection expression? Commented May 8, 2019 at 15:11
  • How are the variables populated? And is this mapped in the Foreach Loop? Commented May 8, 2019 at 16:00
  • Anyone out there who can help:) Commented May 13, 2019 at 6:14

1 Answer 1

Reset to default
0

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;"
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks for your reply, but still the samme error. SQL Server Native Client 11 is in the connection string
@Mags from the error message you are using Microsoft OLE DB Provider for ODBC Driver even if native client is provided in the connection string
I have changed to one connection string in the connection properties, but new error message.
@Mags check this link stackoverflow.com/questions/32701696/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.