SSIS: ErrorDescription and ErrorColumn (ErrorColumnName)

If you’re looking for a way to add the logical error description into your dataflow, you may want to use a common Script Component with these lines of Visual C# script (overwrite the existing procedure):

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);
}

A detailed manual to accomplish this can be found here, thanks to Jamie Thomson.


But if you also want to add the specific column name on which the error occured, take a look at this customer SSIS component.

EOD logo

EOD in action:

EOD’s output:

EOD’s properties:

Error Output Description Properties

How to install this BIDS 2008R2 component?
Step 1) Extract the ZIP-file
Error Output’s Description on CodePlex

Error Output’s Description

Here’s a rebuild version of the original version 2.0.0 ErrorOutputDescription.dll library (Windows 7 and VS2010) which should be more friendly in Win7:
(Rename the file extension to *.zip, WordPress doesn’t allow ZIP-files)

Right click to download and Save As EOD.zip

Error Output Description - Required Files


Step 2) Open a command-windows (search for ‘cmd’) and ‘Run as Administrator’, then execute the Install.bat file.

Install Error Output Description as Administrator


Step 3) Enable the custom component in BIDS so that it will be listed in the Toolbox:
Go to Tools -> Choose Toolbox Items (in BIDS)

Error Output Description Enabled in BIDS

Select the Error Output Description’s DLL and click on ‘OK’.
It should now be selectable in the Toolbox:

Error Output Description Component in BIDS


If you’re having problems with this component (it can be a bit buggy), a quick work around is to delete all dataflow components down-stream of the EOD-component and possibly rebuild the package, before you edit the EOD-component.

Other discussions about this custom component can be found here.



But…after all…it works great once you get it running 😀
You may want to use the column name to zoom in on the incorrect values which were found during processing.

A practical example of such a report can be found here.

Error Output Description - Error Report

Advertisements

SSIS: FINDSTRING() with backspaces

It took some time for me to figure this one out, but actually it’s pretty easy…
Apparently the SSIS coding is based on C/C#, so you’ll need to use an escape character to get the filename out of ‘Fully qualified’ path/filename.

So to get ‘FileName.txt’ out of ‘C:\Temp\FileName.txt’, you’ll need to use a combination of FINDSTRING() and REVERSE():

FINSTRING() – To find a string-part within a string
REVERSE() – Because you’re not able to select the last string-part within a string.


(DT_WSTR,50)
REVERSE(
SUBSTRING(REVERSE(@[User::FileName]),1,FINDSTRING(REVERSE(@[User::FileName]),"\\",1) - 1)
)