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