CODEWITHSUNDEEP
ssisdata-warehousesql-server-data-toolsssis-2012sql-data-warehouse
Guissous Allaeddine
Guissous Allaeddine

Reputation: 445

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

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

Upvotes: 0

Views: 485

Answers (2)

KeithL
KeithL

Reputation: 5594

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.

Upvotes: 1

KeithL
KeithL

Reputation: 5594

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

Upvotes: 1

Related Questions