Description:-
In this article we will show
you, How to Update Data using OLEDB Command Transformation in SSIS with
example. How to insert data using OLE DB Command Transformation and Delete Data
using OLEDB Command Transformation in SSIS article to
understand the Delete operations.
Here we will use the flat file
for the source records, which records we want to delete from the database
table. Using the OLE DB Command we will perform the delete operation.
So let’s start step by step to
perform the delete operation in the table.
STEP 1: Drag
and drop the data flow task from the toolbox to control flow and rename it as OLE
DB Command Transformation DFT.
STEP 2: Double click the data
flow task and drag and drop the Flat file source from the toolbox. After configure the source
file with the flat file source. After check the columns from the columns tab.
STEP 3: Drag and drop the OLE
DB Command from the toolbox and create connection with the destination table or
else you can create connection manage for which table you want to perform any
action.
Now move to Connection Properties tab and insert the SQL
Command in the SQL Command property like below image.
delete from [DestinationTable] where countryName=? and
SalePersonName=?
Now move to Column Mapping tab
and map the input columns to output columns like below. Before mapping I would
like to suggest you confirm the parameter which you have passed in the SQL
Command and map with the input columns.
Before run the package look the destination table data.
Now in the flat file we have below records which we want
to delete form the destination table.
Now run the package and see the output file.
Let’s check the destination table those records has been
deleted from the table.
Thanks for comments.....