SSIS -> aggregate and normalize

[codingvista]

Hi

I have 4 databases of different structure that I need to periodically merge into one, normalized database.

All 5 are SQL Server 2005.

The structure of the sources is basically a flat table that holds user data - they're all slightly different but essentially that's the case. I want to be able to get the distinct departments from the user table in the sources and insert that into the departments table in the destination. Then, get all the users and insert them into the user table in the destination - linking to the department table in the destination to give them a department ID. I think I need to use a lookup but have no idea how to use them.

I am completely new to SSIS and have tried to figure out the best way of doing this.

Anyone able to offer me some pointers?

Cheers

w://

 

[NathanGriffiths]

Hi codingvista,

For the example you gave you could first use a Data Flow task to load the departments table, you will need to add some logic in the data flow to see whether a department already exists or is new (and therefore needs to be inserted). There are a couple of ways to accomplish this; a) use the Slowly Changing Dimension task using department name as the business key, or b) you could add another data source to select all the existing departments in the target database and then use a Merge Join task to do a left join on department name. Anything that does not return a match is new, so add a Conditional Split task to send the new rows to the target database Departments table.

To load the Users table, use another Data Flow task and this time put a Lookup task in the data flow to look up the Department associated with each user against the Department table (using Department name as the lookup column and returning the Department Id, assuming your Department table has an Identity column then return that value).

Hope that helps, its kind of hard to explain an SSIS package in words!