- C# Scripts
- DWH INTERVIEW QUESTIONS
- MS Dynamics AX 2012 R2 Video Tutorial
- Project / Work Support
- SQL SERVER DBA INTERVIEW QUESTIONS
- SQL SERVER DBA Video Tutorial
- SQL Server / TSQL Tutorial
- SQL Server 2016
- SQL Server Scripts
- SSIS INTERVIEW QUESTIONS
- SSIS Video Tutorial
- SSRS INTERVIEW QUESTIONS
- SSRS Video Tutorial
- TSQL INTERVIEW QUESTIONS
- Team Foundation Server 2013 Video Tutorial
- Team Foundation Server 2015 Video Tutorial
- Windows 10
- Windows Server 2012 R2 Installation Videos
SSIS - How To Use OLE DB Command Transformation [ Delete Rows in Data Flow Task]
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.
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.
Create a table with data by using below Query
CREATE TABLE [dbo].[DestinationTable](
[CountryName] [varchar](50) NULL,
[SalePersonName] [varchar](50) NULL
insert into [dbo].[DestinationTable]
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;
Create SSIS Package. After create SSIS Package, Create Flat File Connection and use below data in text file
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=?
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
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.