SSIS – Combination of Parent Package Configuration and SQL Server Package Configuration along with Environment variable

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

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.




author

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!

www.CodeNirvana.in

Powered by Blogger.

Translate

Total Pageviews

Copyright © Journey to MSBI World With Kiran Reddy | Blogger Templates | Designed By Code Nirvana