Share via

SSIS Best Practices - Performance

  • Article

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

  • Anonymous
    September 05, 2006
    Hi Ashvini,

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

    Thanks
    Yogesh
  • Anonymous
    May 04, 2007
    I’m wanting to compile a basic best practices list for SSIS. I don’t really need to get into
  • Anonymous
    January 21, 2009
    PingBack from http://www.keyongtech.com/2091007-best-practices
  • Anonymous
    March 18, 2009
    Beberapa waktu yang lalu saya melakukan kesalahan besar dengan project SSIS yang saya kerjakan. Yang
  • Anonymous
    November 04, 2010
    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.
  • Anonymous
    April 10, 2012
    Hi,I have to delete SSIS package version. Is that possible.Note: I dont have access to msdb database
  • Anonymous
    December 10, 2012
    The comment has been removed