SSIS BULK UPDATE

Question

SSIS BULK UPDATE

day-476284

SSCommitted

Points: 1844
More actions
September 4, 2008 at 10:22 am

#197805

Hi,
I designed an SSIS package which includes "BULK UPDATE" part.
Table1([ID],[Status])
1.Execute SQL Task: (GetResultSet)
I put all the IDs into ResultSet Variable. that their Status need to get updated.
2.Foreach Loop Container:
Uses enumarator which iterates the IDs row by row from the ResultSet variable.
3.Execute SQL Task: (UpdateStatus)
Runs inside of the Foreach Loop Container and UPDATE the [Status] field in Table1
UPDATE Table1 SET Status = 2 WHERE ID = ?
This BULK UPDATE part takes very long time to process.
What I would prefer is:
Run Execute SQL Task without using Foreach Loop Container which can run a query like:
UPDATE Table1 a INNER JOIN [User::ResultSet] b ON a.ID=b.ID SET Status = 2
OR
UPDATE Table1 SET Status = 2 WHERE ID IN (SELECT * FROM [User::ResultSet])
The problem is I could not manage to EITHER join to [User::ResultSet] variable OR select from that.
Anyone can suggest any workaround?
Thanks,
/Day
March 25, 2019 at 5:48 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Why not just use a data flow task to insert the data into a table or temp table on the server that holds the table you want to update?

day-476284 SSCommitted Points: 1844 More actions · Answer 2

Hi Michael,

Thank you for your repsonse.

I resolved the problem using Dataflow.

Because there is no index set on the ID field, it is taking a long time to match those records from Table1 but compare to foreach loop container it is faster.

Thanks again for the help.