SSIS Best Practices – Performance


We’ve been slowly creating a list of best practices. Simon asked me about these at the PDC a couple of weeks back. It’s time to share them out not just to make folks aware of them but also to solicit other goodness you’d like us to include in the official list.


First off, Performance:  



  1. [Oledb Source, Lookup, Fuzzy Lookup]: Remove columns which’re not used downstream. 
  2. [Oledb Source, Lookup, Fuzzy Lookup]: Use a select statement when fetching data from a view instead of using the table drop down.
  3. [OLEDB Command Destination]: for large number of rows, this component might not scale because it sends one SQL statement per row. Persist data to a temporary table and use set based SQL.
  4. [SCD]: for large number of rows that will not exist in the dimension table, consider using a lookup before the SCD.
  5. [OLE DB Destination]: if the server is local, consider using SQL Server destination.

  6. [Flat file source]: turn on fast parse for columns that have types that fast parse understands. Look at BOL for more info on Fast Parse. 
  7. [Conditional Split]: For transforms that use conditions based on columns coming straight from OLEDB or ADO.Net sources, consider using the native filtering from the relational database to remove rows before they come in to the pipeline.
  8. [Flat file Source]: For transforms that have all columns with the default column type and size, consider using the ‘Suggest Type’ functionality.
  9. [OLEDB Destination]: Review the fast load settings on the destination adapter.
  10. [SQL Server]: for OLEDB Destinations/SQL Server Destinations that perform bulk insert into a database, verify that the logging mode is appropriate for performance. Refer to BOL for more info.

 


Comments (7)

  1. ybhalerao says:

    Hi Ashvini,

    Did you get to build this SSIS Best Practices? If yes, where do I get it from

    Thanks

    Yogesh

  2. roy ashbrook says:

    I’m wanting to compile a basic best practices list for SSIS. I don’t really need to get into

  3. Beberapa waktu yang lalu saya melakukan kesalahan besar dengan project SSIS yang saya kerjakan. Yang

  4. Nalin D. Jayasuriya says:

    When using the Merge transformation, SSIS requires the data-stream to be already sorted. Instead of using the Sort transformation just before the Merge transformation, when possible use the ORDER BY clause in the data source and set the 'Sort Key Position' value (via advanced-options) to indicate the sort order. Now the Sort transformation is not required before the Merge. This helps because the Sort transformation is expensive (even if the source records are already in order) – the sorting is done in memory.

  5. Hi,

    I have to delete SSIS package version. Is that possible.

    Note: I dont have access to msdb database

  6. Nagaraju says:

    Dynamically create data flow dest table if it does not exist?

    (suppose i have "Table_1" when i am execute the package i want to create destination table "Table_2" again execute the package "Table_3")

    Please any one give me answer……………..