SQL Server Load stage (Datastage type to OLE DB type error)

[Rwe]

Hi all,

I am trying to use MS SQL Server Load stage to bulk load data
into a MS SQL Server target table but I get the following message:

C_FIELD_J_Fact_PS_F_INV_TRANS_E1..MS_SQL_Server_Load_231.IDENT5: Failed to convert column SRC_SYS_ID value from DataStage type to OLE DB type.

Both source table and target tables are in SQL Server and the involved
field SRC_SYS_ID are defined to be char(5) in source and target table.

Could you give me some hints to solve this issue?

Thanks,
Rwe

[DeepakCorning]

Did u try loading it to a file? Do u still get the same error?

[Rwe]

Before that, I used Dynamic RDBMS stage, it worked fine. I wanted to see
if bulk loading can reduce ETL Time.

Thanks!

[Rwe]

By the way, is the SQL Bulk Stage the same as SQL Server Load Stage?
I didn't find SQL Bulk Stage inside Designer.

[flashgordon]

ms sql can have data types that odbc doesn't support. Check the declaration of the column that is giving you a problem. If you can change the column declaration in ms sql to a more generic type like char or decimal and it's not destructive to try it, try that. I actually thought all access to ms sql is through odbc in Datastage which would make it odd that it worked in DRS stage and not ODBC bulk loader. You don't see native MS Sql load stages like you see for Oracle.

[chulett]

Native MSSQL connectivity is only available on a Windows based Server, which is why you won't see the MS OLEDB or MS SQL Server Load stages on a UNIX install. If (like myself) you've only ever worked with UNIX servers, it can make it seem like they don't exist.

Rwe - can you post how you defined this field in the job itself? You've said they are CHAR(5) in both source and target tables but what did you define it as in the job? Also, when this worked in the DRS stage, what database type did you use - MSSQL Server or ODBC?

[Rwe]

I defined this field to be CHAR(5) in all the job stages and when it worked in DRS stage, I defined the database type to be ODBC.

Thanks all for the help.

[Nisusmage]

Was there any solution to this problem.
I'm getting the same error message.

I'm converting a decimal(3,2) to numeric(3,2) and I still get that error.
I've tried all kinds of data types. Varchar, real, decimal. But everything gives me that error.

[chulett]

There's no difference between Decimal and Numeric in DataStage Server. They're synonymous. So curious why the perceived need to convert?

How about some details about your job setup?

[ray.wurlod]

Have you tried using an ODBC stage rather than an OLEDB stage?