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.