ssis control flow execute sql vs data flow sql command

Question

I have a stored procedure(sp_selectClient) having a cursor which is performing some insert operations in every iteration. In last statement of sp I selected the result from a table.

Now I am creating a ssis package and I am new in this. I found the simplest way by adding Data Flow Task in Control Flow and then inside Data Flow I added one source(OLEDB) in which I set the SQL Command 'EXEC sp_selectClient'. The stored procedure do not accept any parameters. And then I mapped it into destination(flat file). My query is can I do this with Execute SQL Task also. If yes then what are the advantages and disadvantage of doing this.

Arockia Nirmal · Accepted Answer · 2017-02-27 09:47:49Z

3

Yes you can do the same with Execute SQL task also.

OLE DB Command inside data flow task: Will always process the data row by row

Execute SQL task in control flow: will process the data in bulk

What is the difference between Execute SQL Task and OLE DB Command

answered Feb 27, 2017 at 9:47

Arockia Nirmal

7871 gold badge7 silver badges20 bronze badges

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

Comments

Hadi · Accepted Answer · 2018-01-03 08:48:51Z

If your Stored procedure create a result set, you can do this using Execute SQL Task and storing the Resultsets inside a variable of type Object Then looping over rows inside a Script Task.

These are usefuls links that helps you to store ResultSets inside a variable and loop over its rows inside a Script Task:

If your stored procedure does not produce any ResultSets (Select command) it is best practice to use an Execute SQL Task

The OLE DB Command runs insert, update or delete statements for each row, while the Execute SQL Task does a Bulk Insert in this instance. That means every single row that goes through your package would have an insert statement run when it gets to an OLE DB Command.

References:

SSIS – Avoid OLE DB Command

Collectives™ on Stack Overflow

ssis control flow execute sql vs data flow sql command

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related