In this article we will explain Debugging in deployment – Instrumentation using Data Taps.
Links to other Labs of Learn MSBI Step by Step.
Learn MSBI Part 1:- Creating SQL tables, Practicals of SSIS-ETL(Extraction, Transformation & Loading)
Learn MSBI Part 2:- Performing Validation on Records
Learn MSBI Part 3:- SCD (Slow Changing Data)
Learn MSBI Part 4:- Fact and Dimension.
Learn MSBI Part 5:- CUBE Creation
Learn MSBI Part 6:- Displaying reports using SSRS
Learn MSBI Part 7:- DQS (Data Quality Services)
Learn MSBI Part 8:- Data Profiling Task
Learn MSBI Part 9:- Pivot, UnPivot and Aggregation
Learn MSBI Part 10:- Execute SQL Task component (SSIS)
Learn MSBI Part 11:- Send Mail Task & Script Task Component
Learn MSBI Part 12:- CDC Concept using SSIS
Learn MSBI Part 13:- Asynchronous, Synchronous, Full-blocking, Semi-blocking & No-blocking
Learn MSBI Part 14:- Understanding Page Split and maintaining SSIS Performance
Learn MSBI Part 16:- Using Multi-threading and its performance in SSIS program
Data taps is a feature which helps to extract data from a path in production or runtime environment in order to do debugging. The line which are connecting two components are termed as path. So in order to know what data that path carries during production Data taps is required and this is also called as Instrumentation.
Usually if extraction of data from path while debugging within Visual Studio or development environment is done by SSIS built in feature of SSIS called Data viewer and it is simply termed as Debugging. Visit following link to read more on Data viewer and how to use Data viewer.
Debugging of the package is done within the visual studio IDE.
Instrumentation of the package is done under deployment or during production to know the flow and data moving in the package.
As this lab is going to focus on deployment rather than working on package within visual studio. For that there is need to deploy the package.
Deploy the project into SQL Server
In order to do the deployment of package do follow below steps. As this lab is going to demonstrate project deployment.
Step 1: Go to the project location and for that do a right click on the Solution and click on “Open Folder in File Explorer”
Step 2: Once the project is located on the file browser then do a double click on the folder –> bin –> Development package.
Now double click on the file present in Development folder which will open deployment wizard screen and just do Next of the Introduction screen.
Next screen is where select deployment type, select radio button to “Project deployment file” and the source the path (keep it selected to default source path) as shown in the following image and then click on Next.
Following screen would for selecting the destination, as deployment is selected as Project Deployment in SQL Server, select the server name and give the folder path where it has to be deployment in SQL Server and then click on Next.
Following is the last screen to do a review of all the selections which has been done in the wizard. After finding all selection correct then click on “Deploy” button available at the bottom.
Once the deployment is successfully completed referring below image it will show Action with green sign marks and Result as Passed, which means Deployment is successfully done.
Now go to SQL Server Management Studio and check the location under Object Explorer where it has created the deployment as shown in the image down below.
Next is to go and apply the Data tap on particular path of the deployed package as seen under Object Explorer.
Applying Data tap can be to any path of the package as shown in the below image in order to do the Instrumentation. Each path is identified with unique identification string during instrumentation.
in order to view identification string just do a right click on the path and click on Edit whose Identification String can be seen. So for every Data path identification string is going to be different and for that just select the Data Path and open its properties to view it.
As data path lies within the package so knowing package path is also necessary for that do a right click on the package white background and then click on Properties and under that “PackagePath” can be found whose current field value is “\Package\Data Flow Task”.
This is the path which has to be followed to reach Data path on the package
“\Package\Data Flow Task” with “Identification String”
Adding Data taps to path of the package
Next comes Adding Data taps to path of the package is three steps process: –
Step 1: Get the “Execution Context” for the package.
Step 2: Then add the path name by “\Package\Data Flow Task” and then the “Identification String”.
Step 3: Finally Execute that context. Once the package is executed on whichever path the Data tap is added that data will be saved into the CSV file
In order to implement above steps there is a need to write SQL query and these queries writing is itself a big task and you should be expert in writing the query.
Do not panic if you do not know to write the SQL queries it’s OK. SSIS has given provision to make task simpler by using readymade script.
For that go to SQL Server Management Studio, do right click on “Execute” which will open Execute Package editor window. Here click on Script à New Query Editor Window and then click OK.
It will then show screen to open Overview Report. Click on ‘No’ to proceed ahead.
And then a readymade written with SQL queries will appear which has three steps: –
1) Create Execution Context: It includes written query for creating the execution.
2) Adding Data taps: This step has to be written manually where PackagePath: “\Package\Data Flow Task” and Identification String: “Paths[Capital.Derived Column Output]” has to be mentioned. In order to get “PackagePath” go to on SSIS package and under Data Flow pane do right click and click on Properties. In the Properties screen “PackagePath” can be found and for Data path select the connecting arrow line between two components which has “Identification String”
Next is to specify the location of the CSV file, where data captured from the data path gets stored on the CSV file. Default location to where file saved is “C:\Program Files\Microsoft SQL Server\110\DTS\DataDumps”. After execution of the context a CSV file generated with data can be seen here.
3) Execute the package using execution id: After adding data path next step is to start the execution of the package in order to capture the data of data path which will be saved in the CSV file.
Click on “Execute” button available on the top under “SQL Server Management Studio” and after the package is executed results will be displayed at the bottom as shown in the image down below.
Once found results are displayed next step is to go and check at the location “C:\Program Files\Microsoft SQL Server\110\DTS\DataDumps” under SQL Server whether CSV file is successfully generated. After going on that location a file named “SourceDataTap” is being displayed. Now open to file to check its content data.
For that go and open the CSV file in notepad by doing a right click and open it with Notepad option. Once the file is open data of the data path is captured as shown in the below image.
As data is captured from second data path which is coming from the output of CAPITAL named component so it has done the capitalization of SalesPesonName.
Please Note: By using SQL Server Management Studio only, data taps can be implemented. Also data taps for each context is different. Every time whenever required SQL three steps queries has to written which includes create execution, adding data taps and start execution.
Instrumentation is the way to go under production on which ever data path wished to view data can be achieved by applying above mentioned steps with data taps.
See the our below video Learn MSBI Step by Step in 4 days :-