Wait for a record

Question

Wait for a record

Tim Riley-437154

SSC-Addicted

Points: 469
More actions
August 13, 2007 at 10:58 am

#121030

I have a need for an SSIS package that will look for a specific record in a table and if the record is found processing would successfully end. If the record is not found the routine would go to "sleep" for a certain amount of time, wake up and try again. Any help is appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Tim Mitchell SSCoach Points: 15681 More actions · Answer 1

Not sure of how you could do this natively in SSIS, however you could probably get creative and invent a solution. You can schedule a job to run at given intervals that would check for the record and process if it exists; you could write the status to a database table and short circuit the process if a match was previously found.

Hope this helps... more information about your environment might be helpful to nail this thing down.

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

MikeTomkies Hall of Fame Points: 3185 More actions · Answer 2

You might want to look at the WMI event watcher task, this allows you to write WQL queries to watch for events. Example you can watch for an event happening which would be a modification of a file. ther are loads of different classes in wmi of paticular intrest are the CIM and Win32. To look at different classes and how to query them i suggest http://www.freedownloadscenter.com/Network_and_Internet/Network_Management_Tools/WMI_Explorer_Download.html

I havent done much with WMI other than watching a directory for a certain file to be present so cannot provide more information than that but thought it is worth a mention as may help you finding a solution.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

It is easy to branch on whether a record exists or not. Use aa SQL command to return a field from the record and then set your data flow to go one direction if the record exists and another if it does not. If that was not specific enough, let me know and I can give you more details.

I don't think there is a wait component in SSIS though. You could write one, or use a script componet that does nothing but call the Sleep method. Then, just make your control flow loop back to the SQL command.

My opinion (which may not be worth much) is that this type of looping and waiting is bad programmng practice. The suggestion to use the SQL agent and check on an interval is probably the best "quick" solution to this. For making this more event driven, I will give you two suggestions.

The first is to use a regular trigger on the table that gets the record. Have the trigger enable a job in the job agent that runs every second and runs your SSIS package. Then, have the job disable itself. However, as I am writing this, I think this is pretty ugly as well.

Finally, you could create an asynchronous trigger with the service broker that calls a web service that then runs your SSIS package. Then, the event of the record being inserted will truly run the package.

If you have more specifics on why you are waiting for the record to appear, I can probably give you some better suggestions.

Tim Riley-437154 SSC-Addicted Points: 469 More actions · Answer 4

Thanks for the suggestions. Michael, I agree this whole thing is not best practice, but when you try to integrate from several competing vendors, you do what you must, and quietly go bonkers .

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 5

I understand. Although I have trouble doing anything quietly these days.