Solved

SSIS Truncation Error

Posted on 2013-10-29
5
2,065 Views
Last Modified: 2016-02-11
Hello, experts.
I ran into this trunctation issue when I tried to execute a Data Flow Task.
I have a SQL Statement that will populate the data, and it needs to go into flat file.
However, when I tried to do so I get following error

"SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Data Flow Component, SSIS.Pipeline: Validation phase is beginning.
Warning: 0x802092A8 at Data Flow Component, Ascencion MIF Extract [248]: Truncation may occur due to retrieving data from database column "MIF Manufacturer" with a length of 255 to data flow column "MIF Manufacturer" with a length of 100.
Warning: 0x802092A8 at Data Flow Component, Ascencion MIF Extract [248]: Truncation may occur due to retrieving data from database column "MIF Mfgr Item Number" with a length of 255 to data flow column "MIF Mfgr Item Number" with a length of 80.
Warning: 0x802092A8 at Data Flow Component, Ascencion MIF Extract [248]: Truncation may occur due to retrieving data from database column "MIF Packaging String" with a length of 1024 to data flow column "MIF Packaging String" with a length of 32.
Warning: 0x802092A8 at Data Flow Component, Ascencion MIF Extract [248]: Truncation may occur due to retrieving data from database column "UNSPSC DESCRIPTION" with a length of 1024 to data flow column "UNSPSC DESCRIPTION" with a length of 5.
Warning: 0x802092A8 at Data Flow Component, Ascencion MIF Extract [248]: Truncation may occur due to retrieving data from database column "BP HCPCS Code" with a length of 10 to data flow column "BP HCPCS Code" with a length of 5.
Warning: 0x802092A8 at Data Flow Component, Ascencion MIF Extract [248]: Truncation may occur due to retrieving data from database column "CIF Item Number" with a length of 50 to data flow column "CIF Item Number" with a length of 18.
Warning: 0x802092A8 at Data Flow Component, Ascencion MIF Extract [248]: Truncation may occur due to retrieving data from database column "CIF Packaging Description" with a length of 50 to data flow column "CIF Packaging Description" with a length of 23.
Warning: 0x802092A8 at Data Flow Component, Ascencion MIF Extract [248]: Truncation may occur due to retrieving data from database column "CIF Mfgr Item Number" with a length of 50 to data flow column "CIF Mfgr Item Number" with a length of 25.
Warning: 0x802092A8 at Data Flow Component, Ascencion MIF Extract [248]: Truncation may occur due to retrieving data from database column "CIF Vendor" with a length of 50 to data flow column "CIF Vendor" with a length of 40.
Warning: 0x802092A8 at Data Flow Component, Ascencion MIF Extract [248]: Truncation may occur due to retrieving data from database column "AH Common Charge Number" with a length of 30 to data flow column "AH Common Charge Number" with a length of 11.
Warning: 0x802092A8 at Data Flow Component, Ascencion MIF Extract [248]: Truncation may occur due to retrieving data from database column "AH Common HCPCS Code" with a length of 10 to data flow column "AH Common HCPCS Code" with a length of 5.
Warning: 0x800470C8 at Data Flow Component, Flat File Destination [329]: The external columns for component "Flat File Destination" (329) are out of synchronization with the data source columns. The external column "PDUID" needs to be updated.
The external column "MIF Description Short" needs to be updated.
The external column "MIF Manufacturer" needs to be updated.
The external column "MIF Mfgr Item Number" needs to be updated.
The external column "MIF Packaging String" needs to be updated.
The external column "MIF UNSPSC Code" needs to be updated.
The external column "UNSPSC DESCRIPTION" needs to be updated.
The external column "BP HCPCS Code" needs to be updated.
The external column "BP Revenue Code" needs to be updated.
The external column "BP Non-Covered Medicare Code" needs to be updated.
The external column "CIF Item Number" needs to be updated.
The external column "CIF Packaging Description" needs to be updated.
The external column "CIF Manufacturer" needs to be updated.
The external column "CIF Mfgr Item Number" needs to be updated.
The external column "CIF Vendor" needs to be updated.
The external column "CIF Vendor Item Number" needs to be updated.
The external column "AH Common Charge Number" needs to be updated.
The external column "AH Common DESCRIPTION" needs to be updated.
The external column "AH Common HCPCS Code" needs to be updated.
The external column "AH Common Revenue Code" needs to be updated.
The external column "AH Common Non-Covered Medicare Code" needs to be updated.
Information: 0x40043006 at Data Flow Component, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Component, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Component, Flat File Destination [329]: The processing of file "\\Osifile1\ops\46543b_Ascension 2010-2011 Project Symphony\MIF Files\Ascension_Mif_Extract_2013-10-29.txt" has started.
Information: 0x4004300C at Data Flow Component, SSIS.Pipeline: Execute phase is beginning.
Error: 0xC020901C at Data Flow Component, Ascencion MIF Extract [248]: There was an error with output column "UNSPSC DESCRIPTION" (285) on output "OLE DB Source Output" (258). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Data Flow Component, Ascencion MIF Extract [248]: The "output column "UNSPSC DESCRIPTION" (285)" failed because truncation occurred, and the truncation row disposition on "output column "UNSPSC DESCRIPTION" (285)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC0047038 at Data Flow Component, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Ascencion MIF Extract" (248) returned error code 0xC020902A.  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.
Information: 0x40043008 at Data Flow Component, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Data Flow Component, Flat File Destination [329]: The processing of file "\\Osifile1\ops\46543b_Ascension 2010-2011 Project Symphony\MIF Files\Ascension_Mif_Extract_2013-10-29.txt" has ended.
Information: 0x4004300B at Data Flow Component, SSIS.Pipeline: "component "Flat File Destination" (329)" wrote 0 rows.
Information: 0x40043009 at Data Flow Component, SSIS.Pipeline: Cleanup phase is beginning.
Task failed: Data Flow Component
SSIS package "Package.dtsx" finished: Success.
The program '[5772] Package.dtsx: DTS' has exited with code 0 (0x0).
"

In my SQL statement, I did used derived columns so that I specified the column length.

I've heard from my co-workers that SSIS package processes things like Access does where it looks at first several rows then determine what the maximun length is.

Can you experts help me to figure out how to get around this?

Thank you in advance!
0
Comment
Question by:kmc10314
  • 2
  • 2
5 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 39610111
Just for kicks and giggles, go to your Derived Column task and do a right-click:Show Advanced Editor, Input and Output Properties tab.  

Then in the left side treeview expand Derived Column Output >> Output Columns.  Then click on each column and see if you can override the length.
0
 
LVL 5

Assisted Solution

by:dannygonzalez09
dannygonzalez09 earned 250 total points
ID: 39610255
Did you try changing/increasing the length of the columns ? If not, change the length of your columns in the derived column and map them correctly in the destination task... that should do it
0
 

Author Comment

by:kmc10314
ID: 39690821
I've requested that this question be deleted for the following reason:

I couldn't not find solution and no one seemed to be responding this question....
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39690822
kmc10314 - There are two expert comments here, and no follow-up from you, so I don't see how 'no one seemed to be responding this question'.

Any chance you can respond to the above comments?

Until this happens, I am cancelling the delete request.
0
 

Author Comment

by:kmc10314
ID: 39690903
Ah sorry..... I'm having a typical I did solve the thing myself....
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and X…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now