Solved

SSIS Truncation Error

Posted on 2013-10-29
5
2,265 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 66

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 66

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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

636 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