I have a simple SSIS package that I want to move data from SQL 2005 to DB2/AS400. In order to use OLEDDB destination, I installed HIS2004 that provides DB2OLEDB support.
For simplicity and testing purpose, I created a connection pointing to a SQL table that contains only one ccolumn "last_name" of type "nvarchar(50)". This is my source, I then created another connection using DB2OLEDB pointing to a table on DB2 that contains only one field "name" of type "GRAPHIC(50)". This is my destination and the connection works fine to DB2 database. When I run the package, it has type conversion error as follows:
[OLE DB Destination [34]] Error: An OLE DB error has occurred. Error code: 0x00040EDA. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E07 Description: "Data or literal value could not be converted to the type of the column in the data source, and the provider was unable to determine which columns could not be converted. Data overflow or sign mismatch was not the cause.".
If I manually assign a vaule to the "last_name" using the "Derived Column" task like "Hello John", it just works fine.
Anyone knows what went wrong? Did I miss anything here? Thanks.
As a FYI: DTS works great in moving data from SQL 2000/2005 to DB2.
Your help will be appreciated.
There are quite some conceptual differences between SQLServer's nvarchar/nchar and DB2's vargraphic/graphic, for example, nvarchar/nchar are unicode columns, graphic/vargraphic however are not, unless you bind them with a unicode CCSID.
I was able to accomplish similar scenarios using DB2OLEDB provider by following the steps below:
1) Set DB2 connection's "HOST CCSID " property to "Unicode - UTF(8) [1208]"
2) At DB2 destination column, for example, for storing data from your nvarchar(50) input column, set the column type to Graphic/Vargraphic(100) CCSID 13488.
HTH
Wenyang
No comments:
Post a Comment