Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Data Warehousing » Integration Services » SSIS Job needs to run every 15 minutes for 8...
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
Rate Topic
Display Mode
Topic Options
Author
Message
Welsh Corgi
Posted Thursday, September 13, 2012 4:24 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:19 AM
Points: 3,833, Visits: 4,052
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
Welsh Corgi
Posted Thursday, September 13, 2012 5:02 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:19 AM
Points: 3,833, Visits: 4,052
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
sqlbi.vvamsi
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 @ 12:51 PM
Points: 54, Visits: 279
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
Welsh Corgi
Posted Monday, September 17, 2012 6:49 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:19 AM
Points: 3,833, Visits: 4,052
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
opc.three
Posted Thursday, September 20, 2012 11:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:48 AM
Points: 6,728, Visits: 11,774
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1362194
Welsh Corgi
Posted Thursday, September 20, 2012 12:53 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:19 AM
Points: 3,833, Visits: 4,052
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
Welsh Corgi
Posted Thursday, September 20, 2012 1:16 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:19 AM
Points: 3,833, Visits: 4,052
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.