SSIS how to set connection string dynamically from a config file

Question

I am using SQL Server Integration Services (SSIS) in SQL Server Business Intelligent Development Studio.

I need to do a task that is as follows. I have to read from a source database and put it into a destination flat file. But at the same time the source database should be configurable.

That means in the OLEDB Connection Manager, the connection string should change dynamically. This connection string should be taken from a configuration/XML/flat file.

I read that I can use variables and expressions to change the connection string dynamically. But how do I read the connection string value from a config/XML/flat file and set the variable?

This part I am unable to do. Is this the right way to achieve this? Can we add web.config files to an SSIS project?

Answer 1

First add a variable to your SSIS package (Package Scope) - I used FileName, OleRootFilePath, OleProperties, OleProvider. The type for each variable is "string". Then I create a Configuration file (Select each variable - value) - populate the values in the configuration file - Eg: for OleProperties - Microsoft.ACE.OLEDB.12.0; for OleProperties - Excel 8.0;HDR=, OleRootFilePath - Your Excel file path, FileName - FileName

In the Connection manager - I then set the Properties-> Expressions-> Connection string expression dynamically eg:

 "Provider=" + @[User::OleProvider] + "Data Source=" + @[User::OleRootFilePath] + @[User::FileName]  + ";Extended Properties=\"" + @[User::OleProperties] + "NO \""+";" 

This way once you set the variables values and change it in your configuration file - the connection string will change dynamically - this helps especially in moving from development to production environments.