For a little pre-holiday fun, I’ve got a simple scenario to present to you, and I am interested in your response. Your holiday plans are made. All of your family and friends expect to see you at the annual holiday gathering. Your kids are expecting you there on Christmas morning as they open presents. You unexpectedly get a call from…
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…
Key performance indicators, or KPIs, are at-a-glance metrics for simple insight into the business. These are each designed to provide a glimpse into one aspect of a measurable business activity. KPIs are present in various implementations in most every reporting tool, including SQL Server Reporting Services. Until recently, KPIs in SSRS were not a native part of the SSRS reporting…
SQL Server Reporting Services benefits from a major update in SQL Server 2016, which is obvious from the first interaction with the product. The old and bland Report Manager interface has been replaced with a more modern portal that more effectively displays at-a-glance business answers. In this post, I’ll describe some of the improvements in the all-new report portal in…
Over the weekend I got an email from have i been pwned, a website that tracks data breaches and notifies users whose information may have been compromised. The notification I received reported that my email may have been among some 4.9 million accounts whose information was compromised sometime in December of 2015. The email from HIBP indicated that the owner…
Although I’ve been a regular user of SQL Server Reporting Services since it was released in the mid-2000s, I’ve done very little blogging about it. In fact, a quick search on my blog reveals that it has been almost five years since I wrote an SSRS-specific blog post. But let’s be fair – before this year, it has been a…
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,…