SSIS

Biju Varughese


How to convert Character Set ID 937 defined column in AS400 into SQL database

Phil Brammer


What is 937 Chinese

Biju Varughese

Yes.

AS400 Table character set is defined as 937.

Phil Brammer

What connector and driver are you using to get to the AS400 data

Biju Varughese

I am using Microsoft OLE DB provider for DB2

Phil Brammer

And are you receiving any errors If so, what are they

Biju Varughese

I have craeted SSIS package that takes the data from AS400 and Lookup the row in sql database and insert all new rows.

Its say I can't convert unicode and non unicode string data type.

Phil Brammer

Right click on the OLE DB connection and select properties. Set the "AlwaysUseDefaultCodePage" property to True.

Phil Brammer

Also, the error you are receiving will happen when you have discrepancies between DT_WSTR types and DT_STR types. Also a DT_WSTR going into a varchar field will yield that error as well.

Biju Varughese

It is set to true.

Biju Varughese

I have looked at the Advanced Editor for Source Input Output Properties Administrate the column has been defines as DT_STR

for Input , output ,error output.

If I run this package it gives me the following error:

[Source - Query [1]] Error: There was an error with output column "ACCOUNTID" (17) on output "OLE DB Source Output" (11). The column status returned was: "The value could not be converted because of a potential loss of data.".

[Source - Query [1]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "ACCOUNTID" (17)" failed because error code 0xC0209072 occurred, and the error row disposition on "output column "ACCOUNTID" (17)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - Query" (1) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

Biju Varughese

What should be the datatype define in SQL database for DT_WSTR

Phil Brammer

Biju Varughese wrote:

What should be the datatype define in SQL database for DT_WSTR

nvarchar

Biju Varughese

Source View.

SELECT UPPER(SVSGVL) AS AccountID, UPPER(SVLDES) AS AccountDesc,
INT(SVSGTP) AS AccountTypeID, INT(SVTR) AS TranslationMethodID, INT(SVDEF) AS AccountValue, INT(SVRM) AS RemeasurementID
FROM V820PRMF.GSVL01
WHERE (UPPER(SVSGMN) = 'ACCT')
ORDER BY AccountID

by default it is DT_STR datatype for External and Output Column

Destination View.

it shows DT_WSTR datatype for External and Input Column as DT_STR

If I change the Source External datatype to be DT_WSTR for both External and Output column

and it does not allow error output to be changed it remain DT_STR.

Gives a error saying error output properties that does not match.

Biju Varughese

By default system change the SQL datatype to ST_WSTR in SSIS.