A Fine Slice Of SQL Server
Andy Brownsword doesn’t want any blockers:
We have a platter of transformations to use when constructing data flows in SSIS packages. Not all transformations are equal though, and some can catch us out with performance impact as data volumes scale.
In this post we’ll look at blocking transformations which can trip us up if we’re not careful with them (or avoid them).
Click through for some examples of blocking transformations, as well as some tips on how to manage them.
Leave a CommentAndy Brownsword stores some context:
Package configuration allows us to retrieve variable values from a variety of sources. We recently looked at storing the values in a SQL database.
We can elevate the database configuration a step further by using system level Environment Variables. In this post we’ll walk through that process.
Click through to see how.
Leave a CommentAndy Brownsword has fun with file formats:
When ingesting files in SSIS via Flat File Connections, a consistent format is key. Sometimes that isn’t the case. Here we’ll look at an example where the carriage return (
CR,\r) may or may not be included in the file.
Pepperidge Farms remembers back in the day when Windows, MacOS, and Linux (or any flavor of UNIX for that matter) each had a different way of ending a line: line feed, carriage return, or both. And of course most tools weren’t smart enough to figure out which your particular text file followed and display it correctly.
Comments closedAndy 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 closedUsing 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 closedSlava 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 closedCase sensitivity in data can trip us up unknowingly and Integration Services is no exception. Depending on the components we’re using we may notice different behaviour.
Without understanding the way case sensitivity is treated within SSIS packages, we may see unexpected results with string comparisons.
Let’s clear things up and dive into a few examples to see when they behave differently.
Click through for three examples. One more that I’d add is Script components, which are C# or VB. Both of the languages are case sensitive, and string comparison in .NET is case sensitive unless you set StringComparison to one of the options that ignore case.
Comments closedDebbi Lyons has an announcement:
In July 2025, Microsoft will discontinue support for the Microsoft Connector for Oracle in SQL Server Integration Services (SSIS). This blog provides essential details to help customers prepare for this change in advance.
The Microsoft Connector for Oracle enables data export from and import into Oracle databases within an SSIS package. This feature, available in Enterprise editions of SQL Server 2019 and 2022, will remain functional for the lifecycle of the SQL Server product. However, support for this feature will officially end on July 4, 2025. With the deprecation, future product releases will provide no further bug fixes. Additionally, it will not be supported from SQL Server 2025 and onwards.
See, and people have told us there hasn’t been anything happening in SSIS since 2016!
The alternative of using ADO.NET reminds me of when Microsoft tried to take OLEDB out of Integration Services and got the pushback that no, we really don’t want to move from a fast component to a slow component. I would expect much less pushback on this one, simply because I doubt many people are using SSIS to ferry around data in Oracle.
Comments closedAndy Brownsword disappears in a flash:
When handing data we can make use of temporary tables to aid with separation or performance. However, they don’t always play nice with Integration Services packages.
If we set a source to call a procedure returning the contents of a temporary table we’ll see an error like below:
Read on for three options. It’s been a while, but I vaguely recall that you can use global temp tables (such as ##Results) and it will work, as those persist and are available to all sessions so long as there is some open session using them.
Comments closedAndy Brownsword fixes a pair of issues:
Last week we looked at using the Azure Feature Pack for SSIS to interface with Azure Storage. Today we’re going to solve a couple of the common issues I’ve seen when using this pack.
Read on for the problem and solution for each.
Comments closed