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.
 

Unveal Difference Between ON and WHERE Clause When Used With Joins In SQL Server

When you write sql queries, you may notice that sometimes query results are different when you use ON clause from using WHERE clause in Joins. Or you may encounter a question: is there any difference between ON and WHERE clause when used with Joins?

Now here is the answer: 
  1) For inner joins, there is no difference between the WHERE clause and ON clause. Even for performance consideration, they are the same.
  2) For left joins, there will be difference between the WHERE clause and ON clause sometimes.

This article on Codeproject has given very good examples to demonstrate the above conclusion. In that article, Case 5 uses ON clause with Left Joins while Case 6 uses WHERE clause with Left Joins. Case 5 returns three more rows than Case 6. Moreover all these extra three rows have DeptID as NULL.

So you may ask why will be difference for left joins sometimes?
Let us first take a look at definition of Where clause on MSDN: WHERE search_condition is to define the condition to be met for the rows to be returned. That is to say, WHERE clause returns rows for which the predicate/condition evaluates to TRUE. For those rows that have a NULL, WHERE clause evaluates to UNKNOWN. That's why in left outer joins, WHERE clause will exclude all rows related to DeptId IS NULL. Now you should tell why Case 5 have those three more rows, right?

Microsoft's SQL Server Tutorial / Books Online Collection

Here is a summary list of BOL (Books Online) for SQL Server 2008 R2 and 2012. Hope it will help you to locate your resourses more quickly:)

SQL Server Books Online for SQL Server 2008 R2 covers the topic from data management to business intelligent solutions, including

• Database Engine
• Analysis Services - Multidimensional Data
• Analysis Services - Data Mining
• Integration Services
• Master Data Services
• Replication
• Reporting Services
• SharePoint Integration 
• Service Broker 

SQL Server Books Online for SQL Server 2012 includes

• Database Engine  
• Data Quality Services
• Analysis Services 
• Integration Services 
• Master Data Services 
• Replication  
• Reporting Services   

Note that the major changes for SQL Server 2012 are addition of DQS and removal of Sharepoint integration. DQS is a knowledge-driven data cleansing solution that can be used together with SSIS and MDS.

On the other side, SQL Server Developer Reference will give you more tutorials to explore. It is available online at MSDN and TechNet. 

If you still feel that is not enough, go to Microsoft's learning center for more. Don't forget to click tabs to choose the right version among four available versions: 2012, 2008R2, 2008, and 2005. This site provides combination of TechNet and MSDN resources.