Kirk: Importing/Exporting with column ErrorCode, ErrorColumns

I am currently redirecting lookup failures into error tables with ErrorCode and ErrorColumn. It works fine until I want to transfer data into the archived database. The SSIS pacakage generate by SQL Exporting tool is throwing an "duplicate name of 'output column ErrorCode and ErrorColumn" error. This is caused by oledb source error output. The error output automatically add ErrorCode and ErrorColumn to the error output selection and not happy with it.

I think the question is down to "How to importing/exporting data when table contains ErrorCode or ErrorColumn column?"

Can you not use the derived column component to create 2 differntly named rows containing the same data?

-Jamie

|||

Yes, we can use different name and map them on ole db destination when writing to the error tables. We really don't want to go that way unless there is no other option.

Currently it if failing on the first step of the Data Flow, OLE DB Source, it is not reaching Derived column Transformation, and the build in SQL import/export is not working because of the same issue.

It will be good to verify so we can enhence in our sql naming standard. "Don't use ErrorCode or ErrorColumn as column name in the table; otherwise you can't use sql import/exprot tool. They are reserved keywords in SSIS."

-tianyu

|||

Are you inserting into a database table? If so then of course you cannot do this - a table cannot have 2 columns with the same name.

That doesn't mean that you can't insert identically named pipeline columns into that table. You just have to set up the mappings correctly in the destination adapter.

Have I misunderstood the problem?

-Jamie

|||

Use case for my question,

In PayRoll package, data failed username lookup redirect to Error_Dim_PayRoll table during the process. Later on I want to export the error rows to an archiving database. When you use the SQL exporting tool, the wizard will fail and complaint duplicate ErrorCode and ErrorColumn.

It is caused by OleDb Source in the package created and used by import/export tool. The OleDb source will automatically add ErrorCode and ErrorColumn column on its Error output stream.

This is based on default settings for both SQL 2005 and SSIS.

Repro Steps:

1. Create ErrorDB and ErrorDB_Reporting
2. Create Error_Dim_PayRoll tables for both database (script included bellow)
3. Run the Insert statement in ErrorDB
4. Run SQL Import/Export tool to export the row from ErrorDB to ErrorDB_Reporting (you can save generated SSIS package somewhere)
5. You will get complaints and export fails
6. Run the generated package, still fails with duplicate column name error.

CREATE TABLE [dbo].[Error_Dim_PayRoll](
[UserName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ErrorCode] [int] NULL,
[ErrorColumn] [int] NULL, [FailureReason] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
GO

INSERT INTO Error_Dim_Customer (UserName, ErrorCode, ErrorColumn, FailureReason) VALUES(NULL, 1, 1, 'Can not lookup UserKey from Employee table by UserName')
GO

|||

Tianyu Li wrote:

It is caused by OleDb Source in the package created and used by import/export tool. The OleDb source will automatically add ErrorCode and ErrorColumn column on its Error output stream.

It is caused by OleDb Source in the package created and used by import/export tool. The OleDb source will automatically add ErrorCode and ErrorColumn column on its Error output stream which conflict the columns come in from the data source.