SSIS design consideration

The following is my experience with SSIS building:

  • Make sure you understand the 3 modes of Lookup.  General, I would only do full cache if the lookup table only return less than 200 rows
  • Never use the table or view select unless you really need every field on that table or view.
    • select * from a data source will break in the future if more columns are added to the table
    • select * in a lookup brings back unnecessary fields and impact performance
  • Never use "Sort" in SSIS unless it's absolutely necessary.  Sort in database first.
    • Merge join requires sorted input; you can do order by in your select, then configure the output to specify the "sorted" column.
  • Transaction matters.  If you only need to read data, use the snapshot option to ensure fastest reading and without blocking the table.
  • Don't use config file unless you are sharing values across multiple packages.  Not using config files makes the packages more dynamic as values can be set though SQL Job or xp_cmdshell
  • Now that I've mentioned it, don't use xp_cmdshell to start package unless you have to.  By properly storing variables in well constructed tables should eliminate the need to dynamically pass in values.
  • use variable value expression instead of using script task to set values
  • Just like in T-SQL, don't do a select with 15 tables joined together.  Do an insert of the key first, then update the destination table.
  • Learn C# (well, for 2008 or later, otherwise, learn VB as well)

Enjoy your SSIS project!

[Update: 2012-03-28]

This is from MSDN; didn't make much sense to me when I first read it without having done SSIS, but it does now.  LOL. 

Print | posted on Tuesday, October 25, 2011 10:16 PM


No comments posted yet.

Your comment:


Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski