CODEWITHSUNDEEP
sql-serverssissql-server-2012ssis-2012
LearnByReading
LearnByReading

Reputation: 1883

SSIS: migrating data from a denormalized table to multiple tables

I'm trying to go from a highly denormalized table (with over 500 columns) to multiple (20+ tables): ALL the smaller tables have the exact same column names as the giant, denormalized table. Is there an automatic/quick way to migrate data from that table to the many tables with just two transformations? Basically, I'm just trying to break down a big table into smaller ones. There is a single key that I can use to link them all up. Thank you in advance!

Upvotes: 0

Views: 102

Answers (2)

BeginnerDBA
BeginnerDBA

Reputation: 26

the closest you get is a Multicast Transformation which duplicates data in the same format as the source. you dont lose efficiency and performance however, given you'll be using only the subset of columns you need and not taking the rest. You'll lose a bit of performance hit because multicast is a fully blocking transformation which means that you'll have to wait for the whole data to arrive before distribution. I use the word distribution because the data is not multiplicated if you don't overlap the columns.

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

No there is nothing automatic. You can do it in two transformations though. The first is the source table and the second is a script destination which looks at each record coming in and decides which destination table to send it to, and executes an insert to that table.

Upvotes: 0

Related Questions