Link to home
Start Free TrialLog in
Avatar of BI 0824
BI 0824

asked on

SSIS - Loading 20 tables

Hi,
I was asked a solution for the following question. I'm not very good at SSIS.  Need your advice

Source data from  Oracle contains 20 tables which are directly fed into SQL Server Tables using OLEDB Fast load option.
The issue is that the batch configured fails due to  referentrial integrity issues (no primary key ) for child tables and duplicate issues.

I suggested them to disable the constraints in the sql server tables. But once the data is loaded  into the destination and enabling constraints again, it would fail anyways due to constraint violation and duplicates.

What is the best way to implement these kind of scenarios. Should we create  a Staging Database . Firstly  load all the 20 tables into staging and then use set based commands. How about error handling in duplicates and constraint violations . Please  keep in mind these tables source contain huge amount of data ie 20 million I suppose. So using Sort etc transformations would be very  slow.

Can a detailed example be given for me to easily understand.
ASKER CERTIFIED SOLUTION
Avatar of Russell Fox
Russell Fox
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of BI 0824
BI 0824

ASKER

Thanks guys. Once staging environment is created. How do I load all these PK and FK tables.

What I'm looking for is the best scenario of using SSIS tasks. For eg :  - I use OLEDB source (Oracle) and OLEDB destination (staging table). Lets say  there are 4 tables  . A has relationship with B. B has with C and C has with D
When loading A,B,C,D (staging tables) what is the flow. What are the ssis tasks I need to use considering the volume of data.
As per what you had mentioned, 2 columns are created and when FK or Duplicate error you insert -1 .

I just want to know the flow of the data. What components we got to use in SSIS and how to log if errors in those 2 columns?
Avatar of BI 0824

ASKER

Sorry about the late reply. Thanks