SQL Centre of Excellence

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
blog comments powered by Disqus

Page List

Page List