Running package slow

Question

Hi, I have 50 million rows in source(oracle) and want to move to destination (sql server). I have created a package with ole db oracle src to sql destination. I'm doing "foreach loop" and sending month and year in where condition. For some reason, if job fails in middle, how do I put checkpoint or commit or no commit? Also, to improve performance on ole db destination editor, i have added rows per batch = 50000 and Maximum insert commit size = 100000. I have added audit sql tasks. To trigger that, it is taking hours.

I want to understand why package taking so much time to start? If I run package first time, will it take time? If I run second time, will it go fast? Why is like that so? How to make run package fast without any delay in starting?

Answer 1

Hi @dinesh ,

1. Please use checkpoint in the properties of ssis package.
   The following link will be helpful:
   Restart Packages by Using Checkpoints
2. To make Integration Services performance much better, please refer to following steps:
   a.Please perform tasks in parallel if possible in Control Flow;
   b.Change the Data Access Mode to SQL Command in Oracle Source;
   c.Avoid blocking transformations;
   d.Change the Data Access Mode to Table or view - fast load or Table name or view name variable - fast load in the SQL Destination.
   The following links will be helpful:
   SQL Server Integration Services SSIS Performance Tuning Techniques
   Integration Services Performance Best Practices – Data Flow Optimization

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues in October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet.