When using SSIS as tools to loading files, you usually can get a very clear error message that indicates what is going wrong. You can tell which column is wrong from ErrorColumn and for which reason the column brought failure from “ErrorCode – Description”.
However, when loading a source file that is not formatted as expected, if you have got an error output with "No Status is available" as ErrorCode and “0” as ErrorColumn as shown below, what do you feel?
Do you feel like lost in darkness? Somewhat …
Here is my recent experience in helping out troubleshooting file loading problem. Since there is no clue, all I can do is to check all constraints on all columns for potential trouble-makers.
Finally, it turned out that the trouble-makers is one of the obsolete columns that used to be NOT NULL, but no more input at the current loading. The solution is easy. You need to allow that column to have NULL values.
During the research for the clue, I uncovered two helpful resources to decipher SSIS error codes:
- Integration Services Error and Message Reference
- header file dtsmsg.h
The header file dtsmsg.h is under the folder
C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Include (for SQL Server 2012)
The two resources covered the five kinds of messages as shown. The online one is in table form and easier to follow, while the header file is more precise and detailed in technical terms.
So next time, hope you will feel more confident when you get SSIS error messages!
No comments:
Post a Comment