I have a C# Solution and it checks for the existence of a file named Complete.txt that is generated from a Vendor's Proprietary AS400 DB2 Database.

That tasks is currently run from Windows Task scheduler. It starts at 3:00 AM and runs every 15 minutes and if it finds a file in a folder for the current date it creates a record with the Date and Time.

I intend to incorporate this into an SSIS Package and schedule as a Job to gain control of the code and schedule as a SQL Server Job.

I will a lot of SSIS Packages that I need to add an Execute SQL Task to check for the existence of the record starting at 3:00 AM and if it finds the record it will execute multiple containers that include tass to load the Staging Tables with an AS400 Source.

I intend to schedule as a Job and run every 15 minutes but once the record is found I want to execute the rest of the package and I do not want the Job to Kick off again until the next day.

I recall writing a DTS Package in 2004 that check for the existence of a File and it ran every 15 minutes until it found the file then it executed the package.

Unfortunately I can't remember how I did it.

If anyone has done something like this I would very much appreciate any suggestions or ideas?

Why let the package that checks for the record exit? Just let it run. This way you would only need on SSIS package.

There are lots of options but here are two:

1. Use an Execute SQL Task that calls a stored procedure that employs WAITFOR DELAY. The proc would loop waiting for 15 minutes if no record were found and would return if a record were found allowing the SSIS package to continue.

2. Use a Script Task that calls a stored procedure to check for the record that returns a resultset letting your code know whether it should proceed. Have the .NET code loop over calling this proc using Thread.Sleep to wait 15 minutes in between attempts.

The end result is that you are not having to start an SSIS package every 15 minutes, creating msdb job history activity and memory and CPU activity loading and unloading an SSIS package. You also avoid having to construct Control Flow logic in an Agent Job which avoids creating a dependency between your SSIS package and your job scheduler.

__________________________________________________________________________________________________There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

opc.three (9/20/2012)Why let the package that checks for the record exit? Just let it run. This way you would only need on SSIS package.

There are lots of options but here are two:

1. Use an Execute SQL Task that calls a stored procedure that employs WAITFOR DELAY. The proc would loop waiting for 15 minutes if no record were found and would return if a record were found allowing the SSIS package to continue.

2. Use a Script Task that calls a stored procedure to check for the record that returns a resultset letting your code know whether it should proceed. Have the .NET code loop over calling this proc using Thread.Sleep to wait 15 minutes in between attempts.

The end result is that you are not having to start an SSIS package every 15 minutes, creating msdb job history activity and memory and CPU activity loading and unloading an SSIS package. You also avoid having to construct Control Flow logic in an Agent Job which avoids creating a dependency between your SSIS package and your job scheduler.

ok, thanks for the ideas, that sounds much better.

I will have at least two packages because there is data from more than one source.

opc.three (9/20/2012)Why let the package that checks for the record exit? Just let it run. This way you would only need on SSIS package.

There are lots of options but here are two:

1. Use an Execute SQL Task that calls a stored procedure that employs WAITFOR DELAY. The proc would loop waiting for 15 minutes if no record were found and would return if a record were found allowing the SSIS package to continue.

2. Use a Script Task that calls a stored procedure to check for the record that returns a resultset letting your code know whether it should proceed. Have the .NET code loop over calling this proc using Thread.Sleep to wait 15 minutes in between attempts.

The end result is that you are not having to start an SSIS package every 15 minutes, creating msdb job history activity and memory and CPU activity loading and unloading an SSIS package. You also avoid having to construct Control Flow logic in an Agent Job which avoids creating a dependency between your SSIS package and your job scheduler.

Thanks for your input on this.

Sorry I'm not sure how to proceed with option 1.

The project got cut but now I'm in a similar situation.

I have to check for the existence of a record in an Oracle Table for the current Date that the Oracle Loads have finished. Then I can go on to execute several child packages.

It looks like I need to use OPENQUERY.

For option 1 I can use WAITFORDELAY? Would that cause the SP to loop every 15 minutes until it found the record? How do I tell it to execute the next task?