Dynamic Scheduling via SSIS and SQL Server Agent?

[GrandMauler]

Ok, this is what I want to do:

I've developed an SSIS Package that goes to a folder, finds a certain file, pulls the file and processes it.

It does this once a month.

However, sometimes the file wont be there on that day. It may get there the following day or the day after.

I've put the SSIS package in a Scheduled SQL Job.


Within the SSIS package, I'd like to put a check where, if it doesn't find the file, it doesn't go any further and reschedules the job for the next day.


I'm thinking of putting, within the SSIS package, a logic tree where, if it doesn't find the file, it would then execute an SQL Task using sp_update_job and sp_attach_schedule... basically updating the schedule to run once a day until it finds the file.

If it does find it, the job is updated to run once a month on the first monday.


Has anyone done this sort of thing before?



Thanks in advance.

 

[mrdenny]

Same answer as in the other thread.

It should work fine, however you may want to look into adding a new one time schedule instead of changing your monthly schedule.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com