Troubleshooting Error Messages in SSIS

Troubleshooting is a necessary lesson during SSIS packages' development. I will jot down some error messages during SSIS developments and the way to work around them. Hope that it will help you find solutions faster. 

The rule of thumb of troubleshooting is to identify problematic components first and then find out error types. Breaking big problems into small pieces first and then QA for each small pieces. 

Most of times, error messages are clear. For example, when it says "The file xxx does not exist", you know it is time to check the file's existence. However, sometimes there are so many error messages automatically generated by SSIS that you may feel overwhelmed. You don't know where to start.  

Here common error messages are listed and when you are familiar with these, you definitely will find it much easier to locate the key info: the cause of problem. These key info in the following error messages as denoted in red.

1. Msg: An error occurred while attempting to perform a type cast. or The data conversion for column "DATECOLUMN" returned status value 2 and status text "The value could not be converted because of a potential loss of data."
• Solution: You need to locate the error component. If this is related to a type cast in Derived Column Transform, you need to check whether it is a legal type cast first. You can refer to the diagram for legal data conversion. If you still have no clue about errors, check for dates or SUBSTRING related issues such as whether the length and start_point are set right.
 
2. Msg: Data conversion failed. The data conversion for column "COLUMN1" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page."
• Solution: Check whether you have got truncation issue and whether the COLUMN1 in your source file are wider than expected.
 
3. Msg: There was an error with input column "COLUMN1" (1071) on input "OLE DB Destination Input" (773). The column status returned was: "The value violated the integrity constraints for the column."
• Solution: Check whether COLUMN1 has any constraints such as NOT NULL or unique and whether your source file has violated these constraints.
 
4. Msg: The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
• Solution: Check whether the source table has rows with the same values for your key column(s) that are defined in JOIN conditions for source table and target table.
 
5. Msg: It is illegal to call out while inside message filter.
• Solution: While in the script edit window, after coding, before saving and closing window, click build, then close window. The error won't appear.
 
6. Msg: Invalid object name 'dbo.Member'.
• Solution: Check whether the object 'dbo.Member' exists or not.
 
7. Msg: Failed to acquire connection "Conn_database". Connection may not be configured correctly or you may not have the right permissions on this connection. or The connection "{4D8DBB27-A124-42FE-AFB5-5F2866A64455}" is not found.
• Solution: Check whether connection of Conn_database has been configured correctly.
 
8. Msg: System.OverflowException: Arithmetic operation resulted in an overflow. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)...
• Solution: Check whether in the script you have defined some arithmetic operation that is not allowed zeros or empty inputs.
 
9. Msg: Error on Component:[FF_Move_File_ToArchive]: An error occurred with the following error message: "The process cannot access the file because it is being used by another process.".
• Solution: Check whether the file is opened by another application. Close it and try again.