Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Job needs to run every 15 minutes for 8 hours to check for existance of a record in a Table for...


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

Author
Message
Welsh Corgi
Welsh Corgi
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5158 Visits: 4863
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/
Welsh Corgi
Welsh Corgi
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5158 Visits: 4863
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/
sqlbi.vvamsi
sqlbi.vvamsi
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 412
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
Welsh Corgi
Welsh Corgi
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5158 Visits: 4863
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/
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8235 Visits: 14368
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
Welsh Corgi
Welsh Corgi
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5158 Visits: 4863
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/
Welsh Corgi
Welsh Corgi
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5158 Visits: 4863
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/
Welsh Corgi
Welsh Corgi
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5158 Visits: 4863
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/
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8235 Visits: 14368
In the case of Oracle being the target I would go with option 2 and avoid introducing the use of OPENQUERY.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Welsh Corgi
Welsh Corgi
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5158 Visits: 4863
opc.three (8/29/2014)
In the case of Oracle being the target I would go with option 2 and avoid introducing the use of OPENQUERY.


Thanks!

How do I Execute in .NET to check the record count?

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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search