GSquared (10/18/2012)
What is the SSIS package accessing the same table for? To read values out of the inserted row?If it's to read values out of the row, then what you need to do is make those variables in the SSIS package, and pass them in using the /Set options in dtexec, and make it so it doesn't have to read the table.
Will that work for you?
The Package is not only Selecting the Inserted values from the Source Table, but also Updating a column in the Source Table to show that the data has been moved from the Source table to the Destination Table. It is part of an Incremental Load from the source procedure to the Destination Procedure where only that data which has not already been exported to the destination is exported to the Destination.
I think I might need to use an Asynchronous Trigger using Service Broker to avoid the blocking issue. I have seen a few examples of the same on the web but they are all update triggers that update the table. I have tried an example which worked fine. But, I am finding it difficult to customize the Trigger for executing an SSIS Package.
How should I go about this task??.....Any help would be really appreciated....Thanks a lot guys for all your time.