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.