Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Data tapping during SSIS package execution in SQL Server 2012


By:   |   Last Updated: 2013-01-15   |   Comments (6)   |   Related Tips: More > Integration Services Configuration Options

Problem

During SSIS package development we can add a data viewer to the data path of the data flow task to analyze the data passing through the data path or the pipeline, but once the package is deployed (or when not using Business Intelligence Development Studio) there was no built-in support for data tapping to analyze data for troubleshooting. I have heard we can do this now with SQL Server 2012, can you explain how it works?

Solution

Starting with SQL Server 2012, we can deploy our package to integration services catalog (to learn more about this click here) which is the recommended way now and then we can control almost every aspect of package maintenance and execution with stored procedures available in the catalog database. When you want to troubleshoot a failing package in production, sometimes you might need to analyze the data passing through data paths of the data flow components; in order to do so you can use [catalog].[add_data_tap] or [catalog].[add_data_tap_by_guid] stored procedures. To use it, you first need to create an execution instance of the package (using [catalog].[create_execution] stored procedure), then add a data tap by specifying PackagePath of the data flow task and IdentificationString of the data path inside that particular specified data flow task to tap the data (using [catalog].[add_data_tap] or [catalog].[add_data_tap_by_guid] stored procedure) and finally execute the package (using [catalog].[start_execution] stored procedure).

Getting started...

I have a very simple package, as shown below, deployed to integration services catalog with one execute SQL task to truncate the target table and then a data flow task to load data into the target table.

Starting with SQL Server 2012, we can deploy our package to integration services catalog

Now I want to add a data tap to the data flow task; in order to do so first I need to capture the value of the PackagePath property of the data flow task (in my case its "\Package\Load Person") for which I want to tap the data, as shown below:

add a data tap to the data flow task

Next I need to capture the value of the IdentificationString property of the data path (in my case its "Paths[OLE DB Source.OLE DB Source Output]") inside the selected data flow task for data tapping, as shown below: 

 capture the value of IdentificationString property of the data path

Once I am done collecting this information, I can now specify these values as parameters for the stored procedures discussed above:

DECLARE @execution_id bigint

--Create an execution instance in the Integration Services catalog --Data tap(s) are valid for the specified execution instance only EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Person.dtsx', -- Package name that is to be executed @[email protected]_id OUTPUT, @folder_name=N'AdventureWorks', -- Folder that contains the package that is to be executed @project_name=N'AdventureWorks', -- Project that contains the package that is to be executed @use32bitruntime=False, @reference_id=Null
--Add a data tap for specified execution instance --You need to specify the parameters to indicate specific in the data flow in the package where data tap will be added. EXEC [SSISDB].[catalog].[add_data_tap] @execution_id = @execution_id, @task_package_path = '\Package\Load Person', -- Package path for the data flow task you need to tap @dataflow_path_id_string = 'Paths[OLE DB Source.OLE DB Source Output]', -- Identification string for the data flow path you need to tap @data_filename = 'Person.csv', --Name of the file that stores the tapped data. @max_rows = 100000 -- Number of rows that are captured during the data tap. If not specified, all rows are captured
--Start an execution instance in the Integration Services catalog --for the created execution instance EXEC [SSISDB].[catalog].[start_execution] @execution_id GO

If you notice I have specified the name of data file as Person.csv. The data files will be created in <installation drive>:\Program Files\Microsoft SQL Server\110\DTS\DataDumps location. When I executed the above T-SQL statements it executed successfully and returned immediately letting the package run in the background (by the integration services catalog) and tapping data into the above location in the specified file. If you run the above T-SQL statements multiple times, you will see multiple data files getting created and subsequent files are getting appended with a number at the end of the file. If you notice in the image below, the data file sizes are different and that is because I ran the above T-SQL statement multiple times each time specifying a different maximum number of rows to tap.

When I executed the above T-SQL statements it executed successfully and returned immediately letting the package run in the background

When you execute the above T-SQL statements and get a success message, but you are not seeing the data files be patient it might take some time for the files to appear based on the complexity and location of the data path in the package that you are tapping. Also you will notice increasing sizes of the data files, this happens as data is written to the data files and also based on the maximum number of rows you want to tap.

Notes:

  • You can use [catalog].[add_data_tap_by_guid] instead of [catalog].[add_data_tap] and provide GUID of the data flow task of the data flow component.
  • Adding a data tap is applicable for the execution instance only in which it has been added and does not impact other executions.
  • As you might have guessed already, adding a data tap impacts the performance of the package execution and hence you should only add it when you are doing troubleshooting.
  • Try to limit the number of rows to be tapped, if the number of rows is large it might impact performance badly and consume lots of storage to store the large data files.
  • Data files generated are accessible to the administrator (who started the execution that contains the package with the data tap) of the machine on which the stored procedure is run.
  • You can use the above discussed T-SQL commands to tap the data without changing your package.
  • As said before, it does have performance impact and generates lots of IO for storing data in data files, so be very careful when using it in production and do it only when there is an absolute need for troubleshooting.
Next Steps


Last Updated: 2013-01-15


get scripts

next tip button



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, May 22, 2015 - 1:46:48 AM - Murtaza Bohra Back To Top

i cant find DTS folder

under :\Program Files\Microsoft SQL Server\110

can you help?


Wednesday, March 20, 2013 - 8:56:15 AM - Andrew Back To Top

I've got the same problem as Kate.
- The package runs, execute wihtout an error.
- the data tap is present (SELECT * FROM CATALOG.execution_data_taps)
but the file doesnot appear !


Tuesday, February 26, 2013 - 5:51:12 PM - Kate Back To Top

When I tried the DataTap on a something similar, it never created the file anywhere on my local machine. Have you come across this before or know what happened?


Monday, February 25, 2013 - 1:31:08 AM - Aditi Back To Top

Hi Sahid,

    I've followed the above steps to achieve the same but when I try to execute that set of T-SQL statements it is throwing the below error:

Msg 229, Level 14, State 5, Procedure create_execution, Line 127

The SELECT permission was denied on the object 'catalog_properties', database 'SSISDB', schema 'catalog'.

Msg 27156, Level 16, State 1, Procedure create_execution, Line 131

The Integration Services server property, 'ENCRYPTION_ALGORITHM', cannot be found. Check the name of the property and try again.

Msg 27146, Level 16, State 1, Procedure create_execution, Line 178

Cannot access the package or the package does not exist. Verify that the package exists and that the user has permissions to it. 

 

Please let me know if I am missing anything. 


Thursday, January 17, 2013 - 10:40:54 AM - Arshad Back To Top

Hi Shahid,

You can refer to these tips for SSIS deployment:

http://www.mssqltips.com/sqlservertutorial/213/deploying-ssis-packages/

http://www.mssqltips.com/sqlservertutorial/214/sql-server-integration-services-ssis-deployment-utility/

http://www.mssqltips.com/sqlservertutorial/216/deploying-ssis-packages-with-sql-server-management-studio/

http://www.mssqltips.com/sqlservertutorial/215/command-line-deployment-tool-for-ssis-packages/


Tuesday, January 15, 2013 - 11:48:05 PM - Shahid Iqbal Back To Top

Hi Sir, I want to deploy may SSIS package on SQL server 2008 R2, but its not working....

Can u help me by sending complete step by step procedure of deploying SSIS Package on SQL Server 2008 R2, having 64Bit Windows7.


Learn more about SQL Server tools