How to do Data Flow Task from/to the same table?

Question

Hi there

I am using SQL Server 2005 SSIS and we are using the Data Flow Task to move data from one table to another. This works well. Now we have another requirement to do data update from the same table using this approach.

Is this possible to use the same approach for as follow:

*) We have a dataset from Table A based on complex query *) We update back to the Table A

The normal query UPDATE INTO is not an option due it takes awhile to process and we can't see the data movement like we did for Data Flow Task.

Any guidance or anything that will be good.

Thanks

Answer 1

either:

• write it to a temporay table and do the update into with a single SQL task after you processed everything
• break it down into smaller chunks based on SSIS variables and OFFSET and use a FOR/FOREACH LOOP

Answer 2

Read the data with a data source in a data flow task, and use ole db command in the data flow to update the data in the same table. If there is no locking when you read and only row-level locking when you update, that should work