— 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

_
_

1. 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
   
   _

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 (MSDN)
catalog.add_data_tap (MSDN)
catalog.start_execution (MSDN)

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 ++M to replace the defaults with the values that are required for your instance. Get a copy of the script here. If you have trouble with the download email me and I’ll be happy to send it to you.

**— Data Review —
**

After the package is executed, check your SSIS Install directory – mine is
C:Program FilesMicrosoft SQL Server110DTS – 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 —
**

• 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.
• 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.
• 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.