jknj72
asked on
Load fact table SSIS
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!!!
ASKER
So are you saying I should be using the SCD with a hash function to do what I need to do or should I be using a T-SQL Merge statement for what I need to do? I need help either way ;)
Thanks
JK
Thanks
JK
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
To implement a hash function Write a SELECT statement and add a calculated column using the T-SQL HASHBYTES
function when reading the data.
Faster Slowly Changing Dimension with Hash Function
and consider using T-SQL MERGE statement.