Problem
After we develop SSIS Packages, if we want to move developed packages to another server/ environment ,we need to change so many configuration settings in each SSIS Packages like DB Connection strings, File path and etc. But there is so much of work around this task. How we can take the advantage of the SQL Server Package Configuration which are providing by SSIS ?
Solution
SSIS offers serveral types of package configurations like environment variables,Parent Package and XML Configurations and SQL Server Configuration . In all types of configurations , parameter values are storing outside of SSIS packages and that values are utilizing while executing packages. In this post i am going to explain an approach for implementing SQL Server based package configuration that uses an environment variable to facilitate the deployment of the packages onto different servers without having to modify the packages
What is SQL Server package configuration ?
SQL Server package configuration is nothing but storing configuration values like Connection strings ,File path etc in SQL server table. To utlize this values inside SSIS Packages, we need to create one configuration connection manager inside SSIS package which tells, in which server and which database this SSIS Package Configuration table is exists. lets call this connection name as 'ConfigConnection'.
What is Environment Variable ?
Environment Variable is an variable created at machine level(Operating System level) and we can give the value to the Environment Variable and can use this value inside any of the program which are runing in the machine . In our example we will use SSIS_CONFIG_DB Environment Variable inside our SSIS package. So i have created one SSIS_CONFIG_DB Environment Variable at machine level and given below value
Data Source=localhost;Initial Catalog=SSISConfig;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;
We will see later in post how to create Environment Variable and edit value in this Variable
What is need of environment Variable to make use of SQL Server package configuration ?
As i explained above, to get the configuration values of SQL Server table , we have to create one connection (ConfigConnection ). Since during the development we are pointing to a development DB instance, we need to change the connection information point to the QA or Production source databases without editing the package each time.
To notify what is connection string of ConfigConnection to SSIS package , we have to use environment variable which is already having the connection string value This allows for the package to point to configuration table based on Environment (dev, QA, test, prod, etc).Each configuration table shall have the configuration values that are appropriate to each environment.The environment variable shall exists in all machine where you intend to deploy the package, and its content should be updated to accordingly.
Advantages of SQL Server package configuration over the other package configurations :
Choose Advance System Settings
Choose Environment Variables
Sample Package Development :
Let’s assume we have a package that has a connection manager called ‘SourceConnection’, which has the connection information of the source DataBase where the package is trying to retreive data from. Also we have one more connection manager called ‘DestinationConnection’ where the data is going to be load.
Screenshot 1: Sample Package which has one Data flow task , two OLEDB connections i.e. one Source Connection and one destination connection
Screenshot 3 : Right Click on Control flow task window and choose Package Configuration tab
Screenshot 5 : Click on Environment Variable drop down and Choose previously created SSIS_Config_DB variable
Screenshot 8 : Choose SQL Server type in Configuration Type And in Specify Configuration tab, Choose ConfigConnection String and Choose Configuration Table (for first time we need to create new table ,by clicking New else we can choose from drop down)
Screenshot 11: Repeat same steps for Destination Connection Also
After we develop SSIS Packages, if we want to move developed packages to another server/ environment ,we need to change so many configuration settings in each SSIS Packages like DB Connection strings, File path and etc. But there is so much of work around this task. How we can take the advantage of the SQL Server Package Configuration which are providing by SSIS ?
Solution
SSIS offers serveral types of package configurations like environment variables,Parent Package and XML Configurations and SQL Server Configuration . In all types of configurations , parameter values are storing outside of SSIS packages and that values are utilizing while executing packages. In this post i am going to explain an approach for implementing SQL Server based package configuration that uses an environment variable to facilitate the deployment of the packages onto different servers without having to modify the packages
What is SQL Server package configuration ?
SQL Server package configuration is nothing but storing configuration values like Connection strings ,File path etc in SQL server table. To utlize this values inside SSIS Packages, we need to create one configuration connection manager inside SSIS package which tells, in which server and which database this SSIS Package Configuration table is exists. lets call this connection name as 'ConfigConnection'.
What is Environment Variable ?
Environment Variable is an variable created at machine level(Operating System level) and we can give the value to the Environment Variable and can use this value inside any of the program which are runing in the machine . In our example we will use SSIS_CONFIG_DB Environment Variable inside our SSIS package. So i have created one SSIS_CONFIG_DB Environment Variable at machine level and given below value
Data Source=localhost;Initial Catalog=SSISConfig;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;
We will see later in post how to create Environment Variable and edit value in this Variable
What is need of environment Variable to make use of SQL Server package configuration ?
As i explained above, to get the configuration values of SQL Server table , we have to create one connection (ConfigConnection ). Since during the development we are pointing to a development DB instance, we need to change the connection information point to the QA or Production source databases without editing the package each time.
To notify what is connection string of ConfigConnection to SSIS package , we have to use environment variable which is already having the connection string value This allows for the package to point to configuration table based on Environment (dev, QA, test, prod, etc).Each configuration table shall have the configuration values that are appropriate to each environment.The environment variable shall exists in all machine where you intend to deploy the package, and its content should be updated to accordingly.
Advantages of SQL Server package configuration over the other package configurations :
- DBAs are usually more comfortable working with SQL Server tables than XML files.
- Storing package configuration values in a SQL Server database rather than on the file system
- Simple T-SQL commands such as INSERT, UPDATE and DELETE.
Choose Advance System Settings
Choose Environment Variables
Let’s assume we have a package that has a connection manager called ‘SourceConnection’, which has the connection information of the source DataBase where the package is trying to retreive data from. Also we have one more connection manager called ‘DestinationConnection’ where the data is going to be load.
Screenshot 1: Sample Package which has one Data flow task , two OLEDB connections i.e. one Source Connection and one destination connection
Screenshot 3 : Right Click on Control flow task window and choose Package Configuration tab
Screenshot 5 : Click on Environment Variable drop down and Choose previously created SSIS_Config_DB variable
Screenshot 6 : In next wizard screen choose ConnectionString of ConfigConnection under Connection Manager section and click Next and in next screen give name as ConfigConnection and click Ok to save .
Screenshot 7 : Again go to Package Configuration wizard and click on Add to add new configuration for Source
Screenshot 9: Choose Configuration Filter from drop down list (for new record entry in table just type 'Source' then it will insert one entry in Configuration table else choose from drop down list) to utilize value from SQL Server table dynamically , when package is executing
Screenshot 10 : Next screen , choose SourceConnection ConnectionString to copy dynamically from the SQL Server table value to SourceConnection ConnectionString
Screenshot 11: Repeat same steps for Destination Connection Also
Once we have developed our packages like above, if we want to deploy to another server like QA or Production , We just need to Create One Environment Varible at Server level and need to change connection strings values in Configuration table
Note : Once we create Environment Varible at new Environment , we need to restart that machine to identify in SSIS Packages
Kiran Reddy A
Kiran has been a part of the IT industry for more than six years and Involved in many MSBI projects and product development with extensive usage of ETL and Reporting tools like SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Service (SSRS) and T-SQL
Get Free Email Updates to your Inbox!
