SSIS Catalog Logging Tables

SSIS Catalog Logging Tables

Making the most of the SSIS catalog requires an understanding of how to access the information stored in the logging tables. Although there are built-in reports to show this information, there are limitations in their use. Fortunately, the logging tables in the SSIS catalog database are (mostly) straightforward and easy to understand once you’ve worked with them a bit. In…


What is a Day?

What Is A Day?

When designing a data warehouse or reporting architecture, the smallest questions can pose some of the biggest challenges. Identifying what may appear to be an obvious term or metric can slow down or even derail a business intelligence initiative. Early in my career as a BI professional, I was faced with just such a scenario when trying to address a…


Why Before How

Why Before How

I’ve spent a lot of time browsing and participating in online technical forums. In my early years in the industry I asked a lot of questions, and as my skills grew I evolved to the point where I was answering more questions than I was asking. The questions and answers I encountered were quite varied, but there was one specific…


Metadata Hygiene

Metadata Hygiene

Those who follow my blog know that I write a lot about data quality. Measuring and improving the quality of data is an important part of any data initiative, especially in the data warehousing space. While data quality does get its share of attention, there is a concept that is equally important but is sadly overlooked during most data projects:…


Loading to Azure from On-Prem SSIS

Loading to Azure from On-Prem SSIS

In my introduction to the topic of using SSIS in the cloud, I noted that SSIS is not currently available in a platform as a service (PaaS). Unlike SQL server databases and more recently SSAS, which are both available as both on-prem and PaaS services, Integration Services must be run on a user-installed instance of SQL Server. This limitation means…


The Psychology of Value

The Psychology of Value

J.C. Penney is one of the oldest and most storied retailers still in existence in the United States. For years, J.C. Penney used a pricing strategy based on setting their retail prices high and then offering discounts and coupons which savvy shoppers could use to bring down the price of goods. Prior to 2012, a trip through any Penney’s store…


Is Your Database App Ready for the Cloud?

Is Your Database App Ready for the Cloud?

The cloud has evolved. Just a few years ago, cloud-based applications were the exception rather than the rule, and on-premises apps were the clear standard for database apps. However, with the numerous advantages of building in or migrating to managed services, the cloud isn’t just a niche anymore. Managed services and cloud VMs are quickly becoming the standard platform rather…


Digital Privacy is the Wild West

Who has the legal right to access your personal and private digital assets? The answer can be complex, and will depend on where you live, where you are traveling from and to, and whether or not you’ve been suspected of a crime. The rules governing personal digital access are evolving rapidly, and are often subject to creative, on-the-fly interpretations. Digital…


Managing Bad Data in ETL

In the last post in my ongoing series about ETL best practices, I discussed the importance of error handling in ETL processes, reviewing best practices for application flow to prevent or gracefully recover from a systematic error or data anomaly. In this post, I’ll dig a bit further into that topic to explore the design patterns for managing bad data in ETL…


Retrieve A List Of Files From FTP Using SSIS

Retrieve a List of Files from FTP using SSIS

The FTP protocol is one of the oldest methods for sharing and moving files. Although frequently considered to be an “old-school” way to transfer data, FTP is still relied upon in most every data movement architecture. Sadly, the functionality around FTP is very limited in SQL Server Integration Services. A common project requirement is to retrieve a list of files from…