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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry about the late reply. Thanks
ASKER
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?