Search code examples
sql-serverdesign-patternsssisetlbids

Which approach is better for saving the error in SSIS

I use SSIS for transferring the data and I use SQL server 2016 I need to use some lookup component in my DataFlow task and if I can't find the matches, I should save the record as information in one table. That means if for each Lookup component I can't find the related record I have to save that row in a table.

I have to type of DataFlow design and i would like to know which one is better than other?

You can see the DataFlow design in below Images.

In this pattern I use two separate OLE DB Destination but in both, I am saving the information in the same Table. enter image description here

In this Pattern I use the Union All Component to union all records and save them in the same time in Destination table. enter image description here

I would like to know which design is better than another and what is the pros and cons of each design?

Solution

  • For better performance and other things, it is better to use UNION ALL because:

    1. UNION ALL is opening one connection with the database (other case a connection is opened for each oledb destination)

    2. If the package fails on LookUp 1 data resulted by Lookup will be inserted to the OleDB Destination and the package fails, so when restarting the data may be reinserted.

    3. It is not a good idea to create multiple OLEDB Destination for the same Table, it will decrease the performance due to the (Table Exclusive-Lock) each insert will wait others to be done