I have created a package that insert data from Iseries table to SQL 2005 database. I am using Microsoft OLEDB provider for DB2 and Native OLEDB for SQL2005 database as connector. I am getting following errors: [OLE DB Source [1]] Error: There was an error with output column "ACCOUNTID" (32) 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.". [OLE DB Source [1]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "ACCOUNTID" (32)" failed because error code 0xC0209072 occurred, and the error row disposition on "output column "ACCOUNTID" (32)" 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 "OLE DB Source" (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. Thanks, Biju Varughese

Looks like the data types between your DB2 database and SSIS do not match for ACCOUNTID.

ACCOUNTID isn't a numeric(p,s) field is it?

It isa string data type

Phil, Data type:string [DT_STR]

Do you have the SSIS datatype big enough to hold all of the data in the DB2 column?A source column 50 bytes in length will throw that error if going into something smaller in SSIS (say, 40 bytes).The other scenario is that some databases don't hold true to the field specification for storing the data. That is, even though the field is defined at 50 bytes, the database engine will allow storing data greater than that. The definition is merely used for presentation (ie. selecting the results). In this case you would be best served to ensure you perform a substring on the column in the source query to match the length you are expecting.

I have column defined as 16 Char long and SqlTABLE defined as 16 char SELECT CHAR("UPPER"(SVSGVL),16) AS AccountID, "UPPER"(SVLDES) AS AccountDesc, INT(SVSGTP) AS AccountTypeID, INT(SVTR) AS TranslationMethodID, INT( SVDEF) AS AccountValue,INT( SVRM) AS RemeasurementIDFROM V820PRMF.GSVL01WHERE ("UPPER"(SVSGMN) = 'ACCT')ORDER BY AccountID

Just out of curiosity, why is UPPER() surrounded with double quotes?Shouldn't your query be written as:SELECT UPPER(CHAR(SVSGVL,16)) AS AccountID.....

I had copied this sql statement from Analysis Service Project that create data source viewfromIseries using new named query and build cube. Visual Studio add those quotes by it self. Upper and Char are functions, does it matter which comes first?

Well, it is most logical to convert to character before converting to UPPER case, but that's just me. I didn't like surrounding the function, UPPER(), with double quotes either. If it works, it works, I guess. In this case it isn't so....One other thing to try doing is setting the error output to redirect rows upon error instead of failing the component. This would allow you to add a data viewer to inspect the error data to see what's going on.

And since you are converting ACCOUNTID to character data type, what is the original data type of ACCOUNTID?

In Iseries it is Char with 16 length long

One other thing to check... On your OLE DB source component, do you have AlwaysUseDefaultCodePage set to TRUE? That might help if not.

It is already set to true. There must be otherpeople doing this.

Have you redirected the error rows and inspected them as I suggested earlier? I use DB2 all of the time and don't run into any issues. Maybe Ryan can chime in if he's still around.

I'm not that familiar with DB2, so disregard this if it doesn't make sense. Is there any possibility this is a unicode to ansi issue?

When I redirect to raw data source it works. but when I direct to differnet data source it gives me the errors.

Biju Varughese wrote: When I redirect to raw data source it works. but when I direct to differnet data source it gives me the errors.What's the data type in the destination table? And what is its length?

distination database CREATE TABLE [dbo].[DimAccount]( [AccountID] [char](16) NOT NULL, [AccountDesc] [varchar](50) NOT NULL, [AccountTypeID] [int] NOT NULL, [AccountValueID] [int] NOT NULL, [TranslationMethodID] [int] NOT NULL, [RemeasurementMethodID] [int] NOT NULL, CONSTRAINT [PK_DimAccount_1] PRIMARY KEY CLUSTERED ( [AccountID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Source Database Column Type Length AccountID String[DT_STR] 16 AccountDesc String[Dt_STR] 50 AccountTypeID Four byte Signed Int AccountValueID Four byte signed Int TranslationMenthodFour byte Signed Int Remeasurement Four byte signed int Thanks.

And how do you expect to stick a 16 character AccountID into a 15 character width field without truncating results? (Just as the error message indicated)

It is 16 Char long in destination table.

Problem is the data in iseriesis defined as double byte char. So the conversation betwwen double to single is causing this error.

Biju Varughese wrote: It is 16 Char long in destination table.Ah, you edited out the error, I see. It was listed as a 15 byte destination field.

Biju Varughese wrote: Problem is the data in iseries is defined as double byte char. So the conversation betwwen double to single is causing this error. So use DT_WSTR, not DT_STR.

Phil, I came across you question 'ACCOUNTID isn't a numeric(p,s) field is it?' when a guy had the following issue. ******************** I have created a package that insert data from Iseries table to SQL 2005 database. I am using Microsoft OLEDB provider for DB2 and Native OLEDB for SQL2005 database as connector. I am getting following errors: [OLE DB Source [1]] Error: There was an error with output column "ACCOUNTID" (32) 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.". [OLE DB Source [1]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "ACCOUNTID" (32)" failed because error code 0xC0209072 occurred, and the error row disposition on "output column "ACCOUNTID" (32)" 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 "OLE DB Source" (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. Thanks, Biju Varughese ************************ Now I got the similar error when I tried to load data froman iSeries field (which I see as Numeric(2,0) in SSIS) to a SQL 2005field declared as Decimal(2,0).Is there any reason you asked this specific question? Is there a known issue with that data type? I would appreciate your help Thanks, Srini

SSIS treats the presence of data by looking at the length of column from the data is fetched. The actual data length is different. Provided you are sure that no data is actually lost, the below technique can be used: Right click on the OLE DB Source Component & select "Show Advanced Editor..." Click on the "Input and Output Properties" tab Expand "OLE DB Source Output" Further Expand "Ouput Columns" Select "ACCOUNTID" In the right hand side pane that appears, in the "Common Properties", select "ErrorRowDisposposition". Drop down & select "RD_IgnoreFailure". Click "OK" & exeucte the task again. This will remove the data truncation error encountered above.

Srini Maguluri wrote: Phil, I came across you question 'ACCOUNTID isn't a numeric(p,s) field is it?' when a guy had the following issue. ******************** I have created a package that insert data from Iseries table to SQL 2005 database. I am using Microsoft OLEDB provider for DB2 and Native OLEDB for SQL2005 database as connector. I am getting following errors: [OLE DB Source [1]] Error: There was an error with output column "ACCOUNTID" (32) 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.". [OLE DB Source [1]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "ACCOUNTID" (32)" failed because error code 0xC0209072 occurred, and the error row disposition on "output column "ACCOUNTID" (32)" 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 "OLE DB Source" (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. Thanks, Biju Varughese ************************ Now I got the similar error when I tried to load data froman iSeries field (which I see as Numeric(2,0) in SSIS) to a SQL 2005field declared as Decimal(2,0).Is there any reason you asked this specific question? Is there a known issue with that data type? I would appreciate your help Thanks, Srini Yes, there is. The scale in SSIS needs to be greater than zero, at the moment. A bug has been filed, we'll see what happens. The best bet is to convert the NUMERIC(2,0) field in iSeries to a NUMERIC(3,1) or something like that in your source query. Then SSIS will be able to handle it by going into a DECIMAL(3,1) field (in my example).