2

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?

Hadi
  • 36,233
  • 13
  • 65
  • 124
Ardalan Shahgholi
  • 11,967
  • 21
  • 108
  • 144

1 Answers1

2

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 1
    Second to that. Adding - **OLE DB Destination** is extra fast with Table locking, which implies single destination. – Ferdipux Mar 05 '17 at 13:50