The problem is that the input data recently changed and duplicate records were in the data flow going to a table that had a primary key to prevent this. Instead of the task failing it just waits.
I have a dataflow task which has a Script Transformation Editor task flowing to a derived column task flowing to a OLE DB Destination task.
I tried a simple test reading from a table with duplicate records to a table with a primary key to stop it and it fails instead of waiting. The project is now in SQL 2008 and it was converted from SQL 2005. The test was written in SQL 2008.
Other fatal errors are trapped and handled but in this case the task just sits there until it is stoppped. In the mean time I have worked on the input data to stop the duplicate records.
Any help or ideas would be greatly appreciated
My first suspicion would be that the SSIS is waiting on the database engine. Can you see from SSMS what is going on - a long transaction, blocking, locking etc? If you dont already have it then get sp_whoisactive from Adam Machanic and use it to see what is occurring.
answered Jul 24, 2012 at 09:00 AM