OLE DB Command Transformation in SSIS

How To Use OLE DB Command Transformation [ Delete Rows in Data Flow Task]

Scenario:

We have a SQL table which contains our data. Every day we get a text file from our users
 and they want to delete records from SQL table those match with the text file records those they have provided.
 The records in text file are less than 50 all the time and
 we are not allowed to create any staging table for this new process.

Solution:

If we could load the data into a staging table and then write Delete statement by joining two tables
 that would be better solution(set based queries).
As we do not have option to create table and we have to handle everything in Data Flow task.
We will be using OLE DB Command Transformation to do the job for us.
 OLE DB Command Transformation will perform row by row operation but in our case it will be OK
as number of rows are always going to be less than 50. If you have a lot of deletes/updates, insert that
 data into some staging table and use set base queries in Execute SQL task to do the job.

Here is our solution for deleting few records on daily basis from SQL Table by matching records from text file.

Step 1:

Create a table with data by using below Query

CREATE TABLE [dbo].[DestinationTable](

[CountryName] [varchar](50) NULL,

[SalePersonName] [varchar](50) NULL

) 

GO

insert into [dbo].[DestinationTable]

Select * 

FROM (

SELECT N'uSA' AS [countryname], N'aamir shahzad' AS [salepersonname] UNION ALL

SELECT N'Italy' AS [countryname], N'andy' AS [salepersonname] UNION ALL

SELECT N'UsA' AS [countryname], N'Mike' AS [salepersonname] UNION ALL

SELECT N'brazil' AS [countryname], N'Sara' AS [salepersonname] UNION ALL

SELECT N'INdia' AS [countryname], N'Neha' AS [salepersonname] UNION ALL

SELECT N'Brazil' AS [countryname], N'Anna' AS [salepersonname] UNION ALL

SELECT N'Mexico' AS [countryname], N'Anthony' AS [salepersonname] ) t;

Step 2: 

Create SSIS Package. After create SSIS Package, Create Flat File Connection and use below data in text file

CountryName,SalePersonName,SaleAmount

USA,aamir shahzad

Italy,andy

Step 3:

Bring OLE DB Command Transformation to Data Flow pane and connect your Flat File Source to it. After that do configure as shown by blow snapshots.

Choose the OLE DB Connection which is point to Database which has Destination table

Write the query as shown below. 

Delete from dbo.DestinationTable where countryName=? 

AND SalePErsonName=?

Map the input columns to the parameters as shown below, Remember our query we have provided CountryName first in query so we have to map to param_0 and then SalePersonName to param_1

Final Output:

As we can see in the snapshot, before running a package we had 7 records. After running package two records were deleted and only 5 records left in destination table.

Merge Join Transformation in SSIS

Merge Join Transformation is one of the useful tool in SSIS to join two sources. The Merge Join transformation provides an output that is generated by joining two sorted datasets using a FULL, LEFT, or INNER join. Let us see how it works.
You can configure the Merge Join transformation in the following ways:
• Specify the join as FULL, LEFT, or INNER join.
• Specify the columns the join uses.
• Specify whether the transformation handles null values as equal to other nulls.
NOTE: If null values are not treated as equal values, the transformation handles null values like the SQL Server Database Engine does.

This transformation has two inputs and one output. It does not support an error output. Let’s discuss the same with an example. We will start out with a connection manager that is created for the Adventure works database

Now drag and drop a Data Flow Task from the toolbox. Now Double click on Data flow Task.

Now we will create an OLE DB Source called “Products”, in which we will select all of the products (see query below).

Double click on the Products OLE DB Source and the OLE DB Source Editor will appear. We select SQL Command from the Data Access Mode and will specify the query. This query will select the ProductID, Name, ProductNumber, and ProductSubcategoryID from the Product table.

Now Create an OLE DB Source called “Product Subcategory”, in which we will select all of the Product Categories .

Now Double click on the Product Subcategory OLE DB Source and the OLE DB Source Editor will appear. We select SQL Command from the Data Access Mode and will specify the query. This query will select the ProductSubcategoryID, and Name from the ProductCategory table.

Now Create an OLE DB Source called “Purchase Order Detail”, in which we will select all of the Details of the Purchase Orders.

Double click on the Purchase Order Detail OLE DB Source and the OLE DB Source Editor will appear. We select SQL Command from the Data Access Mode and will specify the query. This query will select the PurchaseOrderID, ProductID, and UnitPrice from the PurchaseOrderDetail table.

Now we will create two Sort components and join the pipeline from Products to one of the sort transformations and join the pipeline from Product Subcategory to the other sort transformation. Remember that both datasets that you are joining must be sorted the same before joining with a Merge Join.

Now Click on the Sort that we connected to the Products source and the Sort Transformation Editor will appear. We want to sort this dataset by ProductSubcategoryID, so click on the checkbox to the left of ProductSubcategoryID. This will put ProductSubcategoryID in the lower box. Now we can click OK when we are complete.

Click on the Sort that we connected to the Product Subcategory source and the Sort Transformation Editor will appear. We want to sort this dataset by ProductSubcategoryID, so click on the checkbox to the left of ProductSubcategoryID. This will put ProductSubcategoryID in the lower box. Now we can click OK when we are complete.

Now add a Merge Join transformation. This will allow us to join the Products and Product Subcategory sources together. Drag the pipeline arrow from the Products Sort to the Merge Join.

The Input Output Selection window will appear. Select Merge Join Left Input. This will mean that we are using the Products on the Left hand side of the join. If you are familiar with Left and Right joins in SQL this is a familiar concept. Choosing Left Input doesn’t mean we are necessarily doing an outer or inner join (we define that later), it just defines what side of the join this input will be used as.

Now drag the pipeline arrow from the Product Subcategory Sort to the Merge Join. This time it will most likely not prompt you for which side of the join you want to add this input, as we already selected Left Input for the previous input.

Now we will double click on the Merge Join and the Merge Join Transformation Editor will appear. You will notice that ProductSubcategoryID from both datasets has a line connected to each other. This means that this is the column that we are joining on. You will also notice that the Join Type can be “Inner Join”, “Left Outer Join” or “Full Outer Join”. In order to retain the other columns in the pipeline click on the checkboxes to the left of the column names. If any of the boxes to the left of the columns are not checked, it means that column will no longer be in the pipeline after the Merge Join. Notice that below there is a column called “Output Alias”. This column allows us to rename columns to new names. This allows us to rename “Name” from Products to “ProductName” and renam “Name” from ProductSubcategory to “CategoryName”. So after the Merge Join, this columns will now be known be these alias names. When completed click OK.

Now we will create two Sort components and join the pipeline from the Merge Join to one of the sorts and join the pipeline from the Purchase Order Detail source to the other sort.

Now Click on the Sort that we connected to the Merge Join output and the Sort Transformation Editor will appear. We want to sort this dataset by ProductID, so click on the checkbox to the left of ProductID. This will put ProductID in the lower box. Now we can click OK when we are complete.

Click on the Sort that we connected to the Purchase Order Detail source and the Sort Transformation Editor will appear. We want to sort this dataset by ProductID, so click on the checkbox to the left of ProductID. This will put ProductID in the lower box. Now we can click OK when we are complete.

Now we will add a Merge Join transformation. This will allow us to join the Results of the first Merge Join and the Purchase Order Detail source together. Drag the pipeline arrow from the sort transformation of the first Merge Join to the Merge Join. The Input Output Selection window will appear. Select Merge Join Left Input. Then drag the pipeline arrow from the Purchase Order Detail sort to the Merge Join.

Now we will double click on the Merge Join and the Merge Join Transformation Editor will appear. You will notice that ProductID from both datasets has a line connected to each other. This means that this is the column that we are joining on. You will also notice that the Join Type can be “Inner Join”, “Left Outer Join” or “Full Outer Join”. In order to retain the other columns in the pipeline click on the checkboxes to the left of the column names. If any of the boxes to the left of the columns are not checked, it means that column will no longer be in the pipeline after the Merge Join. Click OK hen completed.

Instead of using OLEDB as destination, I used Audit Transformation for testing purpose and used Data viewer to show output data produced by Merge join Transformation. After testing our package successfully we can delete Audit Transformation and Data Viewer and bring our destination and connect to Merge Join transformation for input columns for our destination.

Now Execute the package to see the Results.

Now we can output our joined dataset to any source we desire. I Hope you are now able to successfully join datasets using SSIS.

MERGE transformation in SSIS

In this post we are gonna discuss about MERGE transformation. MERGE in SSIS is equal to UNION ALL in SQL Server. This transformation unions two datasets/tables.The merge Transformation combines two sorted dataset into single dataset. Highlighted the text SORTED in last statement because “It is not possible to use MERGE when the inputs are NOT SORTED”. There is one more transformation which is very similar to this i.e UNION ALL. The Merge Transformation is similar to the union all transformations. Use the union all transformation instead of the merge transformation in the following situations.

• The transformation inputs are not sorted
• The combined output does not need to be sorted.
• The transformation has more than two inputs.

MERGE takes ONLY TWO inputs where as UNION ALL can take more than two. Now lets see how to configure MERGE transformation with an example.

I created TWO tables with names MergeA and MergeB and inserted few records into each table as shown below.

• Open a new project and drag a Data Flow task from toolbox in Control Flow.
• Edit the Data Flow task by double clicking the object or by selecting EDIT button on Right click on the object. Make sure the Data Flow Page is opened as shown below.

• Select TWO OLE DB data sources from data flow sources and drag and drop it in the data flow.
• Double click on the OLE DB data source to open a new window where we can set the properties of the connection.
• Select the connection manager and click on new button to set the connection string to the TWO tables as shown below.

• Set the connecteions to the TWO tables created using TWO OLE DB data sources.
• As said earlier, we need to SORT the data before giving as input to the MERGE transformation and hence drag and drop TWO SORT transformations into Data Flow pane and provide output of each OLE DB source to each Sort transformation as shown below.

• Open the SORT configuration and select the Checkbox as EmpNo which means the SORT will happen on EmoNo column a s shown below and apply the same in both the SORT transformations. Also provide sort type as either Ascending or Descending as per your requirement.

• The Input data from both the sources is SORTED now and hence add MERGE transformation to the pane and provide OUTPUT of both sort transformations as input to MERGE transformation as shown below.

• Now drag and drop Flat File Destination to see the output on file, make connection between the Merge and the Flat File Destination as shown above.
• Double click on the Flat File Destination to configure, In CONNECTION MANAGER select the NEW Option to set file path of the file and Click OK. If you select a file which already Exists then it will take that file else a NEW file will be created.

• Check the mappings between Available Input Columns and Available Destination Columns and click OK.
• Now the Package is ready which pulls the data from TWO tables then sorts and then mergers the data that is coming from two sources before copying to destination file. Trigger the packages and make sure all turns to GREEN.

• Now open the file and see the data copied into the file which is coming from TWO sources.

In the above example I have taken OLE DB sources and Flat file destination to explain MERGE transformation. You can use any SOURCE and DESTINATION types depending on your requirements. The key things to keep in mind while using MERGE transformation -

• Same number of columns should be there in both the sources.
• Same data types should be present for mapping columns.
• Data should be sorted before giving as input to MERGE.