Problem
As explained in this post SQL Server Package Configuration,we can able to make use of SQL Server configuration table values inside SSIS packages by using Environment variables and SQL server configuration . In SSIS Configuration table , it is storing one record for one package variable. If i have 20 + packages , which are Source and destination connections. then we have to create 40 + configurations in SISConfiguration table , even though all source and destinations are same because SSIS Configuration table is saving configurations based on Package variables.
Solution
By using Parent Package variable we can able to use only two records in SQL Server Configuration table instead of each package connection string i.e. one record for Source connection and another for Destination Connection.
What is Parent Package Variable
If a package calls another package using execute package task, then calling package is referred as Parent package and called package is referred as child package. When parent package variable is to be passed to a child package then it is called Parent Package Variable configuration
Sample Package Development :
To explain this , I have created two Packages . One is called as MasterPackage and Another one is called ChildPackage.
Master Package Setup :
In Master Package , i have taken simple execute package task and configure like below . Choose Location of package in File System and Browse child Package and Click on OK to complete .
Set Up Environment Variable in Master Package :
Screenshot 1 : Right Click on Control flow task window and choose Package Configuration tab
Screenshot 3 : Click on Environment Variable drop down and Choose previously created SSIS_Config_DB variable
Set Up SQL Server Configuration in Master Package :
Screenshot 1 : Right Click on Control flow task window and choose Variables and created two variables SourceCon and DestCon. In this two variables , values inside SQL Server Configuration tables will be copied by using SQL Server Configuration
Screenshot 2 : By using ConfigConnection manager ,Value of Souce Column inside SSISConfiguration Table will be Copied to package variable SourceCon and Repeated same steps for DestCon also
Child Package Set Up :
Following steps enable passing a parent package variable to child package:
1. Add a configuration to the child package
2. Select its configuration type as Parent Package Variable
3. Give the name of the parent package variable as 'SourceCon'
4. Click Next and select the Child package SourceConnection ConnectionString as shown in below (by doing this, the Child packageSourceConnection ConnectionString's value
gets overwritten with the value of parent package variable).Continue for DestCon also.
As explained in this post SQL Server Package Configuration,we can able to make use of SQL Server configuration table values inside SSIS packages by using Environment variables and SQL server configuration . In SSIS Configuration table , it is storing one record for one package variable. If i have 20 + packages , which are Source and destination connections. then we have to create 40 + configurations in SISConfiguration table , even though all source and destinations are same because SSIS Configuration table is saving configurations based on Package variables.
Solution
By using Parent Package variable we can able to use only two records in SQL Server Configuration table instead of each package connection string i.e. one record for Source connection and another for Destination Connection.
What is Parent Package Variable
If a package calls another package using execute package task, then calling package is referred as Parent package and called package is referred as child package. When parent package variable is to be passed to a child package then it is called Parent Package Variable configuration
Sample Package Development :
To explain this , I have created two Packages . One is called as MasterPackage and Another one is called ChildPackage.
Master Package Setup :
In Master Package , i have taken simple execute package task and configure like below . Choose Location of package in File System and Browse child Package and Click on OK to complete .
Set Up Environment Variable in Master Package :
Screenshot 1 : Right Click on Control flow task window and choose Package Configuration tab
Screenshot 3 : Click on Environment Variable drop down and Choose previously created SSIS_Config_DB variable
Screenshot 4 : 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 .
Set Up SQL Server Configuration in Master Package :
Screenshot 1 : Right Click on Control flow task window and choose Variables and created two variables SourceCon and DestCon. In this two variables , values inside SQL Server Configuration tables will be copied by using SQL Server Configuration
Screenshot 2 : By using ConfigConnection manager ,Value of Souce Column inside SSISConfiguration Table will be Copied to package variable SourceCon and Repeated same steps for DestCon also
Child Package Set Up :
Following steps enable passing a parent package variable to child package:
1. Add a configuration to the child package
2. Select its configuration type as Parent Package Variable
3. Give the name of the parent package variable as 'SourceCon'
4. Click Next and select the Child package SourceConnection ConnectionString as shown in below (by doing this, the Child packageSourceConnection ConnectionString's value
gets overwritten with the value of parent package variable).Continue for DestCon also.
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!
1 comments:
nice post
Reply