A Fine Slice Of SQL Server
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 closedAndy 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 closedAndy 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 closedAndy 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 closedIn 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 closedAndy Leonard breaks it down for us:
How do I test new SQL Server Integration Services (SSIS) extensions?
I have a collection of virtual servers that run locally on my laptop. SSIS extensions for Visual Studio 2019 and Visual Studio 2022 are now separate downloads (see the links provided).
Click through for Andy’s workflow.
Comments closedAndy 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 closedAndy 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 closedAndrea 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 closedAndy Leonard troubleshoots an error:
Have you encountered this error: “Failed Get Parameter Info of parameter ____”? I ran into this error a couple times while consulting for a client to lift and shift SQL Server Integration Services (SSIS) to Azure Data Factory (ADF).
Click through for an example and what is the most likely cause of the problem.
Comments closed