 DTS package fails: duplicate column name resolution error -
05-02-2006
, 11:04 AM
I'm getting a really confusing error when trying to run a DTS package. What I'm trying to do is truncate an existing table on the destination server and then copy specified rows (via SQL) from the source server.
The really odd thing is, there are 3 tables involved and I constructed all of the SQL queries and packages essentially the same. 2 packages execute flawlessly; the third produces this error after a successful truncate:
"Duplicate column name resolution could not be done because the ordinal specified a column of a different name"
I'm not sure where this is coming from. There are NO duplicate column names-- I have gone over and over this to make sure. Here is the destination table code:
CREATE TABLE [dbo].[MPWS_L_AUDIT_HEADER] (
[Audit_ID] [int] NOT NULL ,
[DNN] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DNN_Item] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PON] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Planned_Qty] [real] NOT NULL ,
[Actual_qty] [real] NOT NULL ,
[Material] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Customer] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Model] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Transceiver] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Line_ID] [int] NOT NULL ,
[Stage_ID] [int] NOT NULL ,
[Shift_ID] [int] NOT NULL ,
[Stamp] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[User_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Supervisor_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Item_ID] [int] NOT NULL ,
[Flow_id] [int] NULL ,
[Scales_Setting] [real] NULL ,
[PID_Size] [real] NULL ,
[Fail_Flag] [bit] NOT NULL ,
[Complete_Flag] [bit] NOT NULL ,
[Create_TS] [datetime] NOT NULL ,
[Create_UID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Modify_TS] [datetime] NULL ,
[Modify_UID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Audit_ID is the primary key. My SQL makes sure to pull in only the rows that have related records in another table (DETAIL) which is defined as follows:
CREATE TABLE [dbo].[MPWS_L_AUDIT_DETAIL] (
[Detail_ID] [int] NOT NULL ,
[Audit_ID] [int] NOT NULL ,
[User_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Start_TS] [datetime] NOT NULL ,
[Complete_TS] [datetime] NULL ,
[ID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Scales_Result] [real] NULL ,
[pid_fAIL_fLAG] [bit] NOT NULL ,
[Fail_Flag] [bit] NOT NULL ,
[Stamp] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Create_TS] [datetime] NOT NULL ,
[Create_UID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Modify_TS] [datetime] NULL ,
[Modify_UID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Detail_ID is the primary key here. The tables are joined in my SQL query on Detail_ID as shown:
select * from openrowset('SQLOLEDB','source';'login;'password',' SELECT * FROM dbo.L_AUDIT_HEADER INNER JOIN dbo.L_AUDIT_DETAIL ON dbo.L_AUDIT_HEADER.Audit_ID = dbo.L_AUDIT_DETAIL.Audit_ID')
As previously noted, SQL in a related package that is virtually identical to this runs fine when pulling records from another table, dbo.L_AUDIT_FAIL, that joins to the DETAIL table on Detail_ID. As for the problem package, I have deleted everything once and redone the destination table and package from scratch and still get the error. I have played around with transformation and other options with no effect.
Searching on this error nets almost nothing, and the only promising page in the search results will not load for me. I sure hope someone can help because this has turned into a major showstopper...
Thanks,
Randall Arnold
 |
Linear Mode
