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…
It’s that time of the year again – the Festivus season is upon us! In the next few weeks, families all over the world will be gathering around the Festivus pole while they wait for the traditional Festivus meat loaf to finish cooking. Afterward, they get some things off of their chests during the airing of grievances, followed by the…
Being disruptive is a powerful way to conduct business. Steve Jobs and Bill Gates insisted that computers belonged not just in business offices but in the homes of regular joes, and built businesses around a need that had not fully manifested itself. Twitter disrupted the social media space by bringing to market a medium for short, frequent, and publicly visible…
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…
SQL Server change tracking is a lightweight and synchronous mechanism for detecting inserts, updates, and deletes in tracked tables. For those of us who spend a lot of time in ETL, change tracking is a useful tool for defining what data has changed to make the extract-transform-load process more efficient. SQL Server Change Tracking In this 30-minute video, I introduce…
Earlier today, Microsoft announced the release of Service Pack 1 (SP1) for SQL Server 2016. Pay close attention to this announcement, because this is not a typical update. Service Pack 1 unlocks most all of the programmability features found in Enterprise Edition, making them available in lower-cost (and even free) editions of SQL Server. Folks, this is a huge change…
If you’ve spent much time working with the SSIS catalog, you likely already know that the catalog is where all assets related to package storage, configuration, and execution are stored: all of the projects and their associated packages, configuration environments, and execution logs are all stored in the SSISDB database used by the SSIS catalog. When deploying a new package…
I still remember the first real ETL process I developed. I was working for a hospital at the time, going through a major system implementation as we replaced a 17-year-old UNIX-based system with a more modern healthcare application suite with a SQL Server back end. I was tasked with building, testing, and executing the ETL processes for this conversion. While…