Showing posts with label resultset. Show all posts
Showing posts with label resultset. Show all posts

Monday, March 19, 2012

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

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

Wednesday, March 7, 2012

How to Use Data Type GUID in UserVariable or foreach loop?

Hi Folks,

I am trying to build a Package that selects a list of Uniqueidentifiers by "Execute SQL-Task" and then loops through the ResultSet of the query using ADO.Net Enumerator to do something with this GUID Value, namely deleting all entrys with this PK in a different DataBase.

The main Problem I am facing is that you can′t select the type "GUID" for a user Variable in "Execute SQL-Task". All the Datatypes are there except for GUID!

This leads to the following error:

Error: 0xC001F009 at Package: The type of the value being assigned to variable "User::ImagicID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Is this a bug? Or am I doing something terribly wrong?

Best Regards and thanks in advance for your help

Chucker

Hi,

What is the variable initially defined as in the variables section? I believe it has to match that of the one that is being referenced in the SQL parameter mapping editor.

Is it possible for you to perform a CAST or CONVERT in the SQL statement to return the GUID ID as a string/varchar and do a string comparison when checking the GUID against the primary key?

Its just a though but i hope it helps.

Grant|||

For the Iterator (in the ForEach Loop), the only type that works (without converting) is "object".

When using this Type in the Delete Statement, I get the error that this is the wrong type.

So this may work if I only knew how to convert that "object" to GUID in a SQL Statement

I want to write something like

delete from table where pk = @.Uservariable

in the Execute SQL-Task within the ForEach Loop

then I get the error Message that @.Uservariable is not of type GUID

as I already mentioned in the Property Page of the ForEach Loop where

the colum pk can be mapped to a uservariable @.uservariable, the data

Type GUID does not exist.

Cheers

Martin

|||Did you try string? I had a similar issue, but it involved integers. According to books online this shouldn't be an issue, but stranger things have happen.