September 13, 2012 at 4:24 pm
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/
September 13, 2012 at 5:02 pm
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/
September 16, 2012 at 9:24 pm
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
September 17, 2012 at 6:49 am
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/
September 20, 2012 at 11:28 am
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
September 20, 2012 at 12:53 pm
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/
September 20, 2012 at 1:16 pm
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/
August 28, 2014 at 6:41 am
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/
August 29, 2014 at 9:05 pm
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
August 30, 2014 at 7:09 am
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/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply