Hello everyone, this is Mukesh Marla, and in this video, we will look into p word and uncovered transformation in SSIS. In order to understand these two transformations, let's do a small demo. In our demo, we have a database called SSIS demo with a table called p word source. In this pivot source table, we have three columns month, day of week, and sales amount. Now our target is load these values into another table called pivot destination. But the strange thing here is the structure of the pivot destination table.

As you can see, we don't have day of week and sales amount column here, instead of that, we have individual column for Sunday, Monday and Tuesday. So in simple word, what we want to do is we want to load the rows in such a way that it become columns, and this concept is called as pivoting and in SSIS, this can be accomplished easily with the help of the word transformation. Now we have one readymade project created for us with one ready made package. Now in this package, we'll start with Connection Manager. So let's right click and say new oledb Connection Manager. And we will say delete new.

And let me put the server name AZ dot. Let me select the database that is nothing but our SSIS demo. Test Connection. It's done. Okay. And okay, now we have our Connection Manager ready with us.

Next task is our source. So let's get into data flow task. Now in order to configure this, double click this and here, we'll start with source and we will take something called as where it is. It should be and will be source where it is. It might be in other sources. So let's take oledb source Yeah, here it is.

Double click it yes correct the name of the table is going to be pivot source columns yes I want all the columns okay done. Next select pivot transformation, from other transform section and connect source to transformation double click pivot transformation. And set word key as day of week, Set Key as month and pivot value as sales amount. Per key is nothing but it's a column which will contain values which will become new columns in the output. For example, in our source table do we contain distinct values as Sunday Monday and Tuesday. Now these three values will become column names in the destination table.

So pivot key will be day of week. Set Key column is a column in source data source, which contain group of input rows that will get preferred into one output row. For example, in our case, pivot source table contains three values three rows of which belongs to Jan records, three rows which belong to five records and three rows which belongs to march record. And in final output we want only one Jan row, only one fabro and only one mushroom. And hence, we say month is going to be a set key here. Now pivot value is a column which contain value, which is going to be mapped into the new pivot output column, which is in our case is sales Some say okay, but unfortunately, it will end up with another error that the sales amount is not mapped to any output column.

Now the reason for this error is there is no output column defined in pivot transformation at let's come from that, right click the transformation say Show Advanced editor. Go to input output properties, just expand this pivot default output, you will see that in output columns, there is only one column that is month. But we were expecting more, right. So in order to do that, simply say OK, and double click it once again and explicitly specify what columns you want. We want columns as Sunday, Monday. And finally I want to his day and simply click on this generate columns.

Now, it will create three more columns. As you can see here, three columns are created. say okay, now error is gone. Now it's time for destination. So first, let's go to our SQL database and let's remove all the existing records, which will make it easy to understand. So it's done next From exercise toolbox, let's add will be destination.

And let's connect the word transformation to destination, double click it and simply select your destination. Now go to mapping. And now we have to do the mapping explicitly because column names are not same. So let's do that Sunday, Monday, and Tuesday say OK, and done. Now, let's confirm again, our destination table is completely empty. Let's execute it.

Execution done. Let's come here, refresh and as you can see, the values are here. So this is the pivot transformation works. Now there are some challenges using pivot transformation. One of the challenges the column, which we use for Set Key must be sorted. If you go to Visual Studio, double click the word transformation you will see that Set Key says The input data must be sorted on this column.

Now, you may be wondering, but right now values in month column are not sorted. But still it's working. Now actually, pure transformation don't need values to be values in sorted manner. It asked for that because it won't values to be in group. For example, it won't that all Jan values should come one after another. all fake values should come one after the other, and all March values should come one after the other.

That is the requirement. Now let's say there is a scenario where values are not in this world. For example, let's say we have a march value here, let's say March. With Tuesday, and some let's say 20. And here I will say it is going to be Jan, and I was at Sunday and I will say 10 and let them make it 25 let's say values are in this order. And now let's go and execute the packet once again.

Now you will see that package execution will fail. Let's go to progress tab, and you will see that there is an error. Let's simply copy it. And let's open the notepad. And let's try to read it. It says, OK, we have to read this another error, not this one.

In SSIS, it's very important to know which error to catch. You can see that it says the value violated the integrate integrity constraints for the column. And constraint says the values must be sorted. So in order to solve this problem, what we can do is simply go to your data flow tab, and just put one more transformation called saw transformation. Before we were transformation. Let me remove this one.

And let me connect source to source. And then let's double click this and let's configure it I will say I want to sort based on month and I will say, Okay, and then I will say, Can I get here, they will fluctuate. We have a month here, say Okay, now let's execute it. And you can see that now it's working fine. Let's go Come here. And let's check the destination once again.

And you can see that the same records are coming Jan 1020 2030, Jan 10 2030, February 15 2535, February 15 25, and finally march with value as 515 and 25. The same values, these values are here because of the first execution and these values are here because of second execution. So this was the first challenge and we tackled it. Before we go and talk about second challenge First, let's empty this table. So for that I will write a query DELETE FROM pivot destination Remove everything if I done now the second challenge is, what if the values in this table or in this source table are repeating. For example, let's say we have one more Jan record here, Jan. And I will set Sunday, but this time, I will say 20.

Now we have two records for Jan and Sunday. So what happened in this case? Let's see. Let's go to exercise again. And let's execute the package once again. And you will see that it failed login.

Let's go to progress tab. Let's copy the error. And it says duplicate pivot key value Sunday. Whatever column you specify as pivot key, that is day of week. It cannot contain duplicate values. In our case, we have duplicate values.

So let's understand how to tackle this problem. Now before we get into solution, first let's talk about what we want. So now let's say execute so that we get the final response. So right now if your destination is empty. Now after execution, we want, Jan, and Sunday, but the value will be the total of both of these values. That means 30.

That's what our expectation is. So in order to achieve this, we will use one more transformation called aggregation transformation. So let's take aggregation transformation from SSIS Toolbox. And let's connect the source to aggregate aggregate transformation instead of directly connecting connecting it to pivot. So let me say delete, connect sought to aggregate double click it and I will say group by month and day of week and sum all the sales amount. I'll say okay, then connect aggregate to pivot and everything will remain Has it.

Now let's execute it. And you will see, execution completes, there is an error here. Let's go and look into this error first. For that, let's go to progress tab. Let's copy this error. Let's wait state in Notepad and as you can see, error is again the same, the value violated the integrity constraints that is private key must be sorted.

So to solve this problem, stop the execution go to data flow. Simply remove this connection and then with this connection as well and finally remove this connection as well. Now connect your souls to aggregate double click it yes we want record to be grouped based on month and day of week. Okay. Connect aggregate to SAR. Double click it.

Yes we want record to be sorted based on month And finally connects or to pivot. Now execute the application or package execution completes, go to SQL Server, simply refresh it. And you can see the records are here. It's that simple. These are the two challenges, which we normally face when working with pure transformation. hope you understood both of them.

Now before we get into uncover transformation, first, let's reset couple of things. Let's go to pivot source and let's remove this extra Janssen D. Ro. Done. And let's remove this rows as well. Now it's empty. Let's re execute the package.

And now everything is ready. Now. Now let's get into our pivot transformation. Now unpeeled is simply a reverse of pivot. In pivot, we load data And we convert rows into columns. And in unfavored, we load data, and we convert columns into rows.

For example, let's say we have data, something like this. And we want to load these data to a table whose structure look like this. That means now we have column data, which we want to put it inside a row, put it in, put it in the rows. So in that case, we will use something called as annuity. So in order to complete this demo, we have one more table with us called uncovered destination, which has three columns month, day of week and sales amount, exactly like the word source. So for uncovered demo, we'll use pivot destination as source and uncovered destination as destination.

So let's get back to Visual Studio. And here, let's stop the execution first. And let's get back to control flow and right click and say disable. And let's add one more Dataflow tasks, and this is going to be for unpure. So simply double click it again, first we need something called as oledb source so it will be here, double click it and this time our source is going to be a pivot destination. Yes, I want all the columns, I'll say okay done.

Now, we need something called as uncovered transformation. So that we will get in other transformation. And here it is, simply connect source to uncover transformation. simply double click on the word transformation and select those columns which we want to be on pivoted Sunday, Monday and Tuesday. Now specify whatever destination column name you want. I will keep it sales amount but if you want, you can make it as sales amount one also.

But the one important thing is we have to specify same for all three columns. Now pivot key value column name again that can be anything I will make it as they have week one and I will say okay that error is gone. Next thing is we need destination. So go to SSIS Toolbox simply from other destination, take oledb destination and connect and we were to destination double click it your connection is correct, we want to do it, load it to table so the table name is going to be unfavored destination, go to mapping and I will connect day of week one to day of week and I will connect say someone one to sales amount say okay, but unfortunately there is one more Error Error says column day of week one and day of week cannot convert between Unicode and non Unicode string data type. First, let's go to SQL Server.

And let's check the data type of the of that is where care which is non Unicode. And unfortunately, when unfavored transformation gender A new output column called day of week one, the data type of that column is going to be n where care which is a Unicode and it is not possible to put n where care value in America data type directly. So, for that, we will use something called as data conversion transformation. So, from SSIS Toolbox, simply take data conversion transformation, and simply remove this connection and, and oh sorry, and simply connect it to this one, and then connect, double click it and takes day of week one and create one more column called as do copy of day of week one. Let's keep it as it is. If you want you can change the name as well.

I will say where care cup day of week one, I will say okay, now I'll connect this to this. Double click it, go to mapping and instead of certifying day of week one here, specify varchar day of week one, say okay. And there is a warning symbol here which says truncation may occur because n where care is bigger than where care. So it may possible that when you try to put something which is coming from and where care to where care, you may lose something, but in our case it that it's absolutely fine. So simply let's go and execute it. But before that, let's go to our SQL Server.

And let's open this under your destination. And let's make sure that it's empty right now you can see that it's completely empty. Let's execute it. And now it's done. Let's go to SQL Server again. Let's refresh it and you can see that nine rows are here now.

Okay, let's go to our pivot source. You can see there are nine rows which get copied here now. Okay, so with the help of pivot source, so it sorry with the help of your transformation, we loaded this data into this table and with the help of undo or transform We loaded this data into this table. hope you understood both pivot and unpure transformation properly. If you have any questions, please feel free to write to correspond at direct questpond.com. Thank you very much