I’ll be teaching my popular day-long course, Building Better SSIS Packages, on Thursday, June 1 in Pensacola, Florida. This class is being delivered in conjunction with SQL Saturday Pensacola. Registration is open to the public, and early-bird pricing is in effect until early May. Let me know if you have any questions about this class. I look forward to seeing…
In my ongoing series on ETL best practices, I recently wrote about the importance of logging in extract-transform-load processes. For users of later versions (2012 and beyond) of SQL Server Integration Services, adding logging to those ETL processes is very simple. Since logging is managed by the catalog, adding logging to an ETL process is a one-step process and requires…
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…
One of the benefits of consulting is that I get to see and learn from a lot of different design patterns – both good and bad – out in the real world. It is that experience that allows me to offer high-quality SSIS training in addition to my consulting services. Over the years I have built a rich library of…
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…