question

apple avatar image
apple asked

etl optimization

I have an old etl package which runs for about 2o hrs .I need to reduce the time and improve performance . Can you please share your thoughts on the best way to start this I am new and need to get this done quickly. thanks a lot
ssisetl
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
Is there anything you could tell us about the package? Data sources, transformations, types of tasks, data flows, etc? Without any details, the answer comes down to "Faster computer, more RAM, faster disk drives" or "Don't pull so much data"
5 Likes 5 ·
JohnM avatar image JohnM commented ·
All of Ken's comments are correct. You could also look at articles such as this one: http://technet.microsoft.com/en-us/library/cc966529.aspx
1 Like 1 ·

1 Answer

nidheesh.r.pillai avatar image
nidheesh.r.pillai answered
Hello, Few tips in addition to Ken's and John's messages: Do check for fully blocking transformations if they are present (Example the Sort), you could remove them by sorting your data at the query end itself (ORDER BY) rather than bringing them over to SSIS. Additionally check your joins, unions, indexes on tables etc. Though you mention it as an old ETL package I assume it to be not older than the 2005 DTSX version. Incase you performed an upgrade from 2000 or lower DTS to 2005 or higher DTSX, do open your package once and check if the upgrade happened successfully, as owing to new features present, it might not always be the case that the upgrade would be 100% in accordance to requirements
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.