December 21, 2015 at 5:29 am
I'm trying to implement a transaction on a package in SS 2008R2 , but the package hangs on the first task. I've searched google where a lot of different possible causes concerning blocking were mentioned, so to exclude as many causes as possible, I made a new package with just two Execute SQL tasks. The first one updates a row in one table and the other task updates a row in another table. I've left the transactionOption of both task on Supported and set that of the package to Required.
When I run the package, the first task turns yellow, and that's it! Nothing else happens now.
On the progress tab I can see both tasks are validated. It also says Information: Starting distributed transaction for this container.
I've read a lot about blocking by SSIS itself, which can be observed by running sp_who2 and noting that there's a SPID with value -2. That is not the case here!
What am I doing wrong, please help.
December 21, 2015 at 6:27 am
Check to see that the MSDTC service is started on the servers that the SSIS package is running on/against.
Does it fail after a period of time? If so what error does it give?
December 21, 2015 at 6:33 am
MSDTC service is running. I don't get any error. The first task just stay's yellow (running) and won't continue after validation.
December 21, 2015 at 8:55 am
So you aren't seeing any locks when using sp_who2 when the package is hanging?
If you change the transaction option for the package to be not Supported does the package run successful?
December 21, 2015 at 10:41 am
No, there are no locks while the package is in running mode. When I change the package tranOption to NotSupported both tasks run as expected, but obviously there would be no roll-back in case of failure of the second task.
December 22, 2015 at 1:40 am
Do you have any data flow tasks in the package?
December 22, 2015 at 1:52 am
No, none.
I do need to rectify something: there actually are locks while running the package, but they're all S or IS locks.
December 22, 2015 at 3:41 am
Do you get any output from the below while the ssis package is hanging?
SELECT * FROM sys.dm_exec_requests where blocking_session_id <>0
If the transactions are implemented manually does it work? (Add an execute sql task at the beginning with a begin tran statement, add one at the end with a commit tran statement and execute sql task with the rollback statement to connect the relevant failure precedents to.)
December 22, 2015 at 4:56 am
Commenting so I can follow the responses, we are getting the same intermittently on a server for the last 2 weeks.
Package starts, validates then does nothing on the first SQL task in the package. No connections showing at all from the package to any of the SQL servers listed as connection managers. Verified checking the servers for SPIDS using sp_who2, querying DMV's, sysprocesses, using sp_whoisactive etc.
Occasionally the issue happens only when the package is executed from a SQL Agent job running under the context of the SQL Agent Service account, when ran interactively in BIDS as the same account or as another administrative user the same happens. However if we stop the package and restart it miraculously works.
We think it may be attributed to a group policy rolled out by our system admins, so its trial and error at the moment while we try an isolate the policy changes.
MCITP SQL 2005, MCSA SQL 2012
December 22, 2015 at 5:01 am
So I just returned to my testing desktop, where I left the package running. However, after 8 minutes the first task and the package failed after all.
Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.
I'm now trying to find out what it is that causes this.
December 22, 2015 at 5:07 am
Unfortunately that doesn't happen with ours, we tried leaving one package for over 10 hours and it never moved on. The particular package we tested with has not failed once in over 2 years and has not been changed either so its a strange one.
MCITP SQL 2005, MCSA SQL 2012
December 22, 2015 at 5:14 am
It will be to do with MSDTC.
Check that the MSDTC service is running on all machines involved in the transaction i.e. if you are running the package from your PC is the MSDTC service running on both your PC and the servers involved in the package.
Look at the DTC properties - run dcomcnfg.exe and drill down Component Services - on the security tab check that Network DTC access is ticked and that allow inbound or allow outbound is ticked depending on the requirements.
December 22, 2015 at 5:36 am
Thanks, I checked MSTSC on all servers involved was running but not the config, wil post back if this is the answer.
MCITP SQL 2005, MCSA SQL 2012
December 22, 2015 at 5:49 am
And meanwhile I just deployed the package to our production server. Here it did run just fine. So apparently we only have this problem on our test server. Will check MSDTC service and Component Services on our test server, but I have to be honest, since the package works on production, this problem now has a much lesser priority. I'm not sure when I'm able to report back.
Thanks all for your time anyway!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply