2

I'm looking to combine several tables into a single data load table using SSIS. The tables are built by some software that I wrote and are created during a data processing stage. Each of the tables will be in a different format and represent a different load file but all will contain various key fields that will need to be extracted, cleansed and then placed in another table. The various key fields may not have static names (e.g. 'payment' in one table and 'pymnt' in another) so will need to be mapped to the destination table if possible.

As I've not used SSIS a great deal (I've used SQL Server for over 10 years in various dev roles) I was wondering if anyone could give me any pointers in terms of direction or even if SSIS is the right tool for the job ?

I can use either 2008R2 or 2012.

I appreciate the questions is a bit vague but I'm really looking for a place to start my investigations.

Many thanks in advance

SteveB
  • 1,474
  • 1
  • 13
  • 21
  • 1
    You have data in multiple tables. You need to combine this different data into a different table. Is it the case that A + B + C = 1 row or A + B + C = 3 rows? Stated differently, are you combining attributes from multiple tables to define your row in the new table or are you conforming rows from all the tables to fit into the target table? – billinkc Aug 07 '13 at 14:48
  • Thanks for the quick reply - I'll need to conform the rows from multiple tables into a single table. In the future new tables with formats that don't currently exist will be added. Each table to be conformed represents a load file. A new load file will represent a new client. – SteveB Aug 07 '13 at 14:53
  • Any further updates on tis question ? – SteveB Aug 13 '13 at 13:46
  • @billinkc - Thanks for the example. My scenario is a+b+c = 1. Can you please help me with this one - http://stackoverflow.com/questions/21122004/general-etl-principle-etl-from-unknown-number-of-tables ? – Steam Jan 14 '14 at 20:07

1 Answers1

2

SSIS is certainly the tool for this job.

What you are trying to do sounds a lot like something I recently completed. I had to consolidate the results of 4 different guest surveys into standard set of data. As example all 4 surveys have some data that is similar like guest name, email address, loyalty number etc. then each has their own unique set of questions and corresponding answers.

We broke the data into

Four separate staging table to get the data for the different source files. From the source tables using a number of views I broke the data in chunks to populate the consolidates data structure.

A survey table that helped identify each survey.

ID
Name
Description

A survey sent table the has data that is common to all the surveys and identifies each survey that was sent out. 

ID
First Name
Last Name
Email

Four survey detail tables that have uncommon guest information that is not a question

Then we have a quest table the houses all the question.

ID
Question
Description

Then we have a table that ties each of the question to a survey. 

Quesiton_ID
Survey_ID

Finally the key value table that hold the guest response for each question.

Survey_Sent_ID
Question_ID
Answer

Everything is setup such that if a new question is added to one of the surveys it can be added to the workflow in about 5 minutes per question and deployed to production in another 5 minutes.

The packages rip through 40 or so source files in a range of formats in under a minute.

I use views to transform the data in SQL then SSIS just handles the transfer of data from source to destination updating data that has changed and inserting new data.

I'll keep an eye on this if you have any specific question post them up and I will help the best that I can.

Kyle Wormsbacher
  • 56
  • 5