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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Some options    You can use the Execute Package Utility to change your datasource  before running the package   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  You can use the SSIS XML package configuration file  Here is a walk through  You can save your configrations in a database table

[ssis] SSIS how to set connection string dynamically from a config file

Examples related to ssis

Examples related to connection-string

Examples related to configuration-files