Looping SSIS on database query

Question

Looping SSIS on database query

ben-732740

SSC Rookie

Points: 39
More actions
February 22, 2011 at 10:16 pm

#235445

I would like to have an SSIS package which "watches" a table for a certain condition, and when the condition is met, produce a file.
I've thought of a couple of ways of doing this, none of them are that ideal.
One is to have a SP which is started when the server is started, it would loop on a query, and if the query returns a record, it would start a job which would run the package. This is simple, but if the job fails, the calling SP has no way of knowing unless the SSIS package writes to a table on completion.
Another is to have a job which starts the SSIS package that loops forever, checking the table, then running the export if needed. This is ok but I'd rather have the package started asynchronously with a trigger. I could do this using service broker, but I'm not sure I want the overhead of starting it all up.
Finally, the last way would be to avoid AAIS completely and write a stand alone app that queries and loops, but I hate having so many moving parts outside the database.
Any ideas?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246988 More actions · Answer 1

No doubt there is some underlying operation on the table which causes the condition to be true (insert, update etc). Have you thought about creating one or more triggers on the table itself which can start an appropriate job to execute the package?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Anjan Wahwar SSCommitted Points: 1617 More actions · Answer 2

ben-732740 (2/22/2011)
I would like to have an SSIS package which "watches" a table for a certain condition, and when the condition is met, produce a file.
I've thought of a couple of ways of doing this, none of them are that ideal.
One is to have a SP which is started when the server is started, it would loop on a query, and if the query returns a record, it would start a job which would run the package. This is simple, but if the job fails, the calling SP has no way of knowing unless the SSIS package writes to a table on completion.
Another is to have a job which starts the SSIS package that loops forever, checking the table, then running the export if needed. This is ok but I'd rather have the package started asynchronously with a trigger. I could do this using service broker, but I'm not sure I want the overhead of starting it all up.
Finally, the last way would be to avoid AAIS completely and write a stand alone app that queries and loops, but I hate having so many moving parts outside the database.
Any ideas?

Hi,

One idea is to create a trigger (Update, Insert or Delete) on your table. You can then use SQL Server Alert feature which can be used to fire SSIS job based on the alert it receve from trigger.

Please see the nice post (link below) wriiten on alert by Leo.

http://www.sqlservercentral.com/articles/Administration/sqlserveralerts/1435/

Thanks

Anjan

__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!

ben-732740 SSC Rookie Points: 39 More actions · Answer 3

I'd use triggers, but only in combination with Service Broker. I don't like putting anything as heavy as starting a job in a trigger. Plus, what happens if the job is already running? I don't want to take the chance of guessing what kind of impact starting a job will have on table updates.

happycat59 One Orange Chip Points: 29389 More actions · Answer 4

Starting a job from a trigger may get a little tricky. You need to check whether it is already running. You need to grant permission to start the job to users - if your application is not using a shared connection, you may well end up having to grant "SQLAgentOperatorRole" to them and, personally, as a DBA I would not let that happen. And there may also be performance consideration wrt running sp_start_job.

I would prefer to use something like Service Broker - all your application needs to do is to write to a queue. Service broker is then responsible for starting processes to deal with the message on the queue. The SSIS package will need to deal with get messages off the queue, processing the message (you already have that logic assuming you have written the package already or at least specified what it needs to do) and finally, the package will need to tell Service Broker that it has completed processing of the message.

There are other mechanisms for achieving this same logic without needing Service Broker but the logic is the same. It just depends on who/what is responsible for providing and managing the messaging.

ben-732740 SSC Rookie Points: 39 More actions · Answer 5

I agree triggers to start jobs does not seem right. What would you use other than service broker?

COZYROC One Orange Chip Points: 28499 More actions · Answer 6

ben-732740 (2/23/2011)
I'd use triggers, but only in combination with Service Broker. I don't like putting anything as heavy as starting a job in a trigger. Plus, what happens if the job is already running? I don't want to take the chance of guessing what kind of impact starting a job will have on table updates.

You don't have to start a job from the trigger. What you can do is have a table with two columns:

|MustProcess|Processing|

When the trigger finds matching data, it will set MustProcess column. You can have a separate process, which periodically checks this table. If it sees that MustProcess is set, then you will start your process and set Processing column. After it finishes processing, you will reset Processing column.

This is of course very simplistic explanation, but you get the idea.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

chris.stuart SSCarpal Tunnel Points: 4965 More actions · Answer 7

ben-732740 (2/22/2011)
I would like to have an SSIS package which "watches" a table for a certain condition, and when the condition is met, produce a file.

Wel,, how often is this table going to be updated, how critical is it that the package kicks off right after the table has change.

If there is a time period that can lapse between the condition being met and the package being kicked off, why now, check in your ssis package for the condition, via a ssis kicking off a SP. If the return value (condition) is met kick off further processing.

I run all my stuff via SQL Server Agent, it kicks off the pacakges evey hour (you could set it down to say 1 min) and the packages checks for files to load, if they are there, do the processing and if anything goes wrong, SQL Server Agent, sends me an email.

This might not work in your case, but if there is a bit of agrace period, why not?:-)