Hello guys, The question I'm going to ask might be very silly. I need a solution for the question which I could not find by googling. Question is: I have a staging database which receives all the new records on daily basis from the OLTP server. From staging I need to populate the dimension tables. When I use a Lookup Transform to find the matched and unmatched records, I am putting an OLEDB Destination for newly created data for the dimension table, For the records which already exist, I need to update them in dimension table. Initially I used OLEDBCommand and a stored procedure to update the records to dimension table but when I googled, I read that OLEDBCommand will operate in row-by-row basis and it takes huge time for millions of records. It was also suggested that SCD (Slowly Changing Dimension) is also done on a row-by-row basis and will kill time. When I tried to do the OLEDBDestination and SQL Command, and with the stored procedure, it threw an error saying the mapping columns are not available. I know why... it's because it is a stored procedure which only updates but does not give any output. So again, how can I update the dimension tables after Lookup Transform? Am I going about this the wrong way? Please guys help me out with a solution. Thanks in advance.
The question is not silly at all. It's a challenge for many to perform something like a T-SQL Merge in SSIS. There are some options to choose from: - Create a view looking just like your destination and have an INSTEAD OF TRIGGER on the view, which performs the MERGE operation. That way, you'll get bulk insert to the view and if you write the INSTEAD OF TRIGGER in a clever way, you will have set based operations all the way. - Create a table type, create a stored proc which gets a table valued parameter of that type, and in SSIS use an
ADO.NET source and a Script Component as target ( more here:
http://voluntarydba.com/post/2012/12/05/Using-table-valued-parameters-in-SSIS.aspx ) - Move the operation to the Control Flow, and simple use an Execute SQL task perform the MERGE operation for you. There are pros and cons with all Three solutions. If staging and destination table exist in the same database or at least same server instance, I would go for the third solution.