Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to assign user variable a GUID value in Execute SQL Task using SSIS 2008 Expand / Collapse
Author
Message
Posted Wednesday, July 27, 2011 10:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 28, 2011 1:55 PM
Points: 5, Visits: 15
Hello All,
Here is the issue I’m facing.
My task is to get guid’s from database field and run few updates based on the guid values.
I had taken execute sql task which will execute a query to return bunch of guid’s. I had given Result Set as Full result set for Execute SQL task and declared a variable as user::sptkey. While declaring variable there is no data type of guid so I had taken it as string.
My idea in assigning the result set to a user variable is to use foreach loop container and perform the updates based on the individual guid’s.
When I try to execute the execute sql task individually here is the error message I got.
[Execute SQL Task] Error: Executing the query "SELECT spt_key
FROM TMPtable
where spt_key = '9F8..." failed with the following error: "The type of the value being assigned to variable "User::strcst_key" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Does anybody have idea what may be the issue.
Thanks


Post #1149804
Posted Wednesday, July 27, 2011 11:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
mmohan.net (7/27/2011)
Hello All,
Here is the issue I’m facing.
My task is to get guid’s from database field and run few updates based on the guid values.
I had taken execute sql task which will execute a query to return bunch of guid’s. I had given Result Set as Full result set for Execute SQL task and declared a variable as user::sptkey. While declaring variable there is no data type of guid so I had taken it as string.
My idea in assigning the result set to a user variable is to use foreach loop container and perform the updates based on the individual guid’s.
When I try to execute the execute sql task individually here is the error message I got.
[Execute SQL Task] Error: Executing the query "SELECT spt_key
FROM TMPtable
where spt_key = '9F8..." failed with the following error: "The type of the value being assigned to variable "User::strcst_key" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Does anybody have idea what may be the issue.
Thanks


TMPTable.spt_key is a UNIQUEIDENTIFIER correct?

What type of variable is User::strcst_key? It should be a DT_GUID. Integration Services Data Types

If you want to store it in a string then you'll need to CAST it on the way out of SQL, like this:

SELECT CAST(spt_key as VARCHAR(100)) as spt_key
FROM TMPtable
where spt_key = '9F8...'



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1149811
Posted Thursday, July 28, 2011 5:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 28, 2011 1:55 PM
Points: 5, Visits: 15
Thanks for your prompt reply.

You are correct spt_key in temptable is a uniqueidentifier column. Since there is no datatype as guid during variable creation I picked string. I want it to be stored in guid and use it further. Please let me know how can i acheive it.

will declaring user variable user::spt_key as an object and converting it to guid in script task works. if so do you have any example.

appreciate your time.
Post #1149975
Posted Thursday, July 28, 2011 9:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
patelmia (7/28/2011)
Thanks for your prompt reply.

You are correct spt_key in temptable is a uniqueidentifier column. Since there is no datatype as guid during variable creation I picked string. I want it to be stored in guid and use it further. Please let me know how can i acheive it.

will declaring user variable user::spt_key as an object and converting it to guid in script task works. if so do you have any example.

appreciate your time.

Use the variable type of Object to catch the result from the database if you want the UNIQUEIDENTIFIER available in its binary form in SSIS, i.e. usable as a true GUID and cast-able in SSIS to a DT_GUID type in an expression. This is probably not what you want.

Use the variable type of String to catch the result from the database and CAST the UNIQUEIDENTIFIER to a VARCHAR as I have it in my previous post if you want to use it in string concatenation in expressions and scripts. This is more likely what you want.



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1150270
Posted Thursday, July 28, 2011 9:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 28, 2011 1:55 PM
Points: 5, Visits: 15
Hi opc.three,

Thanks for your reply. Do you have any sample peice of code which shows how to use object inorder to acheive this task.

my taks is I will get 10 guid's from select query need to loop through each guid and do some updates on other table with guid value.

Post #1150328
Posted Thursday, July 28, 2011 9:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
patelmia (7/28/2011)
Hi opc.three,

Thanks for your reply. Do you have any sample peice of code which shows how to use object inorder to acheive this task.

my taks is I will get 10 guid's from select query need to loop through each guid and do some updates on other table with guid value.


I could provide a sample SSIS pkg, but before I do, now that we have tracked down the problem this far, why are you doing this in T-SQL?

Is your "other table" in the same database where you are getting the GUID from in the first place? If so, then why use SSIS? A simple set of T-SQL queries, possibly even one query, may get the job done for you. Can you share a little more about what you're doing?


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1150335
Posted Thursday, July 28, 2011 10:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 28, 2011 1:55 PM
Points: 5, Visits: 15
hi opc.three,

Sorry for the delay in sending message.

yes you are correct they both resides in the same database and simple proc which holds all the transactions will be fine. but i have orders to do it ssis. that's why i'm doing it.
Post #1150358
Posted Thursday, July 28, 2011 1:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
patelmia (7/28/2011)
Hi opc.three,

Thanks for your reply. Do you have any sample peice of code which shows how to use object inorder to acheive this task.

If you're going to be using the GUID to submit back to the database as part of another SQL query then I think you'll want to use the string approach and CAST your UNIQUEIDENTIFIER column to a CHAR(36) on the way out. Forget that I used a VARCHAR(100) before, that was for expediency, I did not check to see which data type would be best for the job. CHAR(36) is the proper datatype.

my taks is I will get 10 guid's from select query need to loop through each guid and do some updates on other table with guid value.

I would recommend this workflow in your SSIS pkg:

1. Use an Execute SQL Task where the Resultset is set to "Full result set". The query should retrieve your GUIDs (cast as strings) and store that in a variable of type Object.
2. Use a ForEach Loop Container to iterate over the ADO Recordset in your Object variable.
3. Inside your ForEach Loop Container use however many Execute SQL Tasks you need to make further changes to the database. On the Execute SQL Task you can dynamically set the value of teh the SqlStatementSource Property using an Expression. The Expression will be your query which you can build using the GUID variable supplied by your ForEach Loop Container.

It sounds like a lot, but it's not too difficult. Here are some visual tutorials on working with Expressions in SSIS to help you get started.

Intro on Expressions: Expression Language Basics

This is related to Step 2 above. Using the For Each ADO Enumerator

This one is not directly related to your problem, but it does show how to set a property using an Expression. Adding a Time Stamp to a File Name in SSIS


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1150523
Posted Thursday, July 28, 2011 1:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 28, 2011 1:55 PM
Points: 5, Visits: 15
Thank you very much will check and update you by end of the day.
Post #1150541
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse