March 3, 2008 at 9:47 am
Apparently SP2 fixes the problem. I haven't had any more hangs since applying SP2!
March 3, 2008 at 11:58 am
That's good news at least.
Unfortunately, we had to put SP2 on hold here, we hit a bug where the jobs stopped running due to a profile issue,
on our DEV box where we tested out the service pack.
Apparently there's a hot fix for that but it's still being tested by the DBA-s.
But at least we have some hope for this issue if it is fixed in SP2.
Thanks E.M.
Skål - jh
May 19, 2008 at 1:01 pm
In the Connection Manager for your SQL Server you must set RetainSameConnection=True. This is what we did in our project, but we could not get it to work for our Oracle destination tables. Worked fine for SQL Server.
May 21, 2008 at 1:05 pm
Yes, we used RetainSameConnection=True in the Connection Manager also, and it worked for the transactions.
We had to stop using DTC altogether.
Thanks all!
Skål - jh
May 25, 2008 at 10:40 am
I've got the same "hanging yellow" symptoms, but none of the solutions have worked. There's a mention of "SP2", but it's not clear to me which app requires SP2. Is it for BIDS? SQL Server 2005? .NET Framework 2.0?
May 26, 2008 at 4:21 pm
Found the problem. I had to delete the relationships in the destination db. There's probably a way around actually deleting the relationships, but I certainly couldn't find it.
May 27, 2008 at 8:40 am
In my case SQL 2005 SP2 fixed the problem.
June 3, 2008 at 9:04 am
You may want to look at SP2 at a cumulative release level 3 or above.
SP2 broke our partitioned cubes.
We're on x64 bit and had experienced the occasional 'hang'. Mostly on our cube build. Partitioning helped.
Greg E
June 5, 2008 at 1:19 am
I'm getting exactly the same problem with the Transaction Option property of a Sequence Container set to required. The package gets stuck on as soon as the Data Flow Task enters it's validation phase. It turns yellow and then just sits there. The irony is that I have a couple of tasks in the same container and if I remove or disable the data flow task, the package completes successfully.
If I enable or add the Data Flow Task again it hangs again. The only way I can get it to work is by disabling transactions. And I need transactions enabled!
HELP!!!
June 13, 2008 at 3:50 am
I have the same issue here.
Set the Sequence Container to required and all the children to supported
(this should - in theory - run everything in one transaction for the container)
No DFT - works fine
Add the DFT - hangs, Logging doesn't even show the DFT being accessed, its just yellow on the control flow tab, with the dataflow tab remaining white.
June 13, 2008 at 4:09 am
Here we go...
I've managed to figure out that if I have an SQL task that executes before a Data Flow task in the Sequence container it hangs. I can have as many SQL tasks following the Data Flow as I please, just not one that executes before. My workaround was to strip out the SQL task by putting it in it's own transaction. Didn't affect my performance too much.
That being said, I've only ever had this problem when a Data Flow follows an SQL task. Every other task seems fine...
Might work for some of you...
Would still like to know why!
June 13, 2008 at 6:28 am
Well the Sequence Container uses distributed transactions.
DTC appeared to be the issue for me and switching to SQL transactions with the "keep same connection" option resolved it.
So I'm still suspicious of the distributed transaction coordinator in this, but don't have a definitive reason why.
Skål - jh
June 20, 2008 at 10:23 am
It sounds to me a lot like the Distributed Transaction Coordinator which is tripping up a lot of people here.
What seems to behappening is that SSIS is using DTC to manage the transactions. When the packages hang you can see in the activity monitor that a connection with a SPID of -2 is blocking the SSIS activity (change filter to show system processes too). This lock will remain in place even after you stop running the SSIS packages. The lock has been obtained by the DTC so you will need to restart the DTC service to free up the lock.
Say you set the TransactionOption base container (package, sequence container, etc.) to "required". This will look for a parent transaction and if one exists, enlist in it. If there isn't an existing parent transaction it will create a new transaction. Let's say, for arguments sake, your first component is a TRUNCATE TABLE sql command, your second is a data source and your third is oledb destination.
1. Truncate table.
2. Get new data.
3. Load table.
This will look up as the DTC will issue a command to SQL Server which will lock the table. The DTC won't issue the release until the package finishes. In the meantime, however, the package want to insert data into the table but it can't because the table has been locked. Catch 22.
Remember, it may have enlisted in parent transaction so if the parent container is doing something that requires a lock that's blocking the child component action then your stuck too.
Solution: You'll have to be careful with your DTC transactions and separate them out where necessary.
Hope this helps.
August 27, 2008 at 1:40 pm
Yes that is exactly my situation... again.
Have a sql task to truncate
then a data flow to populate
put them both in a sequence container with transactions required.
The truncate runs, and the data flow hangs.
Thanks again for the explanation.
Now we need to find a way to prevent it.
How to have the truncate and the data flow with
the security of a transaction without all the
extra steps involved in using the SQL transactions
just to avoid the native DTC that the sequence container uses.
I'd love to be able to use the native DTC support.
If anyone has found that answer,
it would be welcome here.
much appreciated!
Skål - jh
September 11, 2008 at 11:42 am
I had the same problem and found this:
The blocking can be resolved by either changing the ValidateExternalMetadata attribute of the OLE DB Destination or changing the TRUNCATE TABLE of the SQL task into a DELETE statement.
From: http://killspid.blogspot.com/2007/10/ssiss-interaction-with-dtc.html
I changed the destination to not validate external data and it worked for me. Hope this helps you.
Viewing 15 posts - 16 through 30 (of 54 total)
You must be logged in to reply to this topic. Login to reply