July 27, 2011 at 10:35 pm
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
July 27, 2011 at 11:03 pm
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
July 28, 2011 at 5:12 am
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.
July 28, 2011 at 9:13 am
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
July 28, 2011 at 9:51 am
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.
July 28, 2011 at 9:54 am
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
July 28, 2011 at 10:17 am
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.
July 28, 2011 at 1:13 pm
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
July 28, 2011 at 1:57 pm
Thank you very much will check and update you by end of the day.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply