Solved

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

Posted on 2015-01-09
6
856 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 47

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 47

Expert Comment

by:Vitor Montalvão
ID: 40539958
SQLSearcher, you can close the question by yourself.
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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…

809 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