vijayanandgawle

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

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

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.