Answered by:
How to store a result set of an execute sql task in a variable ?

-
If my sql result set is a single SQL String, I want to be able to assign it to an SSIS string variable. If its a SQL datetime, then I want it to be assigned to a SSIS datetime. etc.
Can i do this directly or do i have to store result set into a "single row" ie SSIS object and then use a script task to assign that "single row" to an SSIS variable ?
Question
Answers
-
you cant have SSIS variable to change datatype at runtime.
What you can do is to create two variables and then based on your requirement populate them so that only one of them has a value at any time.
- Proposed as answer by Mike YinMicrosoft contingent staff, Moderator Tuesday, November 19, 2013 4:53 PM
- Marked as answer by Mike YinMicrosoft contingent staff, Moderator Sunday, November 24, 2013 3:36 PM
-
You could try creating a variable of type Object. One caveat, I am pretty sure that you would not be able to change the type of the variable in a loop, so once you assign it a string, it will remain a string.
I think that the challenge would be in using that variable later.
Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com
- Proposed as answer by Mike YinMicrosoft contingent staff, Moderator Tuesday, November 19, 2013 4:53 PM
- Marked as answer by Mike YinMicrosoft contingent staff, Moderator Sunday, November 24, 2013 3:36 PM
All replies
-
you cant have SSIS variable to change datatype at runtime.
What you can do is to create two variables and then based on your requirement populate them so that only one of them has a value at any time.
- Proposed as answer by Mike YinMicrosoft contingent staff, Moderator Tuesday, November 19, 2013 4:53 PM
- Marked as answer by Mike YinMicrosoft contingent staff, Moderator Sunday, November 24, 2013 3:36 PM
-
You could try creating a variable of type Object. One caveat, I am pretty sure that you would not be able to change the type of the variable in a loop, so once you assign it a string, it will remain a string.
I think that the challenge would be in using that variable later.
Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com
- Proposed as answer by Mike YinMicrosoft contingent staff, Moderator Tuesday, November 19, 2013 4:53 PM
- Marked as answer by Mike YinMicrosoft contingent staff, Moderator Sunday, November 24, 2013 3:36 PM