SSIS 2016 conditional split with multiple columns in conditions

splitssis-2016upsert

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.

Best Answer

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.

Related Question