Press "Enter" to skip to content

Category: Integration Services

REPLACENULL in SSIS versus DT_DBTIMESTAMP2 Columns

Published 2025-02-11 by Kevin Feasel

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.

Leave a Comment

SSIS: Operating Completed with Problems Logged

Published 2025-02-03 by Kevin Feasel

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.

Leave a Comment

Case-Sensitive Elements in SQL Server Integration Services

Published 2025-01-30 by Kevin Feasel

Andy Brownsword has a list:

Case 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 closed

SSIS Deprecating Microsoft Connector for Oracle

Published 2025-01-22 by Kevin Feasel

Debbi 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 closed

Temp Tables in SSIS Data Sources

Published 2024-12-02 by Kevin Feasel

Andy 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 closed

Working with Always Encrypted Data in SSIS

Published 2024-09-17 by Kevin Feasel

Rod Edwards continues a series on Always Encrypted:

So now, lets see how it plays with another one of those common toolsets that you may use alongside your Encrypted data. In this post, i’ll be talking about accessing and importing data using SSIS, nothing fancy, just reading data from an Excel sheet, and piping into our Always Encrypted table, encrypting as we go.

I’m not saying to use Excel for housing confidential data either!… as no one does that…oh no, not anywhere, ever….</sarcasm>.

As previously, this focuses on using Azure Key Vault for securing Encryption keys required.

Considering that all corporate data is in Excel someplace (some variant of which may eventually become Feasel’s Second Law), of course that sensitive and confidential data will be in a plain Excel file that people e-mail around.

Comments closed

Archiving Files with SSIS

Published 2024-08-02 by Kevin Feasel

Andy Brownsword shuffles things around:

Integration workflows will typically involve handling files. As part of that we’ll need to move them around, for example moving into an archive directory.

The File System Task component can be used for a variety of operations such as creating directories or copying files. Here we’re going to look at two specific operations to help shuffle files around: Move and Rename.

Read on to see how it works and how you can turn this into an archival process for files.

Comments closed

Losing .NET Code with SSIS 2016 Package Deployment

Published 2024-08-02 by Kevin Feasel

Andy Leonard goes on a quest:

…when one of our earliest SSIS framework customers reached out to share – in the very nicest way possible – that Fail Application on Package Failure in our SSIS framework was “not working.” My response was, “Great Scott!”

The customer is running SQL Server 2016. That’s fine for EDNA’s SSIS frameworks because we have framework versions that work with several versions of SSIS and SQL Server from 2012 forward. I maintain a collection of virtual servers built using the tools available for each version, including the operating systems and versions of Visual Studio, SSIS, and SQL Server.

Read on for the context, the story, and what Andy was able to do about it.

Comments closed

Reading Pipe-Delimited Files in SSIS

Published 2024-07-19 by Kevin Feasel

Greg Low does a bit of parsing:

There was a question on the Q&A forums today, asking how to read data using SSIS, when it’s in this format:

|Col1| |Col2|Col3|Col|
|101| |A|21|DC|

One of the concerns was that there was a leading pipe. This is not a problem. When you have data like that, and you set | as the delimiter, because there are 6 delimiters, then there are 7 columns output. 

Read on for Greg’s answer. This is a big part of why I recommend people to use Parquet (or ORC, if you’re an iconoclast like I am): not needing to deal with whatever silly, arbitrary file formats people come up with.

Comments closed