How to use resultset as a source for UPDATE SQL

I am new to SQL2005 SSIS. I would like to know whether the output of a data flow can be the data source of UPDATE SQL or not. If yes, how?

My situation is I use data flow task to select and transform some data from a Table A. Then, I transfer then output to a resultset destination and store the value in a variable X.

After data flow task finish, I want to use the data to perform update SQL by SQL task in control flow e.g.

update table B set fieldB1 = fieldA1 from X where B.key = X.key

I know I can store the output in data flow in a temp table, run SQL and then drop the temp table. But is it approcach is slow? Using variable/resultset is much faster?

Thanks.

It would be nice if you could do this but unfortunately you can't. If the update set is in a variable then your only option is to use an OLE DB Command inside a data-flow and if you have a lot of data in here it isn't in the least bit performant.

Dropping data to a temp table is dfinately teh fastest way to go.

In the past I have asked for a data-flow destination that does set based updates - not sure whether we'll ever get it though.

-Jamie

|||

I am finding this very very hard to believe, but I do.
I am actually speachless and I am currently nurturing a sore forehead(from hitting against my desk) at the moment.

I thought how hard can it be to a simple UPDATE with params...I am still not sure I am remotely close to achieving this.

Here is my sample:
UPDATE GLT

SET GLTREF = '-1'

WHERE GLTranNo = ?

The ? would be coming from a datasource above

What do I use to get this done?

It's almost as if one cannot do Updates in SSIS

Regards,

Pieter