Truncation error when importing from ODBC to flat file

I am having problems reading from and ODBC conncetion from Oracle RDB into SSIS. I am using a DataReader source which uses a ADO.NET odbc connection to an oracle RDB database. I am having that write to a flat file. When I read integers from the source, it works just fine. When I read character data (char(48) for example), it gives me trucation errors. Is the DataReader source capable of reading char data from an odbc connection

Here is the errors I receive:

SSIS package "Package.dtsx" starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x402090DC at Data Flow Task, Flat File Destination [792]: The processing of file "D:\Documents and Settings\Administrator\Desktop\test.txt" has started.

Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.

Error: 0xC020902A at Data Flow Task, DataReader Source [575]: The "component "DataReader Source" (575)" failed because truncation occurred, and the truncation row disposition on "output column "REPORT_PART_NUMBER" (789)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Error: 0xC02090F5 at Data Flow Task, DataReader Source [575]: The component "DataReader Source" (575) was unable to process the data.

Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "DataReader Source" (575) returned error code 0xC02090F5. 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.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x402090DD at Data Flow Task, Flat File Destination [792]: The processing of file "D:\Documents and Settings\Administrator\Desktop\test.txt" has ended.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Flat File Destination" (792)" wrote 0 rows.

Task failed: Data Flow Task

Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (6) 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 "Package.dtsx" finished: Failure.

Thanks,

Alex

Answer this question

Truncation error when importing from ODBC to flat file

Rupert Rawnsley

The problem is the next transform or destination in the dataflow.

Look at the length of the input column for the column that's failing on the next component in the flow.

What is the size of that column
ReneeC
I am reading an int (part_id) and a char(48) (report_part_number) from an oracle rdb database using an oracle rdb odbc driver. I am using a DataReader Source into a Flat File Destination.

DataReader Source

External Columns: Part_id - DT_I4 Report_part_number - DT_STR length=48

Output Columns: Part_id - DT_I4 Report_part_number - DT_WSTR length=0 (it will not let me change these settings)

Flat File Destination

External Columns: Part_id - DT_I4 Report_part_number - DT_WSTR length=255

Input Columns: Part_id - DT_I4 Report_part_number - DT_WSTR

Would it help if I posted the code for this

Thanks,

Alex
bitbonk
I am not aware of a hot fix or work around.
Philip Coupar
Hi Alex,

Do you have any updates on thie I am stuck with the same problem. I am trying to import data from oracle RDB into SQL Server.

Thanks,
SK
GT_MSDN
Well, just check if their collation is fine, few collation tends to take double space then default. And i hope u have checked the length of csv file's column too
Abhishek_SE
This odbc driver worked fine in sql server 2000. Has something changed in sql server 2005 to make it obsolete
smhaig

You've specified that if there is a truncation between the source (ODBC) and output that the source should fail and terminate the dataflow task. If you don't mind that there will be a truncation, change the error disposition to ignore truncations.

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/extran9/html/c61667b4-25cb-4d45-a52f-a733e32863f4.htm

Kirk Haselden
Author "SQL Server Integration Services"
anita punjabi
Alex,
Thanks for the quick update. I am not knowledgable about Open VMS and OCI Service. But I can definitely pass on this information to the DBA.

Also, do you know whether there is a hot fix from MS or are a work around like using Data Conversion or Derived Column transformation.

Thanks,
SK
Jerrie Pelser
We had to start an OCI Service in SQL Services on our VMS node. On the SQL Server side we connected to this OCI service using an OLE DB provider for Oracle. In other words, we had to make our Rdb database look like an Oracle database to the client.
tdcntt
Alex,
Could you please elaborate on the whats needs to be downloaded and what to install. We are uisng Windows 2003 Server and RDB 7.1 with SQL SEver 2005.

Thanks,
SK
Hector de la Rosa M.
When I change it to ignore truncation errors it will execute, but the character data comes across empty. How do I need to configure the DataReader Source to read a char(48)

Thanks,

Alex
Truncation error when importing from ODBC to flat file

Answer this question