Package variables

Couldn't quite find the answer I was looking for via a forum search.

I have 9 packages that are currently called by a master package - all child packages take a datetime variable from the master package which is subsequently used in the child processes.

The question I have is that if I run the master package manually from Visual Studio I can set the master package variable manually to a date of my choosing and run it, which works fine. However we will be wishing to automate the package execution at some point and want to know the best way to run the package(s) on an automated basis and have the data variable supplied to the master package.

What would be the best way to do this ?

Presently we have a variable called MasterPackage_vLoaddate which is a DateTime data type.

Any help appreciated.

You can use the /SET option of dtexec.exe to set your variable at run time.

Or, you can use expressions on that variable if you want to use something like getdate().|||

Thanks Phil..

OK.. you'll need to tell me take me thru this in a little more basic detail

Let's say I wanted to run this job at 07:00 every morning. The variable (date) should always be for the prior day.

So I am guessing i'd need to use somthing like

dateadd(day,-1,getdate())

Can I just add that to the package in VS ? Or where exactly would I use this in order to set the variable correctly. Sorry for being such a noob!!

|||Yes, you have the correct formula.

So, in the properties of the variable you wish to work with, set "EvaluateAsExpression" equal to true. Then, in the "expression" parameter, add your formula. It's that simple.

Every time the package runs, this formula will be evaluated.|||

Will,

Keep in mind that if you want the ability of running the package for any date you choose, you would need to put extra logic on this (e.g. you need to reprocess a day that is not the current day; or if for some reason the package did not run in a specifc day).

You could easily implemnet that logic with a table that keeps track of the executions dates.

Just my two cents

|||

OK...

I'll test that in the morning. I used

set "EvaluateAsExpression" equal to true

and then used the formula getdate("d",-1,getdate())

I am supposing that it will just be a straightforward job of scheduling the master package run in SQL Agent and then the other packages will be called as per expectations ?

Cheers... your reply helped

|||

Will Riley wrote:

I am supposing that it will just be a straightforward job of scheduling the master package run in SQL Agent and then the other packages will be called as per expectations ?

Yep, that should be pretty straightforward.|||

Hi Rafael,

I do already have a table that logs package execution so that in theory, the same day cannot be processed twice. Would you suggest that I derive the date variable from that table (i.e. max date + 1 i.e. something like a SQL command.... SELECT dateadd("d",1,MAX(load_dates.date)) )

If so , how would I pass that to the package ? it might be something I hold in reserve in case the above proves unworkable from Phil.

Cheers

|||

Will Riley wrote:

Hi Rafael,

I do already have a table that logs package execution so that in theory, the same day cannot be processed twice. Would you suggest that I derive the date variable from that table (i.e. max date + 1 i.e. something like a SQL command.... SELECT dateadd("d",1,MAX(load_dates.date)) )

If so , how would I pass that to the package ? it might be something I hold in reserve in case the above proves unworkable from Phil.

Cheers

My solution will work, it just won't be as flexible as Rafael's.

For the table derived option, use an Execute SQL task in the control flow to select the max(date)+1 and then store the result into your package's date variable.|||

Just one more thing....

Am I likely to run in to an issue with times?

The above solution will equate to a datetime value of something like

11/02/2007 07:00

I would rather have

11/02/2007 00:00

As there may well be issues if the time is anything other than 00:00

Is there a simple bit of SSIS "expression" that will evaluate to "yesterday" at 00:00 ?

|||

Will Riley wrote:

Just one more thing....

Am I likely to run in to an issue with times?

The above solution will equate to a datetime value of something like

11/02/2007 07:00

I would rather have

11/02/2007 00:00

As there may well be issues if the time is anything other than 00:00

Is there a simple bit of SSIS "expression" that will evaluate to "yesterday" at 00:00 ?

Put this in front of your formula: (DT_DBTIMESTAMP)(DT_DBDATE)

So you would have:
(DT_DBTIMESTAMP)(DT_DBDATE)dateadd("d",-1,getdate())

If you don't even want the 00:00 on the end, don't include the (DT_DBTIMESTAMP) cast.|||

Phil Brammer wrote:

My solution will work, it just won't be as flexible as Rafael's.

I never meant it won't work. I just wanted to add a point I thought was important to get that issue solved.

|||

Will Riley wrote:

Hi Rafael,

I do already have a table that logs package execution so that in theory, the same day cannot be processed twice. Would you suggest that I derive the date variable from that table (i.e. max date + 1 i.e. something like a SQL command.... SELECT dateadd("d",1,MAX(load_dates.date)) )

If so , how would I pass that to the package ? it might be something I hold in reserve in case the above proves unworkable from Phil.

I think the approach beeing discussed would work fine; you just need to base the logic on that table you already have (good you already have it!).

Having a logic that depends in 'today' date is asking for trouble in long long term; when things do not happen/run as expected.

|||

Rafael Salas wrote:

I never meant it won't work. I just wanted to add a point I thought was important to get that issue solved.

I agree. Will mentioned it.|||

Phil, Rafael,

Thanks both - between you you provided both the answer(s) I needed and some good advice in terms of the use/dangers of dynamic variables.

Cheers