Solved

SSIS The value could not be converted because of a potential loss of data.

Posted on 2015-01-09
6
755 Views
Last Modified: 2016-02-15
Hello Experts Exchange
I have a SSIS package that has ran in the past but has stopped working today.  I have opened the package in BID's and have the following error.

SSIS package "C:\Users\pugrrock\documents\visual studio 2010\projects\LostTime_Bays1-3\LostTime_Bays1-3\B2Red563.dtsx" starting.
Information: 0x4004300A at Data Flow Task 1, SSIS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Task 1, SSIS.Pipeline: Validation phase is beginning.
Warning: 0x80049304 at Data Flow Task 1, SSIS.Pipeline: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available.  To resolve, run this package as an administrator, or on the system's console.
Information: 0x40043006 at Data Flow Task 1, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task 1, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Data Flow Task 1, SSIS.Pipeline: Execute phase is beginning.
Error: 0xC0202009 at Data Flow Task 1, Destination - MachineLosses_Bays1-3 [2]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Invalid character value for cast specification".
Error: 0xC020901C at Data Flow Task 1, Destination - MachineLosses_Bays1-3 [2]: There was an error with Destination - MachineLosses_Bays1-3.Inputs[Destination Input].Columns[Lost Minutes] on Destination - MachineLosses_Bays1-3.Inputs[Destination Input]. The column status returned was: "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at Data Flow Task 1, Destination - MachineLosses_Bays1-3 [2]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Destination - MachineLosses_Bays1-3.Inputs[Destination Input]" failed because error code 0xC0209077 occurred, and the error row disposition on "Destination - MachineLosses_Bays1-3.Inputs[Destination Input]" 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 Data Flow Task 1, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Destination - MachineLosses_Bays1-3" (2) failed with error code 0xC0209029 while processing input "Destination Input" (15). 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.
Information: 0x40043008 at Data Flow Task 1, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x4004300B at Data Flow Task 1, SSIS.Pipeline: "Destination - MachineLosses_Bays1-3" wrote 3 rows.
Information: 0x40043009 at Data Flow Task 1, SSIS.Pipeline: Cleanup phase is beginning.
Task failed: Data Flow Task 1
SSIS package "C:\Users\pugrrock\documents\visual studio 2010\projects\LostTime_Bays1-3\LostTime_Bays1-3\B2Red563.dtsx" finished: Failure.
The program '[4504] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).

Open in new window


I have checked the file for bad data and everything looks ok.

I'm not sure what to try next, can anyone offer a suggestion on what to try please.

Regards

SQLSearcher
SSIS-run-error.jpg
0
Comment
Question by:SQLSearcher
  • 3
  • 2
6 Comments
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points
ID: 40539901
Double check the data. By the error messages "Invalid character value for cast specification" and "There was an error with Destination - MachineLosses_Bays1-3.Inputs[Destination Input].Columns[Lost Minutes]" I would say to focus on the column. Should be some non numeric character.
0
 

Accepted Solution

by:
SQLSearcher earned 0 total points
ID: 40539936
I found the bad data, by copying the worksheet to a new blank worksheet using just paste the values, the bad record was blank.  Good records were zero instead of blank.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40539958
SQLSearcher, you can close the question by yourself.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 65

Expert Comment

by:Jim Horn
ID: 40540210
>the bad record was blank.  Good records were zero instead of blank.
A common practice to deal with these scanarios is to use an SSIS data flow task to import your data into a table that is all varchar() columns.  Then write T-SQL to validate that dates are dates, numbers are numbers, stuff that should not be blank is not blank, you get the idea.  Then import everything passing these validations into your final destination table, and import everything that fails validation into a table to use for troubleshooting.

More steps, and 2x the data pumps, but it beats a feed erroring out at 2am for something silly that can be addressed later in the day.
0
 

Author Comment

by:SQLSearcher
ID: 40546630
Hello Jim
Do you have any example SSIS packages you can give me so I can see a Data Flow task exporting data from Excel to SQL Server to Varchar data types please?

Regards

SQLSearcher
0
 

Author Closing Comment

by:SQLSearcher
ID: 40548543
Found problem with experts help.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

757 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

18 Experts available now in Live!

Get 1:1 Help Now