Joshua Fennessy

See Exactly What’s Happenin’ with SSIS 2012 Data Taps

The Story

Experienced SSIS developers will recognize the usefulness of the simple data viewer object used during the development process to peek into the data set and see what is going on at various stages of execution.

One piece of functionality that has been missing since SQL 2005 is the ability to do this from a production SSIS server. Since the data viewer required the use of BIDS, and most organizations don’t allow development tools to be connected to production; troubleshooting data issues often involved lots of blind poking around and vague assumptions.

This problem is nullified in SQL 2012 with the introduction of the SSIS Data Tap. This is a run-time utility than can be used to peek into the data at various stages of execution – JUST LIKE THE DATA VIEWER!


Information Collection

Data tap files are output to the SSIS root install directory, and must be configured through SSMS. First some information about the package must be gathered in SSDT, so fire up your development environment and follow along.

1. Collect the PackagePath. To find this, start in the control flow and click on the Data Flow object that you want to tap. Copy the value of PackagePath
clip_image003

2. Collect the IdentificationString. To find this, enter the Data Flow object and select the connector that exists between the two transformations that you wish to tap. Copy the value of IdentificationString
clip_image004

With these two pieces of information, it’s off to SSMS for the rest of the procedure!


Execution

Connect to your SQL Server 2012 Instance that hosts the SQL 2012 Integration Services Catalog. Mine is SSISDB on my default instance. Once there, execute the following stored procedures in order.

catalog.create_execution
catalog.add_data_tap
catalog.start_execution

To make it REALLY easy, I’ve written a SQL Script using template parameters that YOU CAN HAVE! All you need to do is use <CTRL>+<SHIFT>+M to replace the defaults with the values that are required for your instance. Get a copy of the script here. Email me if you have trouble with the download and I’ll be happy to send it to you.clip_image006

After the package is executed, check your SSIS Install directory – mine is
C:\Program Files\Microsoft SQL Server\110\DTS\  – for a subfolder called DataDumps. You should see a file there with the name  specified in the catalog.add_data_tap stored procedure.

There is your output! Now you can see EXACTLY what SSIS was working with at any point of the execution, EVEN IN PRODUCTION!


Some Important Notes

1. Data taps are only valid for ONE execution, so unless you create a job step with all of this code, you’ll only get a data tap when you explicitly specify it.

2. Data taps create I/O (go figure). This means that performance WILL be affected when running a data tap. I wouldn’t recommend running them for every execution. Only use them when needed.

3. Data taps use the catalog schema, which means they require an SSIS 2012 Integration Services Catalog to be used.  Tapped packages also need to be deployed to this catalog.

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

%d bloggers like this: