How to add a column based on the table which the data come from in SSIS?

Question

I have two source tables:

1. Ext_Agreements
2. ABS_Agreements

both have the same columns : "each table have different data this is just an example"

                          ID, START_DATE,  END_DATE, 
                          01, 28/02/2021, 04/05/2021
                          02, 11/10/2021, 09/01/2022
                          03, 08/01/2022, 03/05/2022

I want to merge them in one table in the destination Database, while maintaining the information of the type of the agreement by adding a columns "AGREEMENT_TYPE" that contain "Ext" or "ABS" based of the source table of the Agreement.

the destination table will have :

                          ID, START_DATE,  END_DATE, AGREEMENT_TYPE
                          01, 28/02/2021, 04/05/2021,  ABS
                          02, 11/10/2021, 09/01/2022,  EXT
                          03, 08/01/2022, 03/05/2022,  ABS

I tried merge and Union All and derived columns, but I didn't succeed. thank you

Answer 1

If you want to use SSIS, then...

In data flow.

Create a source based on:

select ID, START_DATE,  END_DATE --, AGREEMENT_TYPE = 'EXT'
from Ext_Agreements

Add a derived column and add:

AgreementType and set (DT_WSTR, 3) "EXT"

Do the same this for ABS (source and der col).

Then put them together in a UnionAll.

Answer 2

How about a UNION ALL in your source.

select ID, START_DATE,  END_DATE, AGREEMENT_TYPE = 'EXT'
from Ext_Agreements
UNION ALL
select ID, START_DATE,  END_DATE, AGREEMENT_TYPE = 'ABS'
from ABS_Agreements