March 28, 2010 at 9:23 am
My scenario is something different but it might be helpful for others. I have a script task which is bulk inserting the data from sql server to mysql, it was working fine but recently it was hangs due to unknown reason. I was transferring 200,000 records so i was confuse that it may be due to memory leak or anything. After hit and trial i just disable my antivirus scanner and all the hanging gone. Seems that antivirus was blocking some of the processes without my knowledge which was the reason of hangs.
Hope so it helps someone.
October 5, 2010 at 8:59 am
SQL Server 2008 SP1
Package and all non relevant tasks = not supported
Container = Required
Two data flow tasks (one loads parent, the other loads the child, but uses a lookup to parent table to get PK's) = Supported
Works on Dev, not on staging!
Changes:
Container = Not Supported
Package = Required
Works!!!
August 16, 2011 at 8:34 am
Hi, I think I have a similar problem and would be happy for any hint about what I could try next.
I'm using SSIS 2008 R2 with the Microsoft OLE DB Provider for DB2 for loading 2.2 million rows
from a DB2-table. The dataflow works fine till I reach 390.000 rows then the data transfer stops
without any (error) message. I waited more then 5 minutes but nothing happens.
Thanks Alex
January 26, 2012 at 8:33 am
I experienced this problem and found a solution, so hopefully this will help others.
Action i was doing; I was importing data from using a query into a table. The destination table was part of the source query. The query was find data that was not in the destination.
The process would hang at different points, for example I could select the TOP 80,000 and it would work a few times, but then fail.
Solution: The process was blocking itself. I added WITH (READUNCOMMITTED) to the source query for the table in the destination and the locking problem was resolved. Hope the helps.
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
April 19, 2012 at 10:40 am
Experienced this behaviour and isolated it down to a dataflow task apparently being 'stuck' after the first batch of records. Tried toggling the transaction configuration to no successful operation...
Ended up setting the DataFlow Destination (an OLE DB Destination in this case) Data access mode to 'Table or view' rather than 'Table or view - fast load'. The DataFlow task now completes. It is slower than you'd expect an equivalant task to complete, but faster than not completing at all!
April 19, 2012 at 11:30 am
Chris, glad you solved your issue. Take a look at the solution I gave above, my guess is that you are experiencing the same blocking that I was.
Solution: The process was blocking itself. I added WITH (READUNCOMMITTED) to the source query for the table in the destination and the locking problem was resolved. Hope the helps.
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
April 19, 2012 at 5:37 pm
You're right, my data flow was essentially mutating! Selecting from tableA not in tableB and inserting the results into tableB. My best educated guess at proximate cause is that since the 'fast load' option tries to grab a table lock, tried to do so with the first batch of record, finds the table in use and waits.
The READUNCOMMITTED table hint works as well. I'm leaning toward sticking with the non-fast load option in my case (ETL package for a data warehouse) as concurrency or speed is not a constraint as much as a consistent audit trail for the data.
January 16, 2013 at 4:24 am
hi people, just adding my findings: I'm using SQL 2008R2 (not SQL 2005 as "omhoge") and when copying my ssis-packages between work and home, and vice versa, sometimes the metadata from OLE DB Source (and OLE DB Destination?) gets out of order. I'm not really sure if I'm using the same SP's on both environments, that could cause my problem. I'm not sure.
I got the same strange result is the same as behaviour the questioner "omhoge" describes (no error but the dataflow hangs, it does stay yellow).
After some investigation on the data (nothing found!) I replaced the insert destination table (by using the new button in the OL DB Destination) because that item didn't turn green. It ran well ... BUT ... after analyzing I saw that some columns where null instead having some data. I then decided to replace the OLE DB Source in the dataflow-task and voila everything went OK and also the data was put through as aspected. 😉
November 3, 2014 at 9:31 am
August 13, 2015 at 9:56 am
I have the same ssis pachage pattern. like one master sequence which calls four other sequences packages, those sequences have chiled pakages.
I have not got into this issue yet because i use mostly stored procedures in the packages and data is also small amount as of now, but it is going to increase like in 100000s or more.. I hope it doesnt get stuck in that case.
May be you can try using stored procs instead of different transformations.
Viewing 10 posts - 46 through 54 (of 54 total)
You must be logged in to reply to this topic. Login to reply