UnPivot transformation in SSIS

UnPivot is a mechanism where we converting the column into row.

Read: Pivot transformation in SSIS

Let’s see the example.

Here we have a Pivot table in this table Qtrs are columns. We are converting this column to row as given in the above picture.

Let’s learn how to use UnPivot transformation in SSIS

Open SSDT.

Take Data Flow task.

  

Double click on the Data Flow Task.

In Data Flow pane taking Sources as OLEDB source

  

Creating the connection

   

In Pivot table we have store the data

  

Map the column.

  

Click ok.

Now I am taking UnPivot Transformation

  

Double click on the UnPivot transformation.

  

1. Pass Through

Select the name which you want to carrier. In this example I checked the CompanyID for farther we use these values.

2.

Select the column name which we want to convert Column to Row.

3a.

Name of the Column (Input)

3b.

Name of the Row value (corresponding)

4.

Values of this column name.

5.

Name of these Columns.

See the below. You will get the clear idea about it.

  

6.

Click ok button.

Now I am taking OLEDB destination to store the values.

  

Creating the connection

  

Mapping the columns

  

Click ok.

Package is completed.

  

Now it is ready to run.

  

Oh!!! Package executed successfully.

Let’s see the result in data base

  

Getting expected result.

Pivot Transformation in SSIS

Pivot is a mechanism where we are interchanging the row into the column.

Let’s see the example

   

Here we have a table which is having 3 columns. Now with the help of Pivot we convert row to columns.

Number of column= Unique value of the row

In the given table there are 4 unique values (Q1, Q2, Q3 and Q4). So in the Pivot table there are 4 columns.

Let’s learn how to use Pivot transformation in SSIS

Open the SSDT.

Take the data Flow task

Double click on DFT

Taking the Source transformation as OLEDB source

  

Creating the connection

  

Mapping the column

  

Click ok.

Now I am taking the Pivot transformation

   

Double click on the Pivot transformation

  

Set Key

This key identifies a group of input rows that will get pivoted into one output row. For example: CompanyID

Note: The Input data must be sorted on this column.

Pivot Key

Values in the input data from this column will become new column names in the output.

Pivot Value

Value from this column will be mapped in to the new pivot output column.

Generate pivot output column from value

Here we write the new column name.

After that click on the Generate columns Now Button. The new column value will be seen in Existing Pivoted output columns.

After that click on Ok.

Taking OLEDB destination

  

Creating the connection

  

Mapping the column values

   

Click Ok

Now it is ready to run.

  

Package executed successfully.

Let’s see the result. In database

Finally we get the desirer output.

Annotation

The SSIS Designer provides annotations, which you can use to make packages self-documenting and easier to understand and maintain. You can add annotations to the control flow, data flow, and event handler design surfaces of SSIS Designer. The annotations can contain any type of text, and they are useful for adding labels, comments, and other descriptive information to a package. Annotations are a design-time feature only. For example, they are not written to logs.

When you press ENTER, the text wraps to the next line. The annotation box automatically increases in size as you add additional lines of text. Package annotations are persisted as clear text in the CDATA section of the package file.

Lets learn how to use Annotation.

I am opening the package.

Right click on the control flow.

Click on the Add Annotation.

  

Similarly we can use Annotation on Data flow and Event Handler pane.

Drawback

If you use formatting then Annotation will be misplace.

Let’s see.

So After using Annotation please do not formatting.

Thanks.

RecordSet Destination in SSIS

The RecordSet destination does not save data to an external data source. Instead, the RecordSet destination saves data in memory in a RecordSet that is stored in an Integration Services package variable of the Object data type. After the RecordSet destination saves the data, you typically use a Foreach Loop container with the Foreach ADO enumerator to process one row of the RecordSet at a time. The Foreach ADO enumerator saves the value from each column of the current row into a separate package variable. Then, the tasks that you configure inside the Foreach Loop container read those values from the variables and perform some action with them.

Let’s learn how to use RecordSet destination

Here I am taking a simple scenario where I am Implementing RecordSet destination. We are getting CSV file from the client, that files are having the email id subject and body (message). We need to send the mails to respective stakeholder.

Below is the CSV contain

Open the SSDT.

Package design

I am taking the Data flow task.

In Data flow I am taking the Source as Flat file source.

And the selecting delimiter as comma (,)

Click OK.

Now for storing the result I am taking the RecordSet destination.

For storing the value I am creating one Object type variable.

Now we map the variable value.

And also go to the Input Columns tab and select the column.

  

Click ok l. all value of CSV file is now stored on the dataset.

  

Now I am going to control flow and taking the Foreach loop container to getting one by one email id and sending the mail to respective stakeholder.

Selecting Enumerator as ForEach ADO Enumerator and selecting ADO object source variable.

Now we need to map the variables.

Click ok.

For sending the mail, there are two way either we use send mail Task or use script task.

Here I am using script task.

  

In the script task I am writing the C# code to send the mail.

Here I am seeing the values.

Read the below blog to send the mail using the script task.

How to send the mail in SSIS using script task. Read here

http://bageshkumarbagi-msbi.blogspot.in/2014/12/script-task.html

Finally we learn how to use RecordSet destination.