Solved

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

Posted on 2015-01-09
6
802 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 46

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 46

Expert Comment

by:Vitor Montalvão
ID: 40539958
SQLSearcher, you can close the question by yourself.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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…

912 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

17 Experts available now in Live!

Get 1:1 Help Now