Press "Enter" to skip to content

Category: Integration Services

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

SSIS Extensions in Visual Studio

Published 2024-06-18 by Kevin Feasel

Andy Leonard fills us in on what’s happening in the world of Integration Services:

I’m not sure why the decision was made to “fork” development and maintenance of the Integration Services extension for Visual Studio 2022. Based upon experience with SSIS, Visual Studio, and the Microsoft SSIS team, I am confident there’s a very good reason. As one can glean from my appointment screenshot above, I save the links to both pages. The links are:

SSIS Pre-2022: https://marketplace.visualstudio.com/items?itemName=SSIS.SqlServerIntegrationServicesProjects&ssr=false#overview
SSIS 2022: https://marketplace.visualstudio.com/items?itemName=SSIS.MicrosoftDataToolsIntegrationServices

Read on to see what’s new in both of those projects.

Comments closed

Saving Sensitive Parameters in SSIS Configurations

Published 2024-06-14 by Kevin Feasel

Andy Brownsword doesn’t just leave passwords in plaintext:

Configuring SSIS projects or packages can necessitate parametering information which may include sensitive values such as authentication details. Parameters are stored as plain text in the database by default. We’ll demonstrate how to protect these values using Sensitive parameters.

Read on to learn how to make an SSIS project parameter sensitive, as well as how to use them afterward.

Comments closed

Environment Variables in SSIS

Published 2024-06-06 by Kevin Feasel

Andy Brownsword continues a series on SSIS:

Yep it’s more SSIS again this week. Here we’ll be looking at using Environment configuration within the SSIS catalog. This allows sets of parameters to be defined and used across multiple projects and packages which share common values.

This approach can either be used as a central point for configuration, or you could use multiple configurations for the same packages.

Read on for some examples of how you might use them, as well as the process to create one.

Comments closed

Using Project Configuration for SSIS Package Deployment

Published 2024-05-23 by Kevin Feasel

Andy Brownsword shows us the brand new (well, okay, 12 years old) deployment model for Integration Services:

Last week we looked at configuring SSIS packages using package configuration. This week we’ll look at another approach for configuration: Project Configuration

Project Configuration is the standard way to configure projects. This took over from the legacy approach which we looked at last week. This method allows us to share common parameters and connections across an entire project to help managing a number of packages more efficient.

Read on to see how it works.

Comments closed

A Primer on SSIS Package Deployment

Published 2024-05-15 by Kevin Feasel

Andy Brownsword gives us a blast from the past:

Configurations for Integration Services packages allow us to tailor their execution without needing to redeploy. There are two main ways to manage these configurations – Package Configuration and Project Configuration. In this post we’ll look at the Package Configuration approach.

Package deployment was the original approach, though as Andy points out, it’s no longer the default.

Comments closed

Checking SSIS Package Performance

Published 2024-04-24 by Kevin Feasel

Andy Brownsword digs into SSISDB:

I’ve recently been reviewing SSIS packages to make some performance fixes and needed a way to validate the results of those changes. I thought I’d share the scripts as they may be useful for others.

Rather than relying on run times from the SQL Agent running the packages I wanted to dive deeper into the packages so took another approach.

Read on for a query that gets the information. You can also eyeball it in the SSIS reports if you have a small or consistent set of packages to run.

Comments closed

Deploying SSIS Components using Custom Components

Published 2024-04-16 by Kevin Feasel

Andy Brownsword forgets something at home:

Within SSIS you can make use of custom components which aren’t present out of the box. An example of some would be the Azure Feature Pack if you’re working with cloud resources.

These will let us use features not available natively. They can also provide a challenge down the line when we come to deploy changes to the project.

Here we’ll look at an example of this challenge, how to troubleshoot, and ultimately resolve the issue.

Read on for the scenario and fix.

Comments closed