SSIS how to set connection string dynamically from a config file

41
votes

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?

5
ssisconnection-stringconfiguration-files

5 Answers

33
votes

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.

26
votes

Some options:

  1. You can use the Execute Package Utility to change your datasource, before running the package.

  2. You can run your package using DTEXEC, and change your connection by passing in a /CONNECTION parameter. Probably save it as a batch so next time you don't need to type the whole thing and just change the datasource as required.

  3. You can use the SSIS XML package configuration file. Here is a walk through.

  4. You can save your configrations in a database table.

10
votes

Here's some background on the mechanism you should use, called Package Configurations: Understanding Integration Services Package Configurations. The article describes 5 types of configurations:

  • XML configuration file
  • Environment variable
  • Registry entry
  • Parent package variable
  • SQL Server

Here's a walkthrough of setting up a configuration on a Connection Manager: SQL Server Integration Services SSIS Package Configuration - I do realize this is using an environment variable for the connection string (not a great idea), but the basics are identical to using an XML file. The only step(s) you have to change in that walkthrough are the configuration type, and then a path.

2
votes

Goto Package properties->Configurations->Enable Package Configurations->Add->xml configuration file->Specify dtsconfig file->click next->In OLEDB Properties tick the connection string->connection string value will be displayed->click next and finish package is hence configured.

You can add Environment variable also in this process

0
votes

These answers are right, but old and works for Depoloyement Package Model. What I Actually needed is to change the server name, database name of a connection manager and i found this very helpful:

https://www.youtube.com/watch?v=_yLAwTHH_GA

Better for people using SQL Server 2012-2014-2016 ... with Deployment Project Model