Newbies guide to SSIS in SQL Server 2008 (R2)

I am after reading (preferably online) resources to guide newbies in the creation of SSIS jobs in Microsoft SQL Server 2008 (esp the R2 version).

For context we are trying to create a simple ETL job extracting from one SQL server database's contents, doing some parsing of XML row data in the source dataset and using that parsed data开发者_高级运维 to write/update a series of target tables in another database.

Any pearls of wisdom would be much appreciated.

There are tons of resources out there:

• SQLIS web site - site dedicated to nothing but SSIS
• SSIS articles and how-to's at Simple-Talk
• Pluralsight SSIS online video course (not free - but worth the money anytime!)

Microsoft also has some guides up on msdn: SSIS Developer's Guide

Pearl of wisdom 1 - SSIS packages should be in source control like all other code. This could save you a world of hurt later.

Pearl of Wisdom 2 - Don't try to use a stored proc or even SQL code with a temp table as the source for a data flow. You can however use a CTE.

Pearl of Wisdom 3 - Staging/history tables are your friend, they can be very useful when you need to research something that went wrong. If I'm doing an import, I always havea two staging tables, one for the raw data and one for the cleaned data. Now I can easily see how that bad data ended up in the database, wheter it was from bad data sent unexpectedly ora bug in the process. Same thing with exports, save all your exported data to a history table (By batch) then you can research easily when problems occur.

Pearl of wisdom 4 - think about how you are going to deal with data mismatches between the systems or bad data. Think about how someone will be able to tell what the problem was when the SSIS package fails. Think how are you going to maintain this package and how will you research errors. Don't forget to set up logging and error handling.

Pearl of Wisdom 5 - get the Konesans Trash Destination component (http://www.konesans.com/products/trash-destination.aspx). It's free and it is better to be able to send the data flow to trash while developing (So you can see the data in a data viewer) than to inadvertantly mess up large amounts of your dev database due to a bug.