Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSIS Package Inside Trigger keeps running endlessly Expand / Collapse
Author
Message
Posted Tuesday, October 16, 2012 10:43 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:52 PM
Points: 1,126, Visits: 1,590
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.


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1373619
Posted Wednesday, October 17, 2012 11:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 29, 2014 6:27 AM
Points: 1,307, Visits: 2,267
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.
Post #1373961
Posted Wednesday, October 17, 2012 11:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 29, 2014 6:27 AM
Points: 1,307, Visits: 2,267
BTW, I'd just like to add that I think calling an SSIS package from an insert trigger is a very bad idea.
Post #1373963
Posted Wednesday, October 17, 2012 11:22 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1373967
Posted Wednesday, October 17, 2012 9:54 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:52 PM
Points: 1,126, Visits: 1,590
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.


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1374128
Posted Thursday, October 18, 2012 12:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 8:23 PM
Points: 367, Visits: 822
+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
Post #1374162
Posted Thursday, October 18, 2012 3:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:52 PM
Points: 1,126, Visits: 1,590
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.



Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1374226
Posted Thursday, October 18, 2012 6:29 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1374290
Posted Thursday, October 18, 2012 6:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 7:27 AM
Points: 2,836, Visits: 5,067
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.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1374306
Posted Thursday, October 18, 2012 6:55 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:52 PM
Points: 1,126, Visits: 1,590
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.


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1374309
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse