Press "Enter" to skip to content

Category: Integration Services

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.

Leave a Comment

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.

Leave a Comment

Executing via Proxy in SSIS

Published 2024-02-27 by Kevin Feasel

Andy Brownsword submits a series of requests through a proxy:

When executing packages for SSIS the default option would be to use the SQL Server Agent service account. We might not want to share an account between our services and Integration Services packages due to security risks.

Let’s take a common example: Suppose we have a package which reads from a file share. If permissions aren’t set up correctly this can fail. Even if access is corrected, the service account can cache that failure. The only way to resolve may be to restart the service. This isn’t something we’d want to be doing on production systems.

Read on to see how you can use a proxy to bypass this problem.

Comments closed

SSIS on Linux

Published 2024-01-10 by Kevin Feasel

I am not amused:

In this video, we bang our heads against the wall repeatedly with respect to SQL Server Integration Services. I spend a lot more time than I want to but we do get a mostly-functional product mostly working.

This was a frustrating video to make, but I think it was important to make it clear just what SSIS on Linux can and cannot do.

Comments closed

SSIS Catalog Error 27150

Published 2023-10-27 by Kevin Feasel

Andy Leonard diagnoses an error:

In case you’ve never encountered SSIS Catalog Error 27150, the error message reads:

The version of the project has changed since the instance of the execution has been created. Create a new execution instance and try again.

A customer recently encountered this error.
So… what happened?

Click through for the answer. I’ve had this happen as well for the same reason as Andy.

Comments closed

TaskFactory Activation on an Azure-SSIS Integration Runtime

Published 2023-08-24 by Kevin Feasel

Andy Leonard does some sleuthing:

I regularly help customers migrate SSIS to Azure-SSIS integration runtimes, a nifty component of Azure Data Factory. I was recently stumped by an error activating TaskFactory (Task Factory for the search engines…) on an Azure-SSIS IR node. The error was:

“The system cannot find the file specified.”

Read on to figure out where the file is and how to fix this error.

Comments closed

The Trials and Tribulations of Migrating SSISDB

Published 2023-08-23 by Kevin Feasel

Andrea Allred packs up boxes and moves the database across town:

Recently I needed to move a dev instance from a physical server to a virtual one and needed to do SQL upgrades. We decided it was time to spin up a new dev instance and that required moving SSISDB. I will be honest, I was not prepared for all the things. I also did not have a password for the encryption so I had to force things to work. Because this was a dev server, I wasn’t as worried about what I would lose, I just wanted it to work. Here is what I did.

There’s a lot more here than “restore database from backup,” so click through to read the steps.

Comments closed

SSIS Package Migration with dtutil

Published 2023-06-12 by Kevin Feasel

John McCormack does some spring cleaning:

SQL Server Integration Services (SSIS) is a powerful tool, but migrating packages across SQL Servers can be a slow and thankless task if you don’t use automation. The single best way to do this is by using DTUTIL, a command-line utility provided directly by Microsoft. When it comes to moving 1000s of packages, I would only use DTUTIL because it can achieve in minutes what it would takes days to achieve using point and click.

Read on to see how you can move these packages between instances, as well as downloading packages as a backup.

Comments closed