One of my favorite testing features of SSIS is also one of the most underutilized. SSIS data taps were introduced with the SSIS catalog in SQL Server 2012 as a way to capture data within one leg of a data flow task and write it out to a file for testing or auditing purposes. In this brief post, I’ll show…
There are several ways to execute SSIS packages that have been deployed to the SSIS catalog, and my favorite way of performing this task is to execute SSIS packages with T-SQL. There are a couple of challenges that come up when executing catalog-deployed packages using the default settings in T-SQL, but I have workaround for those issues which I’ll cover…
Recently, I wrote about how to get started with SQL Server change tracking, and I demonstrated a design pattern I use with change tracking in incremental load scenarios. In this post, I’ll round out the topic by showing how using change tracking in SSIS packages can add more flexibility to ETL processes. Using Change Tracking in SSIS In my last post I…
If you’ve used SSIS for any significant amount of time, you’ve almost certainly run into this roadblock: You need to download a file stored on an SFTP (secure FTP) server, but connecting to SFTP with SSIS is not natively supported. There is a built-in FTP task in Integration Services, but it is very limited in its functionality and will not…
This is the third in a series of posts about SSIS parent-child architecture. You can find the index page here. In my previous posts on SSIS parent-child package architecture, I described the benefits of the parent-child package design pattern and demonstrated the implementation of such a pattern in package deployment mode. In this post, I will demonstrate the parent-child pattern…
In my first post in this series, I covered the essentials of SSIS parent-child design patterns, including discussing a number of advantages for using such patterns. In this post, I’m going to demonstrate how to build parent-child structures using package deployment mode. SSIS Parent-Child Architecture in Package Deployment Mode Package deployment mode is available (though not the default behavior) in…
This is the first in a series of technical posts on using parent-child SSIS architecture. In this post, I will provide an overview of the architecture and describe the benefits of implementing a parent-child design pattern in SQL Server Integration Services structures. Parent-Child SSIS Architecture The simplest definition of SSIS parent-child architecture is that it consists of packages executing other…
In the first post in this series, I covered the basics of object typed variables in SQL Server Integration Services, along with a brief examination of some potential use cases. In this installment, I’m going to illustrate the most common use of object typed variables in SSIS: using an object variable as an ADO recordset within a loop container to…
I’ve fielded a number of requests recently asking how to interrogate a file within SSIS and change the processing rules based on the metadata of said file. A recent forum poster specifically asked about using the foreach loop to iterate through the files in a directory and, on a per-file basis, either process the file or skip the file if…
It’s that time again! My Eleven Days of Festivus blogging extravaganza is now in its third year, and I’m happy to share one blog post per day with you for the 11 days between now and Festivus Eve (December 22nd). As before, I’ll link all of the posts from this page. May your feats of strength be mighty, your airing…