SSIS 2012 VS_ISBROKEN on Derived Column

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 > 0 ? SUBSTRING - 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.

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 > 0 ? SUBSTRING - 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

StackExchange Database Administrators Q#162732, answer score: 5