Solved

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

Posted on 2015-01-09
6
900 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 48

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 48

Expert Comment

by:Vitor Montalvão
ID: 40539958
SQLSearcher, you can close the question by yourself.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

856 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