The SSIS catalog comes packaged with a rich set of built-in reports that give those monitoring Integration Services a window into what has been happening inside the catalog. However, if you work with SSIS long enough, you’ll eventually need to write your own queries against the underlying tables. Of the many things the built-in catalog tables and views do well,…
In my last post I shared how to build a package in SSIS to perform simple file archive (zip) operations. In this post, I’ll show you how to go the other direction and unzip files with SSIS. Unzip Files with SSIS Similar to the file zip operation, this solution uses the following tools: 7Zip: This free utility manages the creation…
Of the very many things SQL Server Integration Services does well, one of its shortcomings is that it does not have a built-in way to create compressed archive (ZIP) files. Generating such files is a common need in ETL processes, so it’s surprising that the fifth generation of SSIS still does not have a native task to manage this operation….
Built into the SQL Server Integration Services catalog is the ability to run a validation without actually executing the package. Running a package validation in the SSIS catalog performs a high-level check against the underlying metadata to check for common points of failure (especially those related to data flows). SSIS package validation is not designed to capture every metadata issue,…
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…
I’ve written quite a bit about the benefit of externalizing changing values in SSIS packages. Moving static values such as connection strings and file paths to a configurable input makes easier the tasks of testing, changing, and auditing the process in the future. The short and generic story here is: don’t hard code values that can change. In SQL Server…
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…
I am excited to share that I will be presenting at the DevConnections conference in Las Vegas in October of this year. This is my fourth (if I remember correctly) time to have spoken at DevConnections, and the first time I will have delivered a full-day workshop there. This year I will present one workshop and two regular presentations: Building…
Question from someone in one my recent classes: “What tool do you use to send email from ETL processes?” My response: “I don’t.” The tl;dr version of this post is I let my extract-transform-load processes do just ETL, and leave notifications to the scheduling system where they belong. Get Your Email Out of my ETL Before you read any further,…
If you are looking for affordable, high-quality training on SQL Server Integration Services, you may be interested in one of the two full-day workshops I have scheduled for August of this year. I’ll be taking my popular course Building Better SSIS Packages on the road to Baton Rouge, Louisiana and San Antonio, Texas. This course is designed for the data…