-1

I have multiple archive tables storing similar kind of data in these tables but archived in the month wise format. Now, the requirement is to get all the archived data in to one table instead of multiple tables.

I am doing this activity with the help of Union all in SSIS, however it seems that it is taking random insert in the destination table.

Attach is the route taken for the transformation.

enter image description here

I want to prioritize the insert, please suggest!

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Avineshkumar Tiwari
  • 89
  • 7
  • 1
    Unclear. What do you mean by "taking random insert" and how do you want to prioritize it? – Tab Alleman Aug 18 '17 at 13:45
  • unless you have to fail and rollback the entire process if part fails I would suggest going with separate dataflow tasks rather than using union all. Then you can identify precedence/flow between the tasks and choose which order in which they complete. Added benefit if you can create a recordset of the filenames then you could setup a loop and use the same dataflow.... ultimately what order an insert happens should NOT matter in sql because DB engine will optimize for storage based on indexes and table definition not row ordinal position of insert – Matt Aug 18 '17 at 16:11

1 Answers1

0

You can add an extra column "Priority" to each of OLE DB sources with the corresponding priority for each source and then after union you can add Sort Component that sorts the data by Priority. But if you have a lot of data - that would be really inefficient because sort component will wait until all the source data is read.

I would suggest to write a proper source SQL statement that does the union/prioritization/sort for you and then insert into target.

Also if the sources are on different servers you can create Foreach loop container that will iterate through source tables and inset all of them into the target table. You can use this article for the reference.

Oleksii Shabrov
  • 1