Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Inremental loading SSIS- 200-300 tables Expand / Collapse
Author
Message
Posted Sunday, February 10, 2013 9:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, February 10, 2013 10:01 AM
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
Post #1418117
Posted Monday, February 11, 2013 7:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:19 PM
Points: 7,127, Visits: 12,655
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
Post #1418424
Posted Monday, February 11, 2013 7:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:40 AM
Points: 13,723, Visits: 10,678
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1418431
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse