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.