A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Thursday, March 26, 2015

Biml SSIS ErrorCode and ErrorColumn

Did you know that ErrorCode and ErrorColumn are "reserved" column names in an SSIS Data Flow? I've been doing this for ten years now and I had never run into this until this week. My client's application has an ErrorCodes table defined like this
USE [tempdb]
GO
CREATE TABLE [dbo].[ErrorCodes]
(
    [ErrorCodeId] [uniqueidentifier] NOT NULL
,   [ErrorCode] [nvarchar](3) NOT NULL
,   [ErrorText] [nvarchar](50) NOT NULL
,   [CreateDate] [datetime] NOT NULL
,   [CreatedBy] [uniqueidentifier] NULL
,   [ModifiedDate] [datetime] NULL
,   [ModifiedBy] [uniqueidentifier] NULL
,   [ModuleReference] [uniqueidentifier] NULL
,   [RowStatusId] [uniqueidentifier] NULL
,   CONSTRAINT [PK_dboErrorCode] PRIMARY KEY CLUSTERED ([ErrorCodeId] ASC)
);
We needed to replicate the data out of the application tables so naturally, I had described the pattern in Biml and let it run. Until it blew up on me. The 1.7.0 release of BIDS Helper uses the new and improved Biml engine and it actually reported an error on emission. Contrast that with the 1.6.0 release which happily emitted the DTSX. This of course is documented in the Release Notes for Mist 4.0 Update 1

BimlScript Errors/Warnings

  • Added an error for duplicate dataflow column nodes. This usually arises with "reserved" column names "ErrorCode" and "ErrorText" (sic)

That should actually be "ErrorColumn". There's an ErrorDescription that is added in the OnError event and I've sent an email along to Varigence to get that corrected but I'm not seeing an ErrorText anywhere.

Reproduction

The following Biml is sufficient for you to see the error generated (and to show off the new error reporting functionality in BIDS Helper 1.7.0)
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="CM_OLE" ConnectionString="Data Source=localhost\dev2014;Initial Catalog=tempdb;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;" />
    </Connections>
    <Packages>
        <Package ConstraintMode="Linear" Name="ErrorCodeTest">
            <Variables>
                <Variable DataType="String" Name="SimulateBadTable"><![CDATA[SELECT * FROM (VALUES (NULL, NULL)) D(ErrorCode, ErrorColumn); ]]></Variable>
            </Variables>
            <Tasks>
                <Dataflow Name="DFT Test">
                    <Transformations>
                        <OleDbSource ConnectionName="CM_OLE" Name="OLE_SRC dbo_ErrorCodes">
                            <VariableInput VariableName="User.SimulateBadTable"></VariableInput>
                        </OleDbSource>
                        <DerivedColumns Name="Do Nothing"></DerivedColumns>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>
Component OLE_SRC dbo_ErrorCodes of type AstOleDbSourceNode in Dataflow DFT Test has duplicate column reference with name 'ErrorColumn' on ouput 'Error'. This may be due to the 'ErrorCode' and 'ErrorColumn' columns that are automatically added to error output paths by SSIS. Ensure that all column names are unique.

Known issue

This is apparently a known issue based on this Connect Item SSIS - ErrorCode column in source table causes duplicate ErrorCode to be introduced but it's marked as Closed - Fixed. I'm not seeing the fixed part with SQL Server 2014

For those Bimling, it's also logged over on the Varigence forums but they simply reference the Connect item.

What to do

I don't know yet (sadly, this excites me). My lazy pattern was using the table selector for my OLE DB Source but for this one table, I'd need to explicitly grab the column list and alias the ErrorCode column as something else. What I'd rather do is rename the Error path's columns but that does not seem possible.

No comments: