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

CU01AB0001234ABC Company
CU01AB0002345Unbrella Corp

CU01CD0001234Jake's Widgets
CU01CD0005678Jane's Doohickies
CU01CD0006789Frank's Thingamabobs

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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.