SSIS 2012 VS_ISBROKEN on Derived Column

Question

I have converted a SSIS package built in VS2008 to open in VS2010.

I have a Derived Column component that fails validation. It works fine in VS2008. Here is the Expression used in Derived Column...

FINDSTRING(
    SecurityDescription,"\n",1) > 0   
    ? SUBSTRING(SecurityDescription, 1, 
                FINDSTRING(SecurityDescription,"\n",1) - 1)  
) : SecurityDescription

Below are the errors when I run the package...

Error is:

[Derived Column [36]] Error:
Attempt to parse the expression
"[FINDSTRING](#102,"\n",1) > 0 ? [SUBSTRING](#11898,1,[FINDSTRING](#11898,"\n",1) - 1) : #102" failed and returned error code 0xC00470A3.
The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.

I have double checked and SecurityDescription is a valid column. It works fine if I just add SecurityDescription. Not sure what is wrong with the FINDSTRING code.

Answer 1

First of all, you have an additional parenthesis before : character, expression is:

FINDSTRING(
SecurityDescription,"\n",1) > 0   
? SUBSTRING(SecurityDescription, 1, 
            FINDSTRING(SecurityDescription,"\n",1) - 1)  
 : SecurityDescription

Lineage ID Error

"Lineage ID is a property of the component or transformation used in the data flow task. It contains an integer value that will work as buffer pointer. Each column in the data flow task will be assigned a lineage ID." Read about lineage ID in this Microsoft TechNet article

LINEAGE ID Error implies that a Source metadata was changed, just re-validate source (connection and component) by double click on the the derived column transformation and close it , then check the columns metadata (using the advanced editor). (Note that when double-clicking on a component that contains errors it will prompt to fix it)

Or you can try removing Derived Column transformation and adding it again (if previous solution doesn't works)

Also it is good to revalidate Source columns metadata

Analyzing the exception

If you take a look at the exception thrown you will see that SecurityDescription has different lineage id in the same expression which is not right.

"[FINDSTRING](#102,"\n",1) > 0 ? [SUBSTRING](#11898,1,[FINDSTRING](#11898,"\n",1) - 1) : #102"

So it is clear that metadata needs to be updated.

Also from the error screenshot it looks like the expression is using SecurityDescriptionNoLineFeeds not SecurityDescription.

These errors maybe caused during the package upgrade process

Side Note: check that SecurityDescription is not defined multiple time in differents components. (Ex: Source.SecurityDescription and Script.SecurityDescription)

Try dropping derived column transformation and re-creating it or add a new derived column and drop the old one

Answer 2

The errors suggest SecurityDescription is no longer a valid column to reference. Perhaps VS2008 ignores such errors, or the column was lost in the upgrade.

Answer 3

The columns are tokenized, so references got probably broken.

Many similar errors are simply corrected by rebuilding in the newer VS version; but for some, you have to edit the expression so it gets parsed again; then build.

https://learn.microsoft.com/en-us/sql/integration-services/data-flow/transformations/derived-column-transformation-editor

I generally add the package to source control, which allows me to compare the XML and copy-paste the expressions from a previous version