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.







9 comments:

  1. Thank you so much for this beautiful explantion for OLE DB Command using SSIS.

    ReplyDelete
  2. Grammar, vocabulary, tenses, indirect speech, passive sentences must always be keep in mind while writing a blog. Everyone must read this blog. This is going to help everyone. Dallas vps hosting Nyc

    ReplyDelete
  3. nice http://www.techbrothersit.com/2013/08/ssis-how-to-use-ole-db-command.html

    ReplyDelete
  4. Fatality servers are probably the machines web company. A consequence of large tension having to do with members over the gameplay cpu mmorpgs does get swamped and forestall earning a living. Which means game enthusiasts lose his or her focus and additionally ruin his / her wide thrills. Now fatality servers usually are there we are able to safeguarded and even lag-free casino practical experience at best rates. You go to the website https://www.nettingchat.com/blogs/post/92983 in order to get accomplish data on the subject of server hosting.

    ReplyDelete
  5. Your blog is in a convincing manner, thanks for sharing such an information with lots of your effort and time
    sql server dba online training
    SQL dba online course

    ReplyDelete
  6. Your website share lots of great senarios for job, Thank you

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete