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:
[Oledb Source, Lookup, Fuzzy Lookup]: Remove columns which’re not used downstream.
[Oledb Source, Lookup, Fuzzy Lookup]: Use a select statement when fetching data from a view instead of using the table drop down.
[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.
[SCD]: for large number of rows that will not exist in the dimension table, consider using a lookup before the SCD.
[OLE DB Destination]: if the server is local, consider using SQL Server destination.
[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.
[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.
[Flat file Source]: For transforms that have all columns with the default column type and size, consider using the ‘Suggest Type’ functionality.
[OLEDB Destination]: Review the fast load settings on the destination adapter.
[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.