Hi there, How to let the SSIS package run when the database table ( SQl Server ) has been updated or changed. I think a trigger does that when a table changes happen, but how to implement in SSIS package. Here is the task im doing : 1, DataFlow Task -> Oledb Source -> In Sql Command ( write a select query ) 2, Excel Destination 3, to run the package a DB2 request to run the JOBTac to kick the Batch file for Package. Any Help how i need to trigger SSIS package when table update happens or can i do anything in SSIS package itself ? !! Thanks Regards New Bee in Database :)

Suggestion #1: Don't base the execution of an SSIS package on a trigger! Here's why: If the package fails, the trigger rolls back. No amount of BEGIN TRY/CATCH logic will stop that. What you need is a way to perform the SSIS launch 'asynchronously'. In other words, when the trigger fires, it takes 'some action' to start the SSIS job, then IMMEDIATLY commits the transaction and returns control back to the system or application that initiated the change. That 'some action' will then go and do its thing, but not have any affect on the trigger or transaction. How is this done, you ask? Two options: Create a SQL Agent job that runs the package, and have the trigger start the job. OR Set up a Service Broker dialog and stored procedures and queues to manage it.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Hello Dotnet.Explorer: It is not a "healthy" approach so to speak to do so, but the way I see it can be done would be creating your package and setting it to run as a SQL Agent job. Then, you can trigger it using SP_START_JOB system stored procedure in your On Update (not sure what you mean under "changed") trigger. Do you need any further help? Arthur My Blog

Oh Thanks Arthur !! "Changed" is what i mean is when Table is Updated !!

Oh that was nice suggestion Todd, yes as you suggest i meight try with SQL Agent job.Thanks

I would go a little further than Arthur and Todd C. I wouldn't use triggers to actually start a job - I'd just use them to mark the table as "dirty". Create an agent job that runs your package every n minutes. Create a "dirty table" table that contains table names, the date/time they were last changed, and the date/time the SSIS job last started AND completed. Make your trigger update the "last changed" column in the "dirty table". Your package should read the "dirty table", compare the change time versus the time the last job started. If the job was started before the last change, continue. Now compare the recorded completion time to the start time. If the job started after the completion time - then the package is still running. If it's not, then run the rest of the package. Talk to me now on

I would suggest you to use the method given BY TODD MCdermid only if the table is updated frequently ( Transaction table ). If the table is not updated frequently (few updates per days ) i would suggest to go by Arthur . Cheers , Varun