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.
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