How to increase SSIS performance

Hello again,

I'll just throw my question: how could I increase SSIS-performance?

I have a really heavy job with thousands of records my base selection, then I perform some lookups (I replaced most of them by sql) and derived columns (again, I replaced as much as possible by sql). Finally, after a slowly changing dimension task, I do update/insert on a given table. Is there a trick to speed up lookups and inserts (something like manipulating the buffer sizes - just asking).
Fact is that I replaced a script task by pure sql-joins and gained 6 of the 12 hours this job took.

Any ideas?

Greets,
Tom

Tom De Cort wrote:

Hello again,

I'll just throw my question: how could I increase SSIS-performance?

I have a really heavy job with thousands of records my base selection, then I perform some lookups (I replaced most of them by sql) and derived columns (again, I replaced as much as possible by sql). Finally, after a slowly changing dimension task, I do update/insert on a given table. Is there a trick to speed up lookups and inserts (something like manipulating the buffer sizes - just asking).
Fact is that I replaced a script task by pure sql-joins and gained 6 of the 12 hours this job took.

Any ideas?

Greets,
Tom

Speeding up by replacing the script task doesnt surprise me. If you have a high-performant database engine at your disposal, why not use it!

Some links for you that may help:

Whitepaper on Performance Tuning Techniques
http://blogs.conchango.com/jamiethomson/archive/2006/04/09/3594.aspx

Donald Farmer's Technet webcast (highly recommended)
http://blogs.conchango.com/jamiethomson/archive/2006/06/14/4076.aspx

-Jamie

|||

Hi Jamie,

Great, I've read the whitepaper and discovered a lot!

Greets,
-Tom