SSIS Design Deep Dive Sql Master Class

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:

#PatternExample
1The Full LoadTruncateAndLoad.dtsx
2SCD WizardDimCurrency
3Master Reference LoadLoadMD.dtsx
4Basic Lookup SCD ILoadGeography.dtsx
5HashLoadEmployee.dtsx
6Hash Lookup SCD IILoadProduct.dtsx
7Staged UpdateLoadProduct_stage_updates.dtsx
8Pre-staged KeysLoadFactResellerSales.dtsx
9Merge DeltaLoadFactResellerSales_Merge.dtsx
10SQL DeltaLoadFactResellerSales_SQL.dtsx
11Partial LookupLoadFactResellerSales_partial.dtsx
12Inline Infer ScriptLoadFactResellerSales_inline.dtsx
13Batch InferLoadFactResellerSales_batch.dtsx
14Parent/Child LoadLoadEmployee.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

Leave a Reply