Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

displaying a query's value in an ssis variable Expand / Collapse
Author
Message
Posted Wednesday, November 06, 2013 12:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 5:23 AM
Points: 45, Visits: 164
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?
Post #1511739
Posted Wednesday, November 06, 2013 2:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 12,206, Visits: 9,168
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?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1511747
Posted Wednesday, November 06, 2013 2:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 5:23 AM
Points: 45, Visits: 164
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
Post #1511748
Posted Wednesday, November 06, 2013 2:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 5:23 AM
Points: 45, Visits: 164
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
Post #1511750
Posted Wednesday, November 06, 2013 2:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 12,206, Visits: 9,168
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1511751
Posted Wednesday, November 06, 2013 2:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 5:23 AM
Points: 45, Visits: 164
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
Post #1511756
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse