Press "Enter" to skip to content

Category: Integration Services

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

Using tsnnames.ora with SSIS and Microsoft Connector for Oracle

Published 2023-06-07 by Kevin Feasel

Meagan Longoria deals with the unpleasantness:

Although you can now specify an Oracle database using the EzConnect format, it’s still very common to use a tnsnames.ora file. This file specifies a connection name and connection details for an Oracle database. It’s helpful in that you can provide a “friendly name” to the database specified in the file and then reference the file from multiple client tools.

Read on to learn how you can use it.

Comments closed

Stopping an SSIS Package from the SSIS Catalog

Published 2023-04-12 by Kevin Feasel

Olivier Van Steenlandt hits the big red button:

I developed my package in Visual Studio 2019 and deployed my changes to my SSIS Catalog (SSISDB in my case). As soon as my SSIS Package was deployed I started the SSIS Package execution.

After a couple of minutes, I realised I made a mistake. I didn’t want to lose too much time, so I looked for a way to stop the SSIS Package Execution.

Since I was not executing the package from a SQL Server Agent Job, I couldn’t just stop the job. So I needed to find another way.

Click through for a way to do this via SQL Server Management Studio.

Comments closed

Installing the SSIS 2022 Preview for Visual Studio

Published 2022-11-29 by Kevin Feasel

Koen Verbeeck does a bit of installation:

For those of you that have been working on an older version of SSIS/SQL Server (2014-2016, something like that), the BI components (SSIS/SSAS/SSRS) are now extensions in Visual Studio. SQL Server Data Tools (SSDT) is no longer available as a separate download.

So you’ll need a full-blown version of Visual Studio (make sure you only install the workflows you actually need). The good news is that you can use the community edition of VS if you’re just using it for BI development. Anyway, install VS 2022 on your machine and download the SSIS extension here.

Read on for the full installation process and a couple of warnings.

Comments closed

Refreshing Power BI Datasets from SSIS

Published 2022-09-07 by Kevin Feasel

Teo Lachev solves a problem:

Scenario: You use SSIS to load data for on-prem BI solution. As a last step of the ETL pipeline, you want to refresh a Power BI dataset. There’s quite a bit of misinformation on the Internet about how to do this, hence this blog.

Read on to see how you can do this, supposing that you’re hosting in a Premium-Per-User or Premium workspace.

Comments closed

Finding Logged Errors in SSISDB

Published 2022-07-26 by Kevin Feasel

Thomas Williams checks the logs:

Due to its simplicity, I’m a fan of the default, in-built “SSIS log provider for SQL Server” logging, which writes to the SSIS database SSISDB when the package is deployed to a SQL Server. This logging comes out of the box with very little setup required, and can be supplemented by custom messages – for instance, using Dts.Log in a script task as per https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-scripting/task/logging-in-the-script-task.

Best of all, because log messages are written to tables in the SSISDB database, end-users can run a query or report to troubleshoot errors.

Read on for a query which pulls the last seven days worth of error information from the built-in log.

Comments closed

SSIS — RPC Server is Unavailable

Published 2022-06-24 by Kevin Feasel

Jon Morisi does some troubleshooting:

I just spent a long slog sorting out why I could not connect to my SSIS instance remotely.  I work in a very secure environment requiring network approval for any and all ports.  According to the following article, I was under the impression that a request to open incoming traffic on port 135, to a specific IP, would allow SQL Server Management Studio, on that specific IP, to connect remotely to SSIS:

https://docs.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access?redirectedfrom=MSDN&view=sql-server-ver16#BKMK_ssis

After opening port 135, I was receiving the error message in the title of this article:

If you find yourself in this situation, read on to see how Jon was able to solve the problem.

Comments closed

Broken References in the SSIS Catalog

Published 2022-06-09 by Kevin Feasel

Andy Leonard beats the brakes off of some stuff:

It may help to define reference first. An SSIS Catalog Reference is a relationship between an SSIS Project and an SSIS Catalog Environment in the SSIS Catalog. One or more references to environments may be configured for a project. At execution time one and only one reference may be selected – like Highlander, “There can be only one.”

A broken reference occurs when one configures a reference between a project and an environment, and then the environment is deleted. 

Read on to see what this looks like in the SSIS Catalog Browser and SISS Catalog Compare tools, as well as how you can prevent accidentally creating broken references.

Comments closed