SSIS Error
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
March 30th, 2007 4:45pm

Looks like the data types between your DB2 database and SSIS do not match for ACCOUNTID.
Free Windows Admin Tool Kit Click here and download it now
March 30th, 2007 4:51pm

ACCOUNTID isn't a numeric(p,s) field is it?
March 30th, 2007 4:52pm

It isa string data type
Free Windows Admin Tool Kit Click here and download it now
March 30th, 2007 4:54pm

Phil, Data type:string [DT_STR]
March 30th, 2007 4:59pm

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.
Free Windows Admin Tool Kit Click here and download it now
March 30th, 2007 5:16pm

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
March 30th, 2007 5:44pm

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.....
Free Windows Admin Tool Kit Click here and download it now
March 30th, 2007 5:58pm

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?
March 30th, 2007 6:28pm

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.
Free Windows Admin Tool Kit Click here and download it now
March 30th, 2007 6:33pm

And since you are converting ACCOUNTID to character data type, what is the original data type of ACCOUNTID?
March 30th, 2007 6:34pm

In Iseries it is Char with 16 length long
Free Windows Admin Tool Kit Click here and download it now
March 30th, 2007 6:40pm

One other thing to check... On your OLE DB source component, do you have AlwaysUseDefaultCodePage set to TRUE? That might help if not.
March 30th, 2007 6:49pm

It is already set to true. There must be otherpeople doing this.
Free Windows Admin Tool Kit Click here and download it now
March 30th, 2007 8:01pm

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.
March 30th, 2007 8:12pm

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?
Free Windows Admin Tool Kit Click here and download it now
March 30th, 2007 8:41pm

When I redirect to raw data source it works. but when I direct to differnet data source it gives me the errors.
March 30th, 2007 8:54pm

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?
Free Windows Admin Tool Kit Click here and download it now
March 30th, 2007 8:55pm

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.
March 30th, 2007 9:30pm

It is 16 Char long in destination table.
Free Windows Admin Tool Kit Click here and download it now
March 30th, 2007 9:51pm

Problem is the data in iseriesis defined as double byte char. So the conversation betwwen double to single is causing this error.
March 30th, 2007 10:43pm

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.
Free Windows Admin Tool Kit Click here and download it now
March 30th, 2007 10:45pm

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.
March 30th, 2007 10:47pm

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
Free Windows Admin Tool Kit Click here and download it now
June 5th, 2007 6:13pm

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).
June 13th, 2007 4:39pm

Hi, I also faced the similar issue while Extracting flat file, applying conversion and loading into DB. The problem was the output column data type was set as "byte stream [DT_BYTES]" automatically. To troubleshoot this kind of issue: 1. Open the Advance Editor for the control and check the DB type of Input and output column 2. And also you can add the dataviewer to see the data being passed from one control to another. Thanks, Pramod
Free Windows Admin Tool Kit Click here and download it now
December 2nd, 2010 8:24am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics