displaying a query's value in an ssis variable

Question

Post reply

displaying a query's value in an ssis variable

frdrckmitchell7

Ten Centuries

Points: 1351
More actions
November 6, 2013 at 12:54 am

#284562

Hi all you clever people
I have the following query (I suppose it is self-explanatory, but...):
select max(a.TransDateNumeric) as ToDate from BI1_DW_Dim_CalendarDefinition a
join BI1_DW_Dim_SystemParameters b on b.RUN_Type = 'SALES' AND
(B.RUN_PeriodFrom = A.TransPeriod OR B.RUN_PeriodTo = A.TransPeriod)
WHERE A.TransDateSmall < DATEADD(D,-1, GETDATE())
GROUP BY A.TransPeriod
which displays the value: 20131105 (you guessed right - it is yesterday's date as an integer value).
I want to run this query in an ssis variable so that it displays the value (at the moment my value display the query itself).
Any help anyone?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

You can run the query in an Execute SQL Task and store the result in a variable.

You can only see the actual value of the variable though when debugging the package.

I'm not sure that's what you want?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

frdrckmitchell7 Ten Centuries Points: 1351 More actions · Answer 2

Hi Koen, thanks for replying.

Not sure how to set up my variable (it's an integer in our database so what would the data type be as a variable?). All the data types I use (except the object data type) gives me an error when I run the Execute SQL Task.

If you have any input please let me know.

Kind regards

Fred

frdrckmitchell7 Ten Centuries Points: 1351 More actions · Answer 3

Hi Koen

It actually works when I set it up as an object. I just didn't debug it, so put a Script Task up and it actually displays it as 20131105. I suppose I should cast it to set it as a date when using it later?

Kind regards

Fred

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 4

The data type of the variable should be integer.

Make sure you select single row result set in the Execute SQL Task.

If you want it as a date, I would cast it in the SQL query itself.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

frdrckmitchell7 Ten Centuries Points: 1351 More actions · Answer 5

Hi Koen

There's no other variable datatype that I use that seem to work other than the object data type.

Will make do with it in the meantime and see where I get stuck.

Thanks for your help though.

Kind regards

Fred