SQL SERVER – SSIS Parameters in Parent-Child ETL Architectures – Notes from the Field #040

[Notes from Pinal]: SSIS is very well explored subject, however, there are so many interesting elements when we read, we learn something new. A similar concept has been Parent-Child ETL architecture’s relationship in SSIS.

SQL SERVER - SSIS Parameters in Parent-Child ETL Architectures - Notes from the Field #040 Tim_Mitchell Linchpin People are database coaches and wellness experts for a data driven world. In this 40th episode of the Notes from the Fields series database expert Tim Mitchell (partner at Linchpin People) shares very interesting conversation related to how to understand SSIS Parameters in Parent-Child ETL Architectures.


In this brief Notes from the Field post, I will review the use of SSIS parameters in parent-child ETL architectures.

A very common design pattern used in SQL Server Integration Services is one I call the parent-child pattern. Simply put, this is a pattern in which packages are executed by other packages. An ETL infrastructure built using small, single-purpose packages is very often easier to develop, debug, and troubleshoot than large, monolithic packages. For a more in-depth look at parent-child architectures, check out my earlier blog post on this topic.

When using the parent-child design pattern, you will frequently need to pass values from the calling (parent) package to the called (child) package. In older versions of SSIS, this process was possible but not necessarily simple. When using SSIS 2005 or 2008, or even when using SSIS 2012 or 2014 in package deployment mode, you would have to create package configurations to pass values from parent to child packages. Package configurations, while effective, were not the easiest tool to work with. Fortunately, starting with SSIS in SQL Server 2012, you can now use package parameters for this purpose.

In the example I will use for this demonstration, I’ll create two packages: one intended for use as a child package, and the other configured to execute said child package. In the parent package I’m going to build a for each loop container in SSIS, and use package parameters to pass in a value – specifically, a ClientID – for each iteration of the loop. The child package will be executed from within the for each loop, and will create one output file for each client, with the source query and filename dependent on the ClientID received from the parent package.

Configuring the Child and Parent Packages

When you create a new package, you’ll see the Parameters tab at the package level. Clicking over to that tab allows you to add, edit, or delete package parameters.

SQL SERVER - SSIS Parameters in Parent-Child ETL Architectures - Notes from the Field #040 ssarch2

As shown above, the sample package has two parameters. Note that I’ve set the name, data type, and default value for each of these. Also note the column entitled Required: this allows me to specify whether the parameter value is optional (the default behavior) or required for package execution. In this example, I have one parameter that is required, and the other is not.

Let’s shift over to the parent package briefly, and demonstrate how to supply values to these parameters in the child package. Using the execute package task, you can easily map variable values in the parent package to parameters in the child package.

SQL SERVER - SSIS Parameters in Parent-Child ETL Architectures - Notes from the Field #040 ssarch1
The execute package task in the parent package, shown above, has the variable vThisClient from the parent package mapped to the pClientID parameter shown earlier in the child package. Note that there is no value mapped to the child package parameter named pOutputFolder. Since this parameter has the Required property set to False, we don’t have to specify a value for it, which will cause that parameter to use the default value we supplied when designing the child pacakge.

The last step in the parent package is to create the for each loop container I mentioned earlier, and place the execute package task inside it. I’m using an object variable to store the distinct client ID values, and I use that as the iterator for the loop (I describe how to do this more in depth here). For each iteration of the loop, a different client ID value will be passed into the child package parameter.

SQL SERVER - SSIS Parameters in Parent-Child ETL Architectures - Notes from the Field #040 ssarch3
The final step is to configure the child package to actually do something meaningful with the parameter values passed into it. In this case, I’ve modified the OleDB source query to use the pClientID value in the WHERE clause of the query to restrict results for each iteration to a single client’s data. Additionally, I’ll use both the pClientID and pOutputFolder parameters to dynamically build the output filename.

SQL SERVER - SSIS Parameters in Parent-Child ETL Architectures - Notes from the Field #040 ssarch4

As shown, the pClientID is used in the WHERE clause, so we only get the current client’s invoices for each iteration of the loop.

SQL SERVER - SSIS Parameters in Parent-Child ETL Architectures - Notes from the Field #040 ssarch5

For the flat file connection, I’m setting the Connection String property using an expression that engages both of the parameters for this package, as shown above.

Parting Thoughts

There are many uses for package parameters beyond a simple parent-child design pattern. For example, you can create standalone packages (those not intended to be used as a child package) and still use parameters. Parameter values may be supplied to a package directly at runtime by a SQL Server Agent job, through the command line (via dtexec.exe), or through T-SQL.

Also, you can also have project parameters as well as package parameters. Project parameters work in much the same way as package parameters, but the parameters apply to all packages in a project, not just a single package.

Conclusion

Of the numerous advantages of using catalog deployment model in SSIS 2012 and beyond, package parameters are near the top of the list. Parameters allow you to easily share values from parent to child packages, enabling more dynamic behavior and better code encapsulation.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – SSMS: Memory Usage By Memory Optimized Objects Report
Next Post
SQL SERVER – Microsoft SQL Server Migration Assistant V6.0 Released

Related Posts

7 Comments. Leave new

  • Cheryl McLaughlin
    February 19, 2015 1:55 am

    Great article, however I am using the Attunity Connector for Oracle and I need to extract an id from my query and use it as part of the file name. I am not sure how to go about this. Any help would be appreciated. For example, the file name starts as customer_002_timestamp.txt. If the id in the query is 123 then the file name should be customer_002_123_timstamp.txt.

    Reply
  • how do we pass value from child to parent in 2012

    Reply
  • Srinivasa Reddy Muly
    October 8, 2016 12:26 am

    Rahul, I have the same question. Let me know if you found the answer.

    Reply
  • Liviu Danis (@LiviuD2)
    March 8, 2017 7:57 pm

    Thank you so much.You just saved me. I was trying to pass a parameter to the child package in a loop scenario and kept on expecting it to be passed to a variable for some reason

    Reply
  • Rahul and Srinivasa – sharing what I’ve figured out – (1) add the names of the variables from the parent package to the ReadWriteVariables Expression Property of a Script Task in the child package (you have to go through the Expressions Tab, you can’t use the controls on the Script tab), (2) now reference these variables in that script task just as if they existed in the child package.

    Reply
  • If the parent and child are in the same project, will the child execute with the same Project Params as those specified for the parent when running SSIS via a SQL Agent Job and a selected Environment that is not the deployed values for the Project Params?

    Reply

Leave a Reply

Menu