Press "Enter" to skip to content

Category: Integration Services

Updates on SSIS Framework Manager

Published 2022-02-15 by Kevin Feasel

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

Published 2022-01-26 by Kevin Feasel

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

Appending Date to File Name in SSIS

Published 2022-01-18 by Kevin Feasel

Jason Jenkins needs a dynamic filename:

In a recent project we needed to create an SSIS package to export data from a SQL Server table to a CSV file.  One of the requirements was to dynamically append the date to the file name each time a file was generated. This post will cover how to create a dynamic file name with the date included (YYYYMMDD format) in the file name.

Click through to see how. Hint: it’s expressions. It’s always expressions with SSIS.

Comments closed

SSIS Framework File Community Edition

Published 2022-01-14 by Kevin Feasel

Andy Leonard has an announcement:

The very first data integration / data engineering framework I ever wrote was for Data Transformation Services, or DTS. The DTS framework had one job: manage connections. I don’t recall all the details, but I remember DTS included a task that allowed packages to retrieve settings from INI files. INI files are key-value files, so I simply added entries with identical keys and different values – values that matched connection strings for each lifecycle tier – and placed each version of the INI file in the same location on every server in the lifecycle.

The next framework I wrote was for SSIS. I stored metadata in tables – including connections metadata – and created a concept I called an SSIS Application. An SSIS application is, according to my definition, a “collection of SSIS packages that execute in a pre-determined order.”

The SSIS Framework File Community Edition is very similar to this first framework, except for the connections management.

Click through to learn more about the SSIS Framework File Community Edition and check it out.

Comments closed

Flexible File Components with SSIS

Published 2022-01-07 by Kevin Feasel

Bill Fellows hides SSIS DNA in a can of Barbasol shave cream:

The Azure Feature Pack for SSIS is something I had not worked with before today. I have a client that wants to use the Flexible File Task/Flexible File Source/Flexible File Destination but they were having issues. The Flexible File tools allow you to work with Azure Blob storage. We were dealing with ADLS Gen2 but the feature pack can work with classic blob storage as well. In my hubris, I said no problem, I know SSIS. Dear reader, I did not know as much as I thought I did…

Click through for a whopper of a story. But be sure to read to the very end, as you don’t want to stop at using TLS 1.0.

Comments closed

Extracting SQL Queries from SSIS Packages

Published 2021-12-23 by Kevin Feasel

Bill Fellows works from an…interesting…SSIS package:

Our job was to rewrite this into something more manageable and it appears Azure Data Factory will be the winner. Before we can do that, we need to document what the existing package is doing (the vendor has supplied the incremental load logic) so we can replicate it but in a more economical form. It appears to have the pattern (squint really hard at the picture) Execute SQL Task -> Execute SQL Task -> Sequence container => many data flows -> Data Flow -> Execute SQL Task. The Data Flow Task is named after the table being loaded. An ODBC source with a expression based query, named “ODBC Source 1” wired to an OLE DB Destination, named “OLE DB Destination”. How would you do it, especially given that there are 236 Data Flow Tasks embedded in a single container?

Click through for the answer.

Comments closed

Explaining SSISDB

Published 2021-10-19 by Kevin Feasel

Andy Mallon explains why SSISDB exists:

Back in my day, we stored SQL Server Integration Services (SSIS) packages in msdb, or the file system. Well, I’m neither dead nor retired, so that’s not an accurate statement–today is still “my time.” But when I was learning about SQL Server, those were the only two options. If you or your environment predate SQL Server 2012, then you might only be familiar with those two options.

In SQL Server 2012, Microsoft introduced the SSISDB. Since SQL Server Integration Services (SSIS) is its own service, it deserves its own database. In a service-oriented world, it is discouraged to put data for one service (the database engine) in the same database as another service (integration services).

Click through to see where it comes from and when you might need it.

Comments closed

High-Performance ETL via Buffer Table

Published 2021-07-15 by Kevin Feasel

Daniel Hutmacher needs things to zoom:

It’s almost like a myth – one that I’ve heard people talk about, but never actually seen myself. The “shock absorber” is a pretty clever data flow design pattern to ingest data where a regular ETL process would choke on the throughput or spikes. The idea is to use a buffer table to capture incoming data, and then run an asynchronous process that loads that data in batches from the buffer into its intended target table.

While I’ve seen whitepapers and blog posts mention the concept loosely along with claims of “7x or 10x performance”, none of them go into technical detail on how it’s done, so I decided to try my hand at it.

I’ve compiled my findings, along with some pre-baked framework code if you want to try building something yourself. Professional driver on closed roads. It’s gonna get pretty technical.

Combine that with Eitan Blumin’s post yesterday and you’d think it were buffer week.

This shock absorber pattern works well for warehouse loading, especially when you’re trickle-loading data into columnstore indexes and don’t want to have open rowgroups slowing everything down.

Comments closed