We help IT Professionals succeed at work.

How to optimize Insert in a SSIS Package Data Flow Task?

802 Views
Last Modified: 2013-11-10
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?

Best Regards
Marius
Comment
Watch Question

Consultant, Trainer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi

Thank you for your reply.
I've never used Foreach Loop Container before, so do you have a step by step example on how to do this?

Best Regards
Marius
Reza RadConsultant, Trainer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you for all your help:)

Best Regards
Marius

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.