SSIS 2016 conditional split with multiple columns in conditions

Question

I am having an issue in executing a conditional split for upserting records into my production table, using SSIS 2016 and MSSQL 2016 (Standard Ed.)

I am trying to load two separate files (produced from an OpenVMS database) that contain similarly-formatted content, however they are from two different companies: AB_CustomerData.txt and CD_CustomerData.txt.

Customer Format files

RecordType: CU01
----------------
RecordType 2 characters
Company 2 characters
CustomerNumber 7 characters
CustomerName 50 characters

RecordType: CU02
----------------
RecordType 2 characters
Company 2 characters
CustomerNumber 7 characters
City 9 characters
State 8 characters

RecordType: CU03
----------------
RecordType 2 characters
Company 2 characters
CustomerNumber 7 characters
Phone 10 characters

AB_CustomerData.txt
-------------------
CU01AB0001234ABC Company
CU02AB0001234SmalltownAnywhere
CU03AB00012342135551212
CU01AB0002345Unbrella Corp
CU02AB0002345SmalltownAnywhere
CU03AB00023452135551213
CU01AB0003456MegaCorp
CU02AB0003456SmalltownAnywhere
CU03AB00034562135551214

CD_CustomerData.txt
-------------------
CU01CD0001234Jake's Widgets
CU02CD0001234SmalltownAnywhere
CU03CD00012342134441313
CU01CD0005678Jane's Doohickies
CU02CD0005678SmalltownAnywhere
CU03CD00056782135551314
CU01CD0006789Frank's Thingamabobs
CU02CD0006789SmalltownAnywhere
CU03CD00067892135551315

My end result is to have this in my production table:

Company	CustomerNumber	CustomerName	City	State	Phone
AB	0001234	ABC Company	Smalltown	Anywhere	2135551212
AB	0002345	Umbrella Corp	Smalltown	Anywhere	2135551213
AB	0003456	MegaCorp	Smalltown	Anywhere	2135551214
CD	0001234	Jake's Widgets	Smalltown	Anywhere	2135551313
CD	0005678	Jane's Doohickies	Smalltown	Anywhere	2135551314
CD	0006789	Frank's Thingamabobs	Smalltown	Anywhere	2135551315

I have a ForEach container to loop through these files in my directory, and do the following:

• load the file into a pre-staging table
• process the customer record type (CU01, CU02, CU03 for each customer) into record-type specific staging tables (ie: record-type CU01 goes to a CU01 staging table, etc)
• merge the record types into one larger staging table, containing all records
• merge join the staging table and the production table, to prepare for upserting
• upsert the production table

My conditional splits are defined as follows:

INSERT: (ISNULL(Production_CustomerNumber) && 
!ISNULL(Staging_CustomerNumber)) && (ISNULL(Production_Company) && 
!ISNULL(Staging_Company))

UPDATE: (!ISNULL(Production_CustomerNumber) && 
!ISNULL(Staging_CustomerNumber)) && (!ISNULL(Production_Company) && 
!ISNULL(Staging_Company))

DELETE: (!ISNULL(Production_CustomerNumber) && 
ISNULL(Staging_CustomerNumber)) && (!ISNULL(Production_Company) && 
ISNULL(Staging_Company))

On the first pass of the ForEach container, the data from the first company file loads correctly all the way through to production. However, on the second pass of the ForEach container, any data pre-existing in the production table gets deleted. I am almost positive it is because of my conditional split definitions, but I can't seem to figure out where.

Answer 1

I was close, but no cigar. If I moved the "upsert the production table" task outside of the ForEach container (but population the staging table with the merge join inside the ForEach container), everything works as expected.