April 9, 2008 at 11:29 am
The package imports data from a CSV file, the columns are mapped and sent to a table in the db. It works fine on 2000 but I get errors after I migrated it to an SSIS package. Is there a 2005 issue?
Below are a list of errors. thanks
There are more warnings but just wanted to list 2 so it would be less reading. Below the warnings are the errors.
Warning: 0x802092A7 at Copy Data from RP_charge to WBPTEST dbo RP_CHARGE Task, OLE DB Destination [106]: Truncation may occur due to inserting data from data flow column "Col019" with a length of 255 to database column "RATE_DESC" with a length of 25.
Warning: 0x802092A7 at Copy Data from RP_charge to WBPTEST dbo RP_CHARGE Task, OLE DB Destination [106]: Truncation may occur due to inserting data from data flow column "Col020" with a length of 255 to database column "SERVICE" with a length of 5.
Information: 0x40043006 at Copy Data from RP_charge to WBPTEST dbo RP_CHARGE Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Copy Data from RP_charge to WBPTEST dbo RP_CHARGE Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Copy Data from RP_charge to WBPTEST dbo RP_CHARGE Task, Flat File Source [1]: The processing of file "T:\WBP\RP_charge.csv" has started.
Information: 0x4004300C at Copy Data from RP_charge to WBPTEST dbo RP_CHARGE Task, DTS.Pipeline: Execute phase is beginning.
Error: 0xC0202009 at Copy Data from RP_charge to WBPTEST dbo RP_CHARGE Task, OLE DB Destination [106]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Invalid character value for cast specification.".
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Invalid character value for cast specification.".
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Invalid character value for cast specification.".
Error: 0xC020901C at Copy Data from RP_charge to WBPTEST dbo RP_CHARGE Task, OLE DB Destination [106]: There was an error with input column "Col013" (173) on input "OLE DB Destination Input" (119). The column status returned was: "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at Copy Data from RP_charge to WBPTEST dbo RP_CHARGE Task, OLE DB Destination [106]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (119)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "OLE DB Destination Input" (119)" 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.
Error: 0xC0047022 at Copy Data from RP_charge to WBPTEST dbo RP_CHARGE Task, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (106) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Copy Data from RP_charge to WBPTEST dbo RP_CHARGE Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC02020C4 at Copy Data from RP_charge to WBPTEST dbo RP_CHARGE Task, Flat File Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
Error: 0xC0047038 at Copy Data from RP_charge to WBPTEST dbo RP_CHARGE Task, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC02020C4. 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.
Error: 0xC0047021 at Copy Data from RP_charge to WBPTEST dbo RP_CHARGE Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
Information: 0x40043008 at Copy Data from RP_charge to WBPTEST dbo RP_CHARGE Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Copy Data from RP_charge to WBPTEST dbo RP_CHARGE Task, Flat File Source [1]: The processing of file "T:\WBP\RP_charge.csv" has ended.
Information: 0x402090DF at Copy Data from RP_charge to WBPTEST dbo RP_CHARGE Task, OLE DB Destination [106]: The final commit for the data insertion has started.
Information: 0x402090E0 at Copy Data from RP_charge to WBPTEST dbo RP_CHARGE Task, OLE DB Destination [106]: The final commit for the data insertion has ended.
Information: 0x40043009 at Copy Data from RP_charge to WBPTEST dbo RP_CHARGE Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Copy Data from RP_charge to WBPTEST dbo RP_CHARGE Task, DTS.Pipeline: "component "OLE DB Destination" (106)" wrote 0 rows.
Task failed: Copy Data from RP_charge to WBPTEST dbo RP_CHARGE Task
Warning: 0x80019002 at dts_wbpprod_rp_charge: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (8) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "dts_wbpprod_rp_charge.dtsx" finished: Failure.
April 9, 2008 at 12:24 pm
Your error messages are indicating that the meta-data in your source component of your data flow does not match the data in the spreadsheet.
This could be because you have some text that is longer in data that is past the first couple of hundred lines in the spreadsheet, it could be you have a character that cannot be converted ("A" going into a numeric field).
You need to make sure your data source component has the correct external meta-data.
One good way of doing this is to create a stub file with one or two records in it that correctly represent the data types you want and use this file for setting up the package.
April 9, 2008 at 12:42 pm
Where do I modify the column type and size metadata? In the exernal or output columns of the flat file source or on the oledb destination? thanks a lot!
April 9, 2008 at 12:53 pm
Keith - A few options to look at:
- start by looking at the advanced properties of your source file definition. You will see definitions/assumptions as to how long each column is supposed to be. If those were defaulted to 255, even if the "real" data only has 25, that could cause truncation error to pop up.
- if the fields are padded, you may find that it's okay to actually truncate, in which case you can configure the "error output" of your various steps to ignore truncation errors on some or all fields.
I've found that SSIS doesn't do a lot of thinking when it's trying to guess field sizes, so it makes a fair amount of mistakes.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 10, 2008 at 6:42 am
Thanks for the replies, it got me looking at other things. I think their were several issues.
1. I had to recreate the job from scratch in ssis instead of using the converted one from a dts. not sure why?
2. I had to use a " as a text delimiter. This was one field that had quotes and a comma, so without that " as a delimiter it was thinking that that one field was 2 fields.
3. When setting up the flat file source, I needed to set the columns types and widths.
Thanks for taking the time to answer these forums. It's greatly appreciated!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply