DSXchange

Hello,

I am reading a SQL Server DB Table and am loading another table on SQL Server DB.

Below is the job flow:
ODBC -> Copy -> ODBC

I have a problem wherein I get the below error for some tables and the rest of the tables seem to be loaded fine.
I have not defined any column definition explicitly and the target table is always dropped and recreated.

Error:

Can someone please help me figure out why I am getting this error for some of the tables.

My guess is that you have some unbounded VarChar column definitions - that is, data type VarChar but no Length specification. DataStage can handle that, but SQL Server cannot.

Ray,
Below is my input column definition for the table that is not loading:

FIELD1 DECIMAL(5,0) NOT NULL
,FIELD2 DECIMAL(10,0) NOT NULL
,FIELD3 NVARCHAR(2) NULL
,FIELD4 DATETIME NULL
,FIELD5 DECIMAL(10,0) DEFAULT 0
,FIELD6 DECIMAL(5,0) NULL
,FIELD7 DECIMAL(10,0) NULL
,FIELD8 NVARCHAR(30) NULL
,FIELD9 DECIMAL(1,0) NULL
,FIELD10 DECIMAL(5,0) NULL
,FIELD11 DATETIME NULL
,FIELD12 NVARCHAR(2000) NULL
,FIELD13 DATETIME NULL
,FIELD14 NVARCHAR(30) NULL
,FIELD15 DECIMAL(10,0) NULL
,FIELD16 DECIMAL(5,0) NULL
,FIELD17 DECIMAL(10,0) NULL
,FIELD18 DECIMAL(5,0) NULL
,FIELD19 DECIMAL(10,0) NULL
,FIELD20 DECIMAL(5,0) NULL
,FIELD21 DECIMAL(10,0) NULL
,FIELD22 DECIMAL(5,0) NULL
,FIELD23 DECIMAL(10,0) NULL
,FIELD24 DECIMAL(5,0) NULL
,FIELD25 DECIMAL(10,0) NULL

nima511 wrote:I have not defined any column definition explicitly

What does this mean?

I have not defined any column definition explicitly in my job. I got this from the database to give an idea about the fields I am dealing with.

Repeating something does not an explanation make.

To isolate the field for which I was getting the error, tried dropping the field: FIELD12 NVARCHAR(2000) and I am not getting any error and the target table is getting populated.

How can I handle nvarchar(2000)?

The job does a select * on the source table. It has no columns defined in the stage.

Basically this job selects, and copies data to a destination table. The destination table is created from the source table's metadata. Then after the table is created the data from the src table is inserted into the destination table.

It works on tables that do no have the nvarchar(2000) datatype.

So in both the source and destination stages there are no column definitions in the stage.

We are trying to figure out why using nvarchar(2000) causes the issue.

Are you using Runtime Column Propagation?

Yes I am using RCP.