SSIS Data Tap and Child Packages

Last week I covered how to add a Data Tap to the execution of a 2012 or 2014 SSIS package. What I showed you will work fine for many packages, but how do you add a Data Tap to a child package? I spent quite a bit of time trying to figure out how to do this, and the only thing I found was this sentence from Books Online:

If an execution contains a parent package and one or more child packages, you need to add a data tap for each package that you want to tap data for.

I was this close to emailing a friend on the SSIS team at Microsoft to ask about it. After some experimentation, I figured out that the add_data_tap_by_guid procedure will work to create a Data Tap in a child package. To test this, I created a project with a Master package and a Child package. The Master package just executes the Child package (Figure 1). The Child package exports a table from AdventureWorks2012 to a file.

dtcm1

Figure 1: The Master package execute the Child package

After deploying the project, the code to run the Master package can be generated by right-clicking on the Master package in SSMS and selecting Execute. Instead of running the package, click Script to generate a script. Take a look at last week’s article if you need to learn more about this.

The code to add the Data Tap to the data flow in my Child package looks like this:

EXEC [SSISDB].[catalog].add_data_tap_by_guid @execution_id,
@dataflow_task_guid = ‘{0210C9CC-635B-4038-AFC4-54220909F4F3}’,
@dataflow_path_id_string = ‘Paths[OLE_SRC_Currency.OLE DB Source Output]’,
@data_filename = ‘output.txt’

The @dataflow_task_guid is the ID property of the Data Flow task in the child package (Figure 2), and the @dataflow_path_id_string is the IdentificationString property of the data flow connector or arrow in the Data Flow task layer (Figure 3).

dtcm2

Figure 2: The ID property

dtcm3

Figure 3: The IdentificationString property

Adding a Data Tap to a child package is pretty simple!

About Kathi Kellenberger

I am a SQL Server professional working for Linchpin People. I love talking about SQL Server to anyone who will listen, just ask my two year old granddaughter. I love to write and teach. I am so humbled and honored to say that I made SQL Server MVP in 2013.
This entry was posted in SSIS. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s