Temporal data storage, which was defined in the ANSI SQL:2011 standard, describes an architecture in which relational databases maintain row-level versioned copies of data. When using temporal data, the database does automatically what many DBAs and application developers have had to do manually for years to track point-in-time versioning for individual tables. Starting in 2016, Microsoft added SQL Server temporal…
When designing the ETL architecture for new or changing systems, one of the more common needs is to create a process that copies all of the data from a set of tables (perhaps even all tables) from a source system to a destination database. This is especially prevalent when building an operation data store (ODS), or building a set of…
Having the right tools for the job makes the work much more efficient. However, for those just starting out in SQL Server land, it may not be clear which tools are useful or appropriate for a given task. I regularly field questions about which tools I use on my development machines, and why. To that end, I’ve compiled a brief…
Most traditional ETL processes perform their loads using three distinct and serial processes: extraction, followed by transformation, and finally a load to the destination. However, for some large or complex loads, using ETL staging tables can make for better performance and less complexity. As part of my continuing series on ETL Best Practices, in this post I will some advice…
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…
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…
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…
Extract, Transform, and Load (ETL) processes are the centerpieces in every organization’s data management strategy. Each step the in the ETL process – getting data from various sources, reshaping it, applying business rules, loading to the appropriate destinations, and validating the results – is an essential cog in the machinery of keeping the right data flowing. Establishing a set of ETL…
Recently, I wrote about how to get started with SQL Server change tracking, and I demonstrated a design pattern I use with change tracking in incremental load scenarios. In this post, I’ll round out the topic by showing how using change tracking in SSIS packages can add more flexibility to ETL processes. Using Change Tracking in SSIS In my last post I…