SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to assign user variable a GUID value in Execute SQL Task using SSIS 2008


How to assign user variable a GUID value in Execute SQL Task using SSIS 2008

Author
Message
patelmia
patelmia
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 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
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15139 Visits: 14396
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
patelmia
patelmia
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 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.
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15139 Visits: 14396
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
patelmia
patelmia
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 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.
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15139 Visits: 14396
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
patelmia
patelmia
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 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.
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15139 Visits: 14396
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
patelmia
patelmia
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 15
Thank you very much will check and update you by end of the day.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search