Remove From My Forums

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

Question

0

Sign in to vote

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 ?

Saturday, November 16, 2013 8:12 AM

Reply

|

Quote

Answers

0

Sign in to vote
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
Saturday, November 16, 2013 8:27 AM

Reply

|

Quote

0

Sign in to vote
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
Saturday, November 16, 2013 11:22 AM

Reply

|

Quote

All replies

0

Sign in to vote
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
Saturday, November 16, 2013 8:27 AM

Reply

|

Quote

0

Sign in to vote
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
Saturday, November 16, 2013 11:22 AM

Reply

|

Quote