I have a SSIS package with a Data Flow Task which inserts new rows into a table, lets call it Table1. The new rows are gathered from a view, lets call it View1.
View1 is updated every day so it contains new rows to be inserted into Table1. It is also set up to not include rows already present in Table1. So View1 will always contain new rows to be inserted into Table1.
The Data Flow Task contans an OLE DB Source which is View1, which runs through a Script Component. This is because I need to genereate an id for the new row.
The script component is connected to an OLE DB Destination which is Table1.
The SSIS package is set to run as a SQL job every night at 02.00.
When the view returns less than 30000 rows it runs fine and checking the job history in SQL Server Agent for this job, it shows that it has runned successfully.
When the views returns more than 30000 rows, the data flow task is "hanging" for several hours and locking the database. Checking the job history in SQL Server Agent there is no record that the job has runned, either successfully or failed. Meaning that it's still running.
So everytime the view returns more than 30000 rows, I have to configure the view to return 30000 rows and run the SSIS package manually.
I was wondering if there's any way to run the Data Flow Task in a "foreach" state?
Meaning that it takes the first 30000 rows from the view and inserts them, then if there are any more, takes the next 30000 and so on.
Or is there any other way to optimize the insert?