Error on transform string from sequential file to SQL DB

[Kwang]

I'm very new to datastage so I hope somebody could help me solve this wierd problem.

I want to transform a string (length is 31characters) from a text file (sequential file stage) to a SQL database (OLEDB stage), the datatype of the target field in the target table is varchar(120). I got the following warning messages:

KWUntitled2..MS_OLEDB_2: OLE DB provider error: Connection failure

KWUntitled2..MS_OLEDB_2: OLE DB provider error: [DBNMPNTW]ConnectionWrite (GetOverLappedResult()).

KWUntitled2..MS_OLEDB_2: DSLink4:Array 1 has invalid row(s).

KWUntitled2..MS_OLEDB_2: OLE DB provider error HRESULT=0x80004005.

KWUntitled2..MS_OLEDB_2: DSLink4:Row 4 was rejected by OLE DB provider. dim_provider_id=71;provider_cd=0084;provider_legal_nm=xxxxxxx xxxxxx xxxxxxxxx xxxxxx.

The record did not get through.

Once I changed the data to be a 30-character string, the job ran O.K. and the data is transformed to the sql database successfully.

What could be wrong with this? My job design? Or the sequential file stage setting?

Please give me some clues upon your experience.

Thanks in advance.

[Vipin]

Dear Wang,
Make sure that if you are using CAST function before inserting into the SQL DB , you specify the field length as well.
When field length is not specified with the CAST function, the default length is 30.

It is just a thought that you might be facing this problem I faced once 4-5 yrs back.

And please do reply , if this helps you.

Regards,
Manoj Mishra

[Kwang]

Thanks Manoj,

But I am not using the cast function anywhere. Actually the data type for the field in the source text file is also varchar(120). I'm just doing a direct load. The source text file contains exact data (and data type) from the target SQL DB (loaded from the SQL DB in another job).

Also, I noticed that in the tranformer stage, the Derivation does not recognize the cast function.

Please give more detailed instruction.

Wang

[shawn_ramsey]

Thanks Manoj,

But I am not using the cast function anywhere. Actually the data type for the field in the source text file is also varchar(120). I'm just doing a direct load. The source text file contains exact data (and data type) from the target SQL DB (loaded from the SQL DB in another job).

Also, I noticed that in the tranformer stage, the Derivation does not recognize the cast function.

Please give more detailed instruction.

Wang

We run into this same type of issue all the time with both the OLEDB and SQL Bulk loaders. The issue is that DataStage is not returning the SQL Server error. The best way that we have found to figure out what is going on is to run the Microsoft SQL Profiler tool against the database looking for errors being generated by the DataStage job. Once you get the real error it makes it a lot easier to find the problem.

There are two Ascential ECases (39912 and 39916) about the misleading error message in the OLEDB and SQL Bulk stages. I sure hope that they fix this soon because it seems that our developers end up burning a lot of cycles trying to figure out what is going on before they ask me for help and we run profiler together.

[justlrng]

I work with KWang and we have tried the profiler. It is not returning any errors. It just states "Batch Completed" but it totally misses the record of 31 characters we are trying to insert into a 36 varchar field.

[Kwang]

Hi,
We got this problem solved. We contacted the Ascential support group and upgraded the MDAC from version 2.53 to version 2.60.

KWang