Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Job needs to run every 15 minutes for 8 hours to check for existance of a record in a Table for the Current Date Expand / Collapse
Author
Message
Posted Thursday, September 13, 2012 4:24 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:20 AM
Points: 4,195, Visits: 4,267
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?

Thank you.




For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1358955
Posted Thursday, September 13, 2012 5:02 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:20 AM
Points: 4,195, Visits: 4,267
I thinks that I need a package that runs as a step in a job and if it finds the record in the table it executes the second step which would be an SSIS Load Package.

If the second Step in the Job Completes the Job will not run again.

Does this sound right or am I totally off base?


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1358978
Posted Sunday, September 16, 2012 9:24 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 9:05 AM
Points: 54, Visits: 359
have a job that runs every 15 mins. in job step add a ssis package that checks file if exists and today's date is not in the table then insert record
Post #1359957
Posted Monday, September 17, 2012 6:49 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:20 AM
Points: 4,195, Visits: 4,267
Yesterday morning I created an SSIS PAckage that checks for the existence of a record in a table for the current date.

If the record is found the package succeeds, else the package fails.

I created a Job with the 1st Step the SSIS Package that looks for the record. For testing I set the number of entries to 5 at an interval of a minute.

I added a second SSIS task that loads the table.

If a record is found in the table the second step executes to load the data.

I did this in DTS in early 2004 but I could not remember how I did it.


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1360167
Posted Thursday, September 20, 2012 11:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:00 PM
Points: 7,097, Visits: 12,602
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
Post #1362194
Posted Thursday, September 20, 2012 12:53 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:20 AM
Points: 4,195, Visits: 4,267
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.

Thanks!


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1362248
Posted Thursday, September 20, 2012 1:16 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:20 AM
Points: 4,195, Visits: 4,267
btw,

Before I check for the records in a table I also will have two packages and both check for the existence of a file in two separate folders.

Currently this logic is in a C# exe and is run via the task scheduler but I want to incorporate it into SSIS so that I have full control.



For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1362270
Posted Yesterday @ 6:41 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:20 AM
Points: 4,195, Visits: 4,267
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?

Thank you for your help.

Regards.



For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1608244
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse