ETL Design

Question

ETL Design

gugan_ta

SSCommitted

Points: 1649
More actions
November 7, 2011 at 11:29 pm

#262508

Hi All,
Designing ETL to extarct 82 tables from source to staging. We have two option, pls suggest which will be the best approach.
Option 1 : Single package with multiple containers (each container pointing to resp table)
Option 2 : Multiple package with container in each package to load resp table
This will be increamental load on daily basis. Source will be SQL server DB
We have package & Container level logging.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

I would keep the packages as small as possible. That way you won't have to wait 15 minutes to open a package and wait for the validation to finish.

82 different packages may be overkill, so I would go for a mix between option 1 and 2. Maybe 5 containers per package, which would give you about 17 packages. Make sure you load the different containers in parallel, so if one fails, the others can still continue.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

kramaswamy SSCoach Points: 18181 More actions · Answer 2

IF there isn't a huge amount of logic being used to transfer the data, maybe you could use a foreach loop, and have a table which has the names of the tables that are being transferred, then dynamically build a data source using a SQL Command with the names of the tables.

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

gugan_ta (11/7/2011)
Hi All,
Designing ETL to extarct 82 tables from source to staging. We have two option, pls suggest which will be the best approach.
Option 1 : Single package with multiple containers (each container pointing to resp table)
Option 2 : Multiple package with container in each package to load resp table
This will be increamental load on daily basis. Source will be SQL server DB
We have package & Container level logging.

If you can use third-party solutions, check the commercial CozyRoc Data Flow Task Plus. It is an extension of the standard Data Flow Task, which allows dynamic columns at runtime. You can implement your requirement with single Data Flow Task, running inside For Each Loop container.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Phil Parkin SSC Guru Points: 246959 More actions · Answer 4

kramaswamy (11/8/2011)
IF there isn't a huge amount of logic being used to transfer the data, maybe you could use a foreach loop, and have a table which has the names of the tables that are being transferred, then dynamically build a data source using a SQL Command with the names of the tables.

Assuming the destination is a single table and that the source column names are the same...otherwise I can't see how this would work.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

Phil Parkin (11/10/2011)
kramaswamy (11/8/2011)
IF there isn't a huge amount of logic being used to transfer the data, maybe you could use a foreach loop, and have a table which has the names of the tables that are being transferred, then dynamically build a data source using a SQL Command with the names of the tables.
Assuming the destination is a single table and that the source column names are the same...otherwise I can't see how this would work.

Ah, good to see you're still alive 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 246959 More actions · Answer 6

Koen Verbeeck (11/10/2011)
Phil Parkin (11/10/2011)
kramaswamy (11/8/2011)
IF there isn't a huge amount of logic being used to transfer the data, maybe you could use a foreach loop, and have a table which has the names of the tables that are being transferred, then dynamically build a data source using a SQL Command with the names of the tables.
Assuming the destination is a single table and that the source column names are the same...otherwise I can't see how this would work.
Ah, good to see you're still alive 😀

Only just - on holiday in Morocco and stilll recovering from the shock of paying twice as much as I do in London for beer!

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

Phil Parkin (11/10/2011)

Only just - on holiday in Morocco and stilll recovering from the shock of paying twice as much as I do in London for beer!

:blink: And beer in London is already expensive 🙂

How is Morocco? Never been there myself.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 246959 More actions · Answer 8

Note to OP: sorry for hijacking your thread 🙂

Morocco is hectic and chaotic. An interesting place, with some excellent people and many more who are just trying to separate me from my wallet.

Sunny and warm - just been quad biking in the foothills of the Atlas mountains, which was amazing. Can't say I'm in a hurry to get back to work. But I think that a week here has been enough for me. The price of beer IS a show-stopper!

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin