How to Put a MAX(Date) from a Table into an SSIS Variable
-
I can't seem to figure out how to add a variable that uses a table for its value.
I'd like to use
SELECT DATEADD(HH, -1,MAX(LastModifiedDate)) FROM TicketsTable
As my variable.
Currently I use something like DATEADD(DAY, -2,MAX(GETDATE())
Seems like it should be super simple...but I'm just not getting it.
More details on the task.
I pull data from a ticketing database into a reporting database. I pull this data nightly. My variable pulls system time - 2 days. If I can pull the Last Modified Date from the Reporting database tickets tables it would reduce the resource cost dramatically and allow me to run it dynamically instead of every night.
Thank you,
-
dirkdirden (7/5/2016)
I can't seem to figure out how to add a variable that uses a table for its value.I'd like to use
SELECT DATEADD(HH, -1,MAX(LastModifiedDate)) FROM TicketsTable
As my variable.
Currently I use something like DATEADD(DAY, -2,MAX(GETDATE())
Seems like it should be super simple...but I'm just not getting it.
More details on the task.
I pull data from a ticketing database into a reporting database. I pull this data nightly. My variable pulls system time - 2 days. If I can pull the Last Modified Date from the Reporting database tickets tables it would reduce the resource cost dramatically and allow me to run it dynamically instead of every night.
Thank you,
It's all explained, by way of example, here[/url].
-
Holy Crap baskets that is a lot of steps for such a seemingly easy task.
I'll give it a shot.
-
It's explained in detail & perhaps that makes it seem a lot. It really isn't.
Basically, you need a variable. You run an execute SQL task and map the results of executing the query to the variable. Job done.
-
dirkdirden (7/5/2016)
Holy Crap baskets that is a lot of steps for such a seemingly easy task.Goes right along with the Holy Snappin' Ducksh!t that you have to fill those baskets with and is a good reason why I try to avoid SSIS at almost any cost. Certainly, there are those that will disagree.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply