June 30, 2008 at 4:38 am
Hi everyone,
I currently have two dts packages which i would like to combine, i know what i need to do but i dont know how.
DTS Package1: This pulls data for the current month to date.
But on the 1st of everymonth i need to run the data for the whole of the previous month, therefore that same as DTS1 but the data parameters are different.
DTS Packages2: Pulls data for all dates for previous month
Therefore at the start of a dts package i need it to say, if getdate() is 1st of month then run DTS2, ELSE run DTS1. I can copy all the taks into one dts pakacge and combine them im just not sure on how to go about making it select which tasks to do.
Any help would be great.
Thanks
Paul
June 30, 2008 at 7:28 am
It's a bit of a grim hack - but you could set up a VBA script task to check whether it's the first of the month, and raise a DTS fail or success accordingly - then link Success and Failure workflows to execute package tasks running the appropriate DTS package.
Ugly, though.
June 30, 2008 at 7:40 am
Checkout some samples on http://www.sqldts.com or http://www.sqlis.com
Rather check this: http://www.sqldts.com/215.aspx
Maninder
www.dbanation.com
June 30, 2008 at 9:35 am
I would do the date check in a job step and jump to the appropriate jobe step to execute one of the packages.
Example:
Job step 1: if date is first of month, go to next step else go to step 3
Job step 2: execute DTS2
Job step 3: execute DTS1
Greg
June 30, 2008 at 9:40 am
How would i go about doing this?
Sorry if i am being thick i am new to dts pakcages, this is the first time i have built one
June 30, 2008 at 10:25 am
No problem. Are you working in SQL 2005 Management Studio or in SQL 2000 Enterprise Manager and do you know how to create a scheduled job?
Greg
July 1, 2008 at 1:45 am
Yes i am, and yes i know how to create a scheduled job....
July 1, 2008 at 8:51 am
Okay, here's how I'd do it, briefly:
Job step 1: T-SQL step
if (select day(getdate())) = 1
raiserror('first day of month',15,1)
Use Raiserror to make the step fail if it's the 1st of the month. On the 'Advanced' tab,
set the 'on success action' to 'go to step 3' and the 'on failure action' to 'go to next step'.
note: I made up my own error (15) and severity (1). You just need some values for Raiserror.
Job step 2: DTSrun/DTS1...
On the 'Advanced' tab, set the 'on success action' to 'quit the job reporting success' and
the 'on failure action' to 'quit job reporting failure'.
Job step 3: DTSrun/DTS2...
On the 'Advanced' tab, set the 'on success action' to 'quit the job reporting success'
and the 'on failure action' to 'quit job reporting failure'.
Greg
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply