Run a single DELETE FROM SQL command based on a RecordSet of multiple rows

Question

I have a CSV file with a single column, id, and 3 rows: 10, 20, 30. What I want to do is simply delete the rows from a table in a database where the table's id col contains any of the three values. How do I do this with one DELETE FROM command being sent, not 3?

I can read the file in SSIS within a Data Flow, so far so good. I can store the result in a RecordSet variable/parameter and pass it along to other tasks, I got that far. I know I can't use an OLE DB Command step in the Data Flow because that will execute one SQL statement per row - not a real problem with 3 rows but obviously reality isn't so trivial - so an Execute SQL task is probably going to be involved at some point. Question is, am I really going to have to somehow manually create the string '10', '20', '30' using a C# script, manual FOR loop, or some other contorted concoction, just to run a DELETE FROM?! Is there some simpler solution I'm missing?

For context, this is part of a larger task where I'm trying to create an SSIS package that reads CSV files into tables and deletes from the target tables the ids that are being uploaded (i.e. it overwrites what's already there if needed). I am aware that I can solve this problem in general by creating a job on the server which first truncates a staging table, then runs an SSIS package that simply loads the CSV blindly into the staging table, then a 3rd step which deletes from the target table and selects the staging table into the target - it's not a solution I like but I can accept that it's the way to do things. My question is more to do with trying to learn working with SSIS and less about achieving a result.

I am not sure what you mean by jobs on the server? I think it is perfectly fine SSIS to use a temp table on the SQL server. Then you have 3 tasks in SSIS: (1. SQL Task:Truncate temp table) - (2. Dataflow: Read CSV into Temp Table) - (3. SQL Task: [begin trans --> Delete from target where id in temptable --> insert into target from temp --> commit or rollback based on try catch ] ) — Søren Kongstad
– Søren Kongstad, Commented Mar 5 at 9:31
In fact I often use a "drop table if exists;create table" in the first SQL Task. This way the schema manipulation is included in the SSIS package, which makes deploying easier. just set ValidateExternalMetadata to false! — Søren Kongstad
– Søren Kongstad, Commented Mar 5 at 9:36
I appreciate the input, but the question is specifically about how to create a DELETE FROM statement from a ResultSet in SSIS. As I said, I have a solution for the broader question, that is not the topic here, it's only provided for context. — RedAero
– RedAero, Commented Mar 5 at 13:10
If you need to do it from recordset the most optimized way in my opinion would be to create a stored procedure on the server taking a table paramater, and then create a script task in SSIS that takes the RecordSet and calls the stored procedure using that. Something along the lines of: voluntarydba.com/2012/12/05/… — Søren Kongstad
– Søren Kongstad, Commented Mar 6 at 11:57

Tim Biegeleisen · Accepted Answer · 2025-03-05 04:35:52Z

0

Use an IN clause:

DELETE
FROM yourTable
WHERE id IN (10, 20, 30);

If you needed to do this via passing just one parameter, you could use:

DELETE
FROM yourTable
WHERE '%,10,20,30,%' LIKE '%,' + id + ',%';

To be clear, your search parameter would be %,10,20,30,%.

edited Mar 5 at 4:35

answered Mar 5 at 1:50

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Raj More Mar 5 at 2:35

OP is asking to do this in SSIS where only one command is sent to the database.

Collectives™ on Stack Overflow

Run a single DELETE FROM SQL command based on a RecordSet of multiple rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related