SSIS Derived Column Transformation Gotcha

I noticed last week that a custom CRM workflow that triggers when a record is created was causing a "KeyNotFoundInDictionary" error.  As we know the target input parameter only contains fields that have been updated. This told me that  a field I expected to be populated wasn't.  The fact that whoever wrote the custom workflow should of ensured the key existed in the list of parameters sent in the target parameter before trying to access it is another story.

Anyway what was confusing was the record is created via a nightly integration which pulls data from our ERP system using SSIS.  The field causing the problem is created by concatenating a few fields together using the derived column transformation editor and set correctly in the CRM destination editor control.

Therefore I assumed the error was with the derived column field so I ran the package and added a "data viewer" on the step after the derived column is created to ensure it had a value at this point.  As you can see from the below screenshot the data viewer reported each field as "<Missing Reference>. 

 I  then cam across the following blog SSDT 2012 Data Viewers show <Missing Reference> that suggested I needed to right click on the data path and resolve references something which I have done many times but only needed to do so when SSIS reports an error which stops me from building.  

Anyway clicking "resolve references" and simply clicking OK, even though there was no reference error seemed to fix the problem. SSIS for all its greatness does get itself confused at times. Next time I ran the package I was able to see data in the data viewer. 

However, the data viewer showed that the field was set to "Null" even though we can clearly see its been created in the derived column and SSIS has reported no error. 

After a bit more investigating I found the cause of the problem it appears to be if you concatenate fields in a derived column editor and one of those fields has a value of null rather than just ignoring that field and concatenating the rest it sets the value to null, I would of expected it to either ignore it our output the value NULL in the string. 

Anyway To fix I had to do a ISNULL check in my derived column and if the value was empty output an empty string.  Now the field is populated on record creation the custom workflow fires and runs fine. However, I must remember to go back and update the plugin code to check the key exists before accessing it someday!!

• Twitter
• LinkedIn