June 1, 2011 at 10:04 am
Here's the deal. Our SSIS packages are currently running as Windows Tasks instead of scheduled SQL jobs. I know, I know. But that's how it is and the plan is to change over to jobs when the new server is set up but for now I have to deal with it as is.
The tasks call batch files that use the dtexec command. Works just fine.
Now the monkey wrench gets thrown in. There's one package that runs weekly on Friday. Now they want it to do an end-of-month run as well. Okey dokey. But if the end of month job runs on a Friday they're going to get 2 sets of the report. Not efficient.
I know I can use sqlcmd to run the SQL and identify whether the current day is a Friday and run the package if it isn't but I'm just not seeing how to combine the two.
Here's what I kind of see happening.
SQLCMD
IF DATEPART(dw,GETDATE()) = 6
SELECT 'Skip this run'
ELSE
dtexec [package]
GO
EXIT
The SELECT statement shouldn't do much of anything and is just there so the code has something to do on Fridays.
Obviously I haven't used command line SQL much and hope to keep it that way.
TIA.
June 1, 2011 at 10:16 am
Interesting.
My first thought is to move the scheduling logic to a new script task at the beginning of the package, which executes the subsequent tasks if the conditions are met via appropriate variables/precedence constraints, otherwise just exits the package gracefully without doing anything.
Then schedule the package to execute every day in Task Scheduler.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 1, 2011 at 10:25 am
Since this can be handled relatively easily through job scheduling we'd prefer not to edit the package to handle the logic. Plus we're trying to rip out each and every script task unless absolutely necessary. This shouldn't qualify as 'absolutely necessary'.
It is interesting. The extended stored procedures aren't enabled so I can't use the xp_cmdshell stored proc to execute the dtexec command.
My next thought would be to make a new end of month package that handles the scheduling logic and move the task there. Not exactly elegant but neither is using tasks instead of SQL jobs.
June 1, 2011 at 10:35 am
I see your point.
Another option is to use an Execute SQL task to log start and end dates to a job status table - so you can track when the job has run.
Within your package, you could then easily include a check to see whether the job has already run 'today' and exit if true before doing anything. This could be done without a script task 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 1, 2011 at 10:44 am
The package is not going to be changed. So adding tasks isn't my easy out.
From my original code snip you can see I just want to execute the package if the day of the week is not Friday. This is a scheduling task.
Without using the depreciated and dangerous xp_cmdshell command is there a way to do this within a batch file?
Edit - There's supposed to be an audit table that prevents this exact thing but it's not working and making it work is part of my future ongoing project of revising all the packages. So again, the easy way out isn't available to me.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply