Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Inremental loading SSIS- 200-300 tables


Inremental loading SSIS- 200-300 tables

Author
Message
nedneu
nedneu
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 66
Hi Guys,

my project started 2 months ago, and alalready'i'mransferring over 100 tables on each process i run to the DWH.
i'll probably reach 200-300 tables pretty soon (a huge DB i start with i know, surprised me too ).

since my BI is OEM ("out of the box") i still have 3 weeks versions (product dev sprints),
and tables are still changing their structure (data types, column names , new columns etc ) which gave me a real headache , so i ignored it for the first few weeks.
how did i ignore it :
1. truncated all the tables before i take them to the ODS/MRR layer
2. took all the data from the source system fully to the MRR layer
3. created only the dimensions "increamental" table (which still changes every week with new columns and changed data types )
4. dynamically creating and populating the staging tables and the warehouse tables.

now my model started to form, so i have to take care of the incremental loads
1. it seems easy since i have updatetime for each record , but i also have deletions in my shource system , how can i approach this ?
2. i've concidered CDC (but i have to put it table by table .. )
3. any solutions for someone who starts with 100-200 tables ???

(hellllppppp :-) )

Nadav
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8259 Visits: 14368
nedneu (2/10/2013)
Hi Guys,

my project started 2 months ago, and alalready'i'mransferring over 100 tables on each process i run to the DWH.
i'll probably reach 200-300 tables pretty soon (a huge DB i start with i know, surprised me too ).

since my BI is OEM ("out of the box") i still have 3 weeks versions (product dev sprints),
and tables are still changing their structure (data types, column names , new columns etc ) which gave me a real headache , so i ignored it for the first few weeks.
how did i ignore it :
1. truncated all the tables before i take them to the ODS/MRR layer
2. took all the data from the source system fully to the MRR layer
3. created only the dimensions "increamental" table (which still changes every week with new columns and changed data types )
4. dynamically creating and populating the staging tables and the warehouse tables.

now my model started to form, so i have to take care of the incremental loads
1. it seems easy since i have updatetime for each record , but i also have deletions in my shource system , how can i approach this ?
2. i've concidered CDC (but i have to put it table by table .. )
3. any solutions for someone who starts with 100-200 tables ???

(hellllppppp :-) )

Nadav

CDC might be your best bet since the size of data is likely to outrun your ability to take all data every night and run a MERGE statement to reconcile what changed. Attunity provides free components for SSIS 2012 to help with this: CDC Flow Components

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16469 Visits: 13207
When you use SSIS, your source should be stabilized before you start writing ETL.
Once it does stabilize, I would also go with CDC, as opc.three mentioned.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search