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

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
SQLSearcherAsked:
Who is Participating?
 
SQLSearcherAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
SQLSearcher, you can close the question by yourself.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
SQLSearcherAuthor Commented:
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
 
SQLSearcherAuthor Commented:
Found problem with experts help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.