1

i have a package with a data flow task that has an OLE DB source and OLE DB Destination. In the OLE DB Source I have a SQL Command with Two Consequent Inserts of a Target table with help of Two Joins from a Table from different Database and other being same

My query is returning over 3 million records and every time my package gets to this task it hangs, so the query query in the OLE DB Source executes but when it's about to insert into the OLE DB destination table it hangs at row 9000 something and the task stays yellow and stays at the number of rows count forever not inserting anything in my destination table even after letting it run for a couple of hours.

Now if I were to take this query and run it in management studio it finishes in less than a minute and rows are inserted in that destination table so it's not like the query is not optimized to perform the record retrieval and then insert

user2701912
  • 41
  • 9
  • My first thought is that SSIS is probably baulking at the data levels. Can you replace the data flow with a SQL Task and run the insert that way? – Preet Sangha Aug 21 '13 at 02:22
  • I've been able to load many millions of rows of data without this behaviour. Many things could be occurring in your destination database. It could be growing (data or log) as you attempt to insert this data. It could be blocked by other activity in the system. If you didn't check the "Lock Table" button, it might be that the row/page lock it started with escalated to a table lock but it hasn't been able to get an exclusive lock due to activity. It could be as simple as you have the default (2005/2008) Data Access Mode of "Table or View" selected. If so, change to "Table or View - fast load" – billinkc Aug 21 '13 at 02:43
  • If you want concrete help, please update your question by clicking Edit. Add in SQL Server version, a screen shot of your data flow, the OLE DB Destination. Fire off the package and either work with your DBA or install [sp_whoisactive](http://sqlblog.com/files/folders/beta/tags/who+is+active/default.aspx) on the target server and start mashing away and look for blocking. Remove the OLE DB Destination and just route the rows to a Row Count or Derived Column - does that run to completion and how long does it take? – billinkc Aug 21 '13 at 02:46
  • I have used an Execute SQL Task to include my insert and update statements and it took me less than 1 minute to complete the same process rather in a data flow Task – user2701912 Aug 22 '13 at 01:02
  • Thanks billinkc for the Suggestion, I will try it out and will update – user2701912 Aug 22 '13 at 01:11

0 Answers0