Load fact table SSIS
Posted on 2015-02-09
Im trying to load a Fact table from a Stage table in SQL Server. This is an existing package that is using the Slowly Changing Dimension which is incredibly slow. The process starts off with a Query to an ODBC table and then does many Lookups and then finally a SCD to Insert or Update the fact table. Now, I dont see how any of these records will be updated but just in case I want to check. I am not going to use the SCD to check though, it takes hours to complete. I was thinking about using a Conditional Split? My only problem is that there are no Primary keys in the source table so I have to use many fields to see if its an actual duplicate? I do see there is Line numbers that seem to be unique to the document number so maybe I can go off of that. Can someone help me out with the Conditional Split to check for a duplicate and to go to one OleDB destination for Insert and another for Update. This way I can evaluate if I need this. FYI, the Fact table has millions of rows so I dont think I should be doing any kind of Truncate and then reload the table. I just need a way to check the Destination before I do anything. Can anyone help me with this real quick??? Thank you!!!