SSIS 2012 – Resolving VS_ISBROKEN Error on Derived Column

sql serverssisssis-2012ssis-expressions

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.

Best Answer

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

Related Solutions

SSIS 2012 Retry Feature – How to Implement When Database Connection Fails

There is a way to setup the number of times to retry and to set the interval between each try inside the SQL Agent Job. Simply open the job and edit the job step. Click on advanced link. Once there, you can set the retry attemps and retry interval see below. You can also setup the package to restart from the point of failure. I have not done that personally but I am sure there is a blog that talks about it. This one might help. http://www.mssqltips.com/sqlservertip/1408/integration-services-checkpoints-to-restart-package-from-failure/ enter image description here

SSIS import with header record

Instead of using a script component, I recommend using a more database-centric approach.

Consider the following approach:

Read the data from the flat file using a flat file source. Keep your flat file source simple. It should have one column which contains the EntireRow.
(optional) add a script component or derived column to count rows and generate a InputRowNumber field. The InputRowNumber is helfpul for debugging.
Write the two columns to a simple staging table called InputFlatFile containing the InputRowNumber int, and EntireRow varchar(max) fields.
Using ExecuteSQL Tasks, execute queries which insert the data from your InputFlatFile staging table into your real database. Based on your example data, these queries should be fairly straight forward.

INSERT INTO HeaderTable (NAME_EDC, DUNS_EDC)
SELECT SUBSTRING(EntireRow, 231, 60) NAME_EDC
     , SUBSTRING(EntireRow, 291, 13) DUNS_EDC
FROM InputFlatFile
WHERE LEFT(EntireRow,2) = '01'

INSERT INTO DetailTable (columns..)
SELECT SUBSTRING(EntireRow, 1, 2) REC_TYPE_DETAIL
     , SUBSTRING(EntireRow, 3, 6) PMT_NUMBER
     (... more columns)
FROM InputFlatFile
WHERE LEFT(EntireRow,2) = '02'

use an ExecuteSQL task to permanently delete the data in the staging table so it is ready for the next load, or use an OUTPUT clause to move it into an archive table if necessary.

I think the database-centric approach is better for the following reasons:

From this base, you can easily create a queryable data lineage. Want to know which file created which row in the final database? Create a FileLog table and insert a row at the start of the control flow with logging information like file name, modified date, etc. Retain a reference to the InputRowNumber and FileLogID in the destination database and you can trace the data from source file to destination easily.
In my experience, people who work with SSIS tend to have a stronger database background than OO programming background so the Set Theory that I used above comes naturally and is easier to maintain on an ongoing basis (also, there tends to be more helpful ideas for database-centric approaches on this and other forums)

Best Answer

Related Solutions

SSIS 2012 Retry Feature – How to Implement When Database Connection Fails

SSIS import with header record

Related Question