Press "Enter" to skip to content

Category: Integration Services

Refreshing Power BI Datasets from SSIS

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

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

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

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.

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

Tokenizing Results in SSIS

Peter Schott shows off a handy trick:

I’ve worked with SSIS for some time now, but a recent question and post pointed out an expression I’d never used before. We often have a need to split strings and pick out some portion of that string. Sometimes we have a need to pull in everything in the “nth” occurrence of a string. The TOKEN expression can be used to get that particular value. I tested this out by mocking up a really simple package.

Read on to see how.

Comments closed

Migrating SSIS On-Prem Workloads into Azure

Jitendra Yadeo has put together a how-to guide:

– There can be scenario where organization wants to migrate there existing SSIS ETL process on cloud so instead of rewriting SSIS package using Cloud specific ETL tools like Azure Data Factory we can directly migrate SSIS packages and call it through Azure Data Factory.

– Goal of this blog is to show how SSIS packages hosted on on-premise can be migrated to Azure Data Factory (ADF) using Azure-SSIS Integration Runtime (IR).

Read on for a step-by-step guide.

Comments closed

Creating an SSIS Integration Runtime in Synapse

Andy Leonard shows one way to create an Azure * SQL Server Integration Services integration runtime for Azure Synapse Analytics:

On 17 Feb 2022, I first saw the Microsoft announcement of the public preview of Azure-SSIS integration runtimes in Azure Synapse Analytics. I blogged about the announcement in a post titled Azure-SSIS Integration Runtime now available in Azure Synapse Analytics.

I am excited to share one way for you to provision an Azure-SSIS IR in Synapse Analytics, following these steps. To start provisioning a shiny new Azure Synapse Analytics Azure-SSIS integration runtime, open Synapse Studio:

Read on for the step-by-step guide.

Comments closed

SSIS Integration Runtimes in Synapse

Andy Leonard heard it on the grape vine:

My first response was – and I quote – “WOO HOO!” It’s good to see SSIS getting some love.

A couple years ago, someone claimed SSIS was dying. I first checked it out. Then I blogged about it in a post titled SSIS is Not Dead (Or Dying). It’s been a couple years and SSIS is not dead. One could say SSIS functionality being added to Azure Synapse, arguably Azure’s flagship offering, appears to be the opposite of dying.

I’m not sure I’m as sanguine as Andy is about the future of SSIS but I will say at the very least I agree that it’s not going anywhere anytime soon.

Comments closed

Updates on SSIS Framework Manager

Andy Leonard has a progress report for us:

Kent Bradshaw and I continue to update SSIS Framework Manager, the visual tool for managing SSIS Framework Applications. In our parlance, an SSIS Application consists of one or more SSIS Packages (to which we refer in the framework as application packages) configured to execute in a specific order. To date, enterprises using SSIS Frameworks (well, our SSIS Frameworks, at least) have relied on T-SQL for management functionality. We aim to change that with the next release of our SSIS Framework which will include SSIS Framework Manager.

Click through to see what they’re working on.

Comments closed

Row Yielded No Match during Lookup in SSIS

Nick Edwards plays match-maker:

Have you ever been faced with the SSIS error “Row yielded no match during lookup”? If so, this blog is for you!

A customer of ours recently faced the same issue in one of their SSIS packages and asked us to investigate this for them. Initial investigations on their side highlighted that when they replicated the lookup component using a standard join in T-SQL (similar to the image below) it returned the expected results.

So why was SSIS reporting an error and ultimately causing the package to fail?

Read on to learn why. For bragging rights (and a demonstration of how much SSIS pain I’ve suffered through the years), I got it in one.

Comments closed