optimizing SSIS 2008 package

2
votes

We are dealing with large data in SQL Server database,
Now package execution taking hours to execute.
Is there any way/techniques to optimize execution time of the package?
I know about SSIS Parallelism.
Is there any other best ways?

Edit:
we are dealing with more than 2 crores of records.
SQL server database is the data-source as well as destination (in the same server), And I used transformations like, Derived column, conditional split, multicast, data conversion, lookup, OLEDB command, OLEDB destination, OLEDB source, Merge, merge join, sorter, aggregate transformations.

4
sql-server-2008ssisexecution-time
As your question stands, it's rather vague and cannot be answered. What does "large data" mean to you? What are your bottlenecks? Where is the package spending its time? What does your data flow look like? What are your data types? - billinkc
To add to Bill's questions... where are the data source(s)? Where are the destination(s)? What kind of transformations are you performing? - Todd McDermid

4 Answers

1
votes

Look at any task that requires you to sort the data, those are typically the ones that slow things down. For instance, instead of using a merge join use a datasource that is a query with a join.

1
votes

A very good article on the implications of using different types of transformations in SSIS: Non-Blocking, Semi-Blocking and Fully-Blocking components in SSIS

Basically if you use sort or aggregate in your package, you will have a full stop at those components until every row is consumed by them before any row is passed to the next component. Use these only if you have no other choice in your dataflow.

Semi-blocking components like Merge and Merge Join create a new buffer for row being passed through, and so incur an overhead while the buffer is created in memory. Use this sparingly in your code.

Non-Blocking will allow you to maximize your throughput

0
votes

Avoid semi blocking components.

Use SQL Task component for Merge,sorter,Aggregate functions

Set 10000 for Row per batch and commit size in OLE DB Destination .

Set Data Access Mode (Table or View Fast Load) in OLE DB Destination.