In this video, we will discuss about something called as data types. If you remember, in our first video, we discussed something about debugging. In other words, you know, when I'm actually in my developer environment or when I am on my SSIS package, if I want to debug if I want to know that what kind of data is passing from one component to another component, we had used something called as the data viewers. So basically, we can go and enable Data Viewer in any one of these paths. And then when we run this project, you know, we get a pop up like this, you know, where we can go and see that what kind of data is passing from one component to the other component. But the data viewers we can only use in development advice.

What if our SSIS package is running on production? And on production, I would like to know that you know, what kind of data is passing between the components. And that's where exactly we have these things called as data types. So data type is a feature, which you can use in production, or when a package is running. So you can go and add a data tab to a path. When I say path means this thing, this arrow over here is a path, which actually connects one component and the other component.

So during runtime, you know, if you want to know or during production, if you want to know that what kind of data is going through a path, you can go and add a data type to it. And you can extract that data into a CSV file and see it. So data tab is a feature which will help you to debug in production. So you can see that we have a very simple application here. And what this application does is it actually goes and leads record from a text file. And now when he reads the records, you know, it has lots of columns over here.

He takes a customer name and changes that to uppercase. So first he reads the file, he changes the customer name to uppercase, then does some kind of row count, and then does a sword. So we have a very simple application, which has three paths over here. So so we have four components and three paths. And let us say that when this application gets deployed into production, how do we go and do debugging? Now a lot of people also termed this debugging in production as instrumentation.

So in the rest part of the video, I'll be using the word instrumentation when I say debugging in production, and when I say debugging inside Visual Studio or in development environment, I will be using the word debugging. So when I say debugging means debugging inside Visual Studio and when I say debugging in production, then we will use the word instrumentation. So now let us say that this application is deployed into SQL Server. And we would like to go and do instrumentation on it. So first thing is let us go ahead and install this project into SQL Server as deployed. So if you remember, we had two ways of doing deployment, right?

So, one was the package model, and one was the project model. Okay. So I'm going to go and deploy using the project model. So let me go and run this integration services package, in case you are not aware of how to go and do deployment, then please go and see the video. You know, where we have explained deployment into integration services by both project deployment as well as package deployment. So in this This video I will not be discussing about how to go and deploy in SQL Server.

So let me just say next. And I will say yes, I want to do project deployment next. And I want to deploy on this SQL Server. And I will deploy inside the SQL Server I have created a folder called as some SSIS program. So I'm going to go and deploy in that folder. And let's say deploy.

So deploying project and once the deployment is successful, then inside SQL Server, I should see my SSIS package. So when I go here, inside this some SSIS program, you can see my data types project, and inside data task project, you can see the Data tab package. So now, if you'd like to go and apply data tab on this package, We'd like to go and apply data type on a particular path during runtime, you know, from the Management Studio. So basically, you'd like to go and apply data path data tap on this path must be or would like to apply data data tap on this path. So we'd like to go and apply data tap on the particular path on which we want to do instrumentation. Now, each one of these paths inside the package is identified by a identification string.

Because if you say that you want to go and apply data tap right, you need to somehow identify that path. So every path what you see over here is identified by identification string. So for example, this path, which actually moves data from the capital component to the row count is named as parts capital derived column output. In the same way, this path over here What do you see is read customer dot flat file, so every part Here is identified by a connection string. And these paths actually are inside a package. And how do we go and browse to the package, we go and we browse to the package from the package slash data flow task.

So basically, in order to reach this path are to specify this path inside data tab, we have to say package name, slash, the data flow task name and then the identification string. So now that we know how to get the particular path using the identification string and the data flow path, let us go and start adding the data tabs. Now, in order to add data tab to a path, you need to follow three steps or you need to execute three steps. First thing is, you need to first get the execution context for this data package. Once you get the execution context, you will then go and add the package. By using the slash package name slash data flow name, and then the identification string.

And afterwards, you will go and you will execute the package. Once you execute the package executed the package, in whichever path you have added the data flow, that data will be saved into a CSV file, which you can then have look. So step one, you need to get the context. Step two, you need to go and add the data taps. And step three, you will actually go and execute that context, right. So for that, we need to go and write a query.

Now definitely one thing is that in order to write all these three steps in a query would be a very, very big thing, right? So what you can do is you can right click on this you can say execute. And from here you can get some ready made script you know, which can make your life easy. So what you can do over here you can go and say script This execute into a new query query window. So, you can see over here Now, let me go and indent this. So, so, you can see this first step here is nothing but getting the execution context right.

Once you get the execution context, the next step is to add data taps, which we have to write manually it is not here. And the last one here is executing the package by using the context. When I say context means this execution ID, okay. So it's a three step process. First one, get the context this code we have already got it. Second, add data tabs, this is what we need to write.

And the last one is executing the context and then seeing the output of that path into the file. So, you can see all these methods, which are creating the context, which are executing the context is all part of your SSIS dB. So if you remember, this SSIS DB is nothing but it is a database, you know, in which you go and you deploy your package, right. So if you want to create the context, then inside this SSIS DB there is something called as create underscore execution. In the same way, if you want to go and start execution, you have to start execution. So in the same way, if you want to go and add a data tab, you need to go and say, catalogue dot add underscore data tap.

Right. And if you remember I said when you add a data tab, you need to specify the package name slash data flow tasks. Name slash the identification string, right. So, if you see for now, our package name is package path, I will say is package slash data flow. So I'm going to go and copy this package path. And I will put it here.

Let us also pick identification string. So I'm going to go and take this identification string. So in other words, I want to now here to the data what is passed from the capital component to the row count component. So I'm going to go and take this pop and paste it here. And once you execute this, from this part, when the data comes out, it will actually get emitted into this source data tab dot CSV. Right.

Great. So now we are ready with the full script. And this complete script, what I will do as I Go and copy it inside the project. So, in case you want to have a look at it you when you when you're seeing this video inside. Inside this I'm gonna go and add a text file. And I'll come by so what I will do is I need to just add a text file, right miscellaneous.

Okay, so what I'll do is this compete script I will go and I will save it in this solution over the years so that you can have a look. Okay? So I'm going to go and save this thing over here. So I'll name this a script is a script. So what you can do is In case you want to have a close look at the script inside the same solution inside the same project I have added this text file here called a script okay. So, in the miscellaneous you can go and have a look at the script okay.

But and this complete source code is available you know as a download So, so in whichever place you are seeing the video at the top you have this source code right. So, now that we are done with things let us go and execute this So, I'm going to go and execute the script here. So, we can see now everything is muted successfully right. So, let us go now and see that is the data moved to this source data type dot CSV. Now, this file gets created in wherever your SQL Server is installed. So, for example, at this moment my SQL Server is installed in progress files.

So I need to go to Program Files, I need to go to Microsoft SQL Server 110, DTS and data dumps. So, in this you can see now he has created a file source data tab. So, if I go and open this with notepad I should see all the values over here. And you can see that because this path has capitals, you know, everything has been changed here to capital. So, now, you know, in production, you know, wherever you wish or you know, in whichever path you want to go on here the data, you just give the package name slash data flow task name, slash the identification string and add a tap and start listening to the data. So, now, a couple of points here very important.

First thing is that the only way to add data Is wire the Management Studio. So, you know, you cannot go and add a data tab permanently. So you cannot just go and say okay, add a data tab. And whenever I right click or I say execute, it will generate the file No, you need to go and write such kind of SQL into three steps. And you need to execute. So when you do an execute by using this create execution and start execution, at that time only you can use data tab.

So it is wonderful, this context. So if I rerun again, that will be a different context. So this is the one the way to go and do instrumentation in SSIS. So I hope that you have enjoyed this video. In this video, we were trying to understand what exactly our data tab and how to do instrumentation using data tabs in production environment. Thank you so much.