SSIS Package Inside Trigger keeps running endlessly
-
Hi,
Following some requirements, that came in yesterday, I have been assigned a task of executing an SSIS package from inside a Trigger.
The SSIS Package performs an Incremental Load. The Trigger(which is for Insert) just executes the package using the DTEXEC.exe.
Following is the code for the Trigger:
ALTER Trigger Test_Trigger On SC_Data
FOR Insert
As
Begin
EXEC xp_cmdshell 'dtexec /FILE "C:\Documents and
Settings\Administrator\Desktop\Test\Test\Test1.dtsx" /decrypt saa'
End
The problem is that the package executes fine otherwise....but the Trigger keeps executing forever. The Insert operation is also not performed. It seems like the Insert operation and the Execute Package operation are blocking eachother.
After reading for sometime about this on the web...I decided to edit the Data Flow Task to use a Select Query with (NOLOCk). But still it doesn't work and the trigger keeps executing.
The Trigger works if I create a job of the SSIS package and call that JOB from the Trigger. But the requirement is to call the package directly not the JOB.
Has anyone here had such a problem before??....Is there a way to execute the package(not JOB) from inside the Trigger???....
Any help would be very much appreciated.
-
If it works fine when the trigger calls a job that calls the package, then I would bet it is an issue with the account being used to run the package. Is the package using trusted connections? Try logging into the server using the service account running SQL Server and running the package that way to see if it completes, or changing the trigger to change your security context before running the SSIS package.
-
BTW, I'd just like to add that I think calling an SSIS package from an insert trigger is a very bad idea. :unsure:
-
If the trigger is running forever, that means the package isn't returning control to the trigger. To find out where in the package this is hanging up, set up logging in the package and have it write to a text file. If it never starts writing (most likely scenario), then the package isn't starting, and it's a control issue probably caused by permissions. If the package finishes running per the log, but the trigger doesn't, then it's a problem with how you're calling xp_cmdshell and you'll need to dig into that. Most likely though, either the package never starts, or it's waiting for the trigger or the transaction firing the trigger, to do something.
I agree with the prior post that says putting an SSIS package call inside a trigger is a really, really bad idea. Locks, escalations, asynchronous thread control, and a dozen other reasons. I can't imagine a situation where inserting a row in a table depends on importing a file before you commit the insert. I'd do a frequently-running job that checks for new rows in the table and runs them in a batch.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Thanks a lot for the replies.
I know that this is a bad idea, but some people above me disagree.....anyways if this doesn't work then they'll learn I guess.
I am pretty sure now that its a Locking issue. I think the Trigger acquires a Lock on the table which is not released and the SSIS package keeps on waiting for escalation of the Lock in order to start working.
I executed "sp_who2" while the trigger is working and it shows that the session of the Insert Statement keeps Running......but the session of the SSIS Package gets "SUSPENDED".
If it works fine when the trigger calls a job that calls the package, then I would bet it is an issue with the account being used to run the package. Is the package using trusted connections? Try logging into the server using the service account running SQL Server and running the package that way to see if it completes, or changing the trigger to change your security context before running the SSIS package.
I'm don't think its an issue with the account because if I execute the package using DTEXEC outside the Trigger using the same account then it executes fine.
Thus, it leaves me with the Locking scenario. Is there a way I can handle this scenario??....May be by using some Query Hints or some other way.
-
+1 that this is almost certainly a terrible idea.
If the trigger has a lock on the table and you have a source(/destination?) component reading the same table it could be the metadata check that fails to achieve the required lock. In the pre-execute phase (I think) of a DFT, SSIS will verify the metadata of sources and I believe this requires a schema lock.
You can set ValidateExternalMetadata to false on any source components to avoid this check.
Are you using transactions?
Maybe the package executes fine through a job because the trigger just initiates the job and then completes and releases the lock, whereas if the package is called directly the trigger waits for control to be returned?
Just some thoughts.
HTH,
Sam
-
Hi Sam,
I changed the properties of my package as you suggested. But, it didn't improve anything. I am adding an image which shows the various current sessions on the table and the it shows that the session of the SSIS package is being blocked by the session of the Insert.
The image might provide a better view of the Blocking problem.
SPID 66 is the Insert Query and SPID 71 is the SSIS task which is being blocked by SPID 66 and gets suspended.
-
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?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Did your "above you" insisted that you should use trigger to execute XP_CMDSHELL? Or they just told you that SSIS should start as soon as records inserted?
I would not definitely, under any circumstances, call xp_cmdsheel from a trigger - just NEVER.
You may try to create scheduled task and use msdb.dbo.sp_start_job to start it from the trigger, that may be a bit better...
Also you can use Service Broker.
-
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.
-
Eugene Elutin (10/18/2012)
Did your "above you" insisted that you should use trigger to execute XP_CMDSHELL? Or they just told you that SSIS should start as soon as records inserted?I would not definitely, under any circumstances, call xp_cmdsheel from a trigger - just NEVER.
You may try to create scheduled task and use msdb.dbo.sp_start_job to start it from the trigger, that may be a bit better...
Also you can use Service Broker.
Hi Eugene,
The "above me" above me asked me to call the SSIS package from a Trigger without making the package a part of a job. So I was pretty much left with Xp_cmdshell. Any other suggestions of doing it are very welcome. Thanks for ur time.
-
vinu512 (10/18/2012)
Eugene Elutin (10/18/2012)
Did your "above you" insisted that you should use trigger to execute XP_CMDSHELL? Or they just told you that SSIS should start as soon as records inserted?I would not definitely, under any circumstances, call xp_cmdsheel from a trigger - just NEVER.
You may try to create scheduled task and use msdb.dbo.sp_start_job to start it from the trigger, that may be a bit better...
Also you can use Service Broker.
Hi Eugene,
The "above me" above me asked me to call the SSIS package from a Trigger without making the package a part of a job. So I was pretty much left with Xp_cmdshell. Any other suggestions of doing it are very welcome. Thanks for ur time.
You could do a CLR trigger, if the server/database allows that. It's an option, but I don't know that it's actually any better.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
I really don't know a lot about CLR. And I think if I start now then it would be really time consuming. So, I'd rather stick to solving this problem the way it is for right now. I'll give CLR Triggers some time after I'm finished with the task at hand.
So, please guys.....any other suggestions of how I could get this to work??
-
vinu512 (10/19/2012)
I really don't know a lot about CLR. And I think if I start now then it would be really time consuming. So, I'd rather stick to solving this problem the way it is for right now. I'll give CLR Triggers some time after I'm finished with the task at hand.So, please guys.....any other suggestions of how I could get this to work??
Add logging to the SSIS package. See where it gets to, or if it even starts, when fired from the trigger. When you find that out, you'll have a better idea of what needs to be fixed/modified.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply