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
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.
ReproductionThe 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 issueThis 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 doI 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.