SSIS Foreach Loop Container OLE DB- Problem with dynamic connection

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

In the OLE DB Source connection properties I have added the the 4 variables as expression. I have also tested with 1 variable as connectionstring, same issue.

Directly the connection goes to offline and OLE DB Source gives an error. Error message:

TITLE: Microsoft Visual Studio

---

Exception from HRESULT: 0xC020801C Error at Package [Connection manager "server.database.user"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

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

---

BUTTONS:

Ok

ConnectionString:

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

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;"