Thanks to everyone who came along to the SSIS SQL Masterclass last Thurs (31/05/2011). The SSIS Design patterns in particular seemed to be very popular, and we have lots of requests for the sample packages I used in the demos.
I’ve attached the source code. You’ll need to play with it to get it to work as I haven’t added configuration files – that’s another session!
Some tips on getting it to work:
a) You’ll need to download the source database “AdventureWorks” from codeplex
b) The sql server name was “katmai1” you can add an alias to get it to work in sql configuration management
c) Inside the zip file are two sql scripts to create the extra required databases: [AdventureWorksStaging] and [AdventureWorksDWH]
d) We use two third party components as examples available from these links:
http://ssismhash.codeplex.com/
http://dimensionmergescd.codeplex.com
Here is a list of the design patterns and which samples demonstrate the pattern:
| # | Pattern | Example |
| 1 | The Full Load | TruncateAndLoad.dtsx |
| 2 | SCD Wizard | DimCurrency |
| 3 | Master Reference Load | LoadMD.dtsx |
| 4 | Basic Lookup SCD I | LoadGeography.dtsx |
| 5 | Hash | LoadEmployee.dtsx |
| 6 | Hash Lookup SCD II | LoadProduct.dtsx |
| 7 | Staged Update | LoadProduct_stage_updates.dtsx |
| 8 | Pre-staged Keys | LoadFactResellerSales.dtsx |
| 9 | Merge Delta | LoadFactResellerSales_Merge.dtsx |
| 10 | SQL Delta | LoadFactResellerSales_SQL.dtsx |
| 11 | Partial Lookup | LoadFactResellerSales_partial.dtsx |
| 12 | Inline Infer Script | LoadFactResellerSales_inline.dtsx |
| 13 | Batch Infer | LoadFactResellerSales_batch.dtsx |
| 14 | Parent/Child Load | LoadEmployee.dtsx |
Drop me an email or post comments to the blog if you can’t get the samples to work or have any questions/comments.
Happy ETL’ing,
Bob
Download File – AdventureWorksETL_Sample
