Press "Enter" to skip to content

Category: Error Handling

Debugging SSIS Packages

Andy Brownsword wrote code with an error in it:

When constructing or investigating a SSIS package we can run into a variety of issues. To help resolve there are various techniques which can be used to troubleshoot the package.

Whilst we have the Progress tab for the package to tell us what’s happened during execution, it’s usually more effective to debug packages in flight. Below we’ll look at a few ways we can achieve this:

Never having written code that doesn’t work perfectly the first time, this post is, of course, merely academic for all of us perfect developers. But for those people who may have let a little something sneak in or have to deal with the errors that others have seeded into our beautiful programs, Andy provides three separate techniques for troubleshooting packages in flight.

Comments closed

Step Outputs to Help Troubleshoot Failed SQL Agent Jobs

Jim Evans gives us a reminder:

When troubleshooting SQL Agent jobs, often the Job history output is truncated or poorly formatted, making it hard to read. This is especially true when calling SSIS Packages, running jobs like DBCC CheckDB or when running T-SQL code that returns a lot of output. Are there options to get more readable Job output to aid in troubleshooting?

There are a few settings here that we can use to make troubleshooting SQL Agent jobs a little bit easier. In addition to these, it’s also a good idea to retain more history for longer, especially if you’re not in a position to track those job outputs each day.

Comments closed

Error Code 0x851A0043 on SQL Server 2022 Updates

Ben Johnston runs into an error:

I’ve been working in the Azure space for the last few years and less with full server installations. Azure leaves file locations out of my control, but I recently had a client upgrading an on-prem server to SQL 2022.It was a fresh installation on a new VM. I installed SQL, restored all the data, then I moved files to their optimal locations. This was a fresh installation of SQL Server and I wanted to move all of the data files, log files and tempdb files to their own drives. I also wanted to move the system databases. The documentation cautions that cumulative updates (CU) could fail if a specific registry key isn’t updated after moving the master files, so I wanted to test this before I spent too much time configuring the server.

An excerpt of that warning follows here.

Click through for the warning, as well as how to fix the problem.

Comments closed

REPLACENULL in SSIS versus DT_DBTIMESTAMP2 Columns

Slava Murygin notes an error:

 Using the “REPLACENULL” functionality frequently in the “Derived Column” component, the “Conditional Split” component, and other places in SSIS where formulas can be applied is common.

However, I recently encountered an issue with the “DT_DBTIMESTAMP2” data type.

The following formula produced an error:

REPLACENULL(TestDt, (DT_DBTIMESTAMP2,7)”1900-01-01 00:00:00.0000000″)

Error: 0xC020902A at Test Transformation, Derived Column [2]: The “Derived Column” failed because truncation occurred, and the truncation row disposition on “Derived Column.Outputs[Derived Column Output].Columns[TestDt]” specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Read on for an explanation and two alternatives.

Comments closed

Handling Errors in Apache Flink Apps

Alexis Tekin and Jeremy Ber handle an error:

Data streaming applications continuously process incoming data, much like a never-ending query against a database. Unlike traditional database queries where you request data one time and receive a single response, streaming data applications constantly receive new data in real time. This introduces some complexity, particularly around error handling. This post discusses the strategies for handling errors in Apache Flink applications. However, the general principles discussed here apply to stream processing applications at large.

Read on to see how this all works when you’re hosting a Flink application. This directly relates to Flink applications that live in AWS, though very little in the article is AWS-specific.

Comments closed

The Power of an Error Message

Joey D’Antoni shares some thoughts on error messages:

I’ve been working in technology for entirely too long—but one of the first, and most important lessons I learned was try to identify the correct error message, in the right log file, and then it can be generally trivial to resolve that error message. The challenge to forever and ever always has been “finding the right error message”, as what typically happens is that once there is one failure in a computer program, there is a series of cascading errors, which makes it harder to identify the right error message that you need to copy and paste into google/stack overflow/chatgpt/copilot.

Read on for more. I think it’s easy for us to rag on bad error messages, though even opaque error codes can still be reasonable if there’s a comprehensive guide. This was a classic problem with blue screens: oh, I have an error code 0x2378f83f. So what does 0x2378f83f mean? Dunno, nobody else on the internet has apparently ever had that code. And Joey also brings up my favorite error message to rag on: “String or binary data would be truncated.” Sadly, although they did finally add column name and message text to the error, they never fixed the most galling mistake in that error message: the passive voice.

Comments closed

Understanding Error Severity Levels 19 through 25 in SQL Server

Steve Stedman handles an error:

SQL Server uses error severity levels to indicate the nature and seriousness of an error. These severity levels range from 0 to 25, with each level indicating a different type of issue, from informational messages to catastrophic failures. In this post, we’ll focus on severity levels 19 through 25, which are typically the most critical errors that can occur in SQL Server.

Fortunately, given how devastating they are, severity level 21 and higher are extremely rare in my experience.

Comments closed

SSIS: Operating Completed with Problems Logged

Slava Murygin uses Integration Services to process an Analysis Services cube:

If you process SSAS cubes via SSIS packages you might notice a weird message like “Full Processing:Warning: Server: Operation completed with XXX problems logged.

How you can get that message (if you have that problem):

Slava notes that the simple solution doesn’t actually work, as both services decide not to store that information. But Slava does provide a solution to the problem.

Comments closed

Partitioned Tables and Indexes in PostgreSQL

Hettie Dombrovskaya runs into an error:

Here is a story. When anyone gives a talk about partitions, they always bring up an example of archiving: there is a partitioned table, and you only keep “current” partitions, whatever “current” means in that context, and after two weeks or a month, or whatever interval works for you, you detach the oldest partition from the “current” table and attach it to the “archived” table, so that the data is still available when you need it, but it does not slow down your “current” queries.

So here is Hettie confidently suggesting that a customer implement this technique to avoid querying a terabyte-plus-size table. A customer happily agrees, and life is great until one day, an archiving job reports an error of a “name already exists” for an index name.

Read on to learn why.

Comments closed