1

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.

J. Mini
  • 1,161
  • 8
  • 32
Kulstad
  • 95
  • 9

1 Answers1

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.

Kulstad
  • 95
  • 9