CODEWITHSUNDEEP
ssisdataflowtask
Marc Allen
Marc Allen

Reputation: 11

SSIS DataFlowTask using Record Sets instead of Records

I am using SSIS 2012 with a data flow task having a data source and an Ole DB Sql Task. The data source is creating a set of Id's { 1,2,3, etc } with the Ole DB Sql Task deleting a record in another database-table. What I am seeing in the Sql Profiler is a delete command for each Id which is expected as it is working on a record by record basis. I can get upto 10,000 records.

Is there any way I work with the output of the data source as a set and say:

delete from Table1 where Id in { set of Id's }

Upvotes: 1

Views: 79

Answers (1)

Dominic Goulet
Dominic Goulet

Reputation: 8113

You cannot do that in SSIS.

In fact, you can build an expression and execute that expression in SSIS, but you don't WANT to do that. Expressions are limited in the number of characters they can have, and they are a mess at maintenance time.

Some things are better done directly in a stored procedure, while other are better in SSIS. The art of SSIS is to know when to do it in SSIS or in a procedure.

Good luck!

Upvotes: 3

Related Questions