SSIS

Comparing SSIS and Azure Data Factory

For the better part of 15 years, SQL Server Integration Services (SSIS) has been the go-to enterprise extract-transform-load (ETL) tool for shops running on Microsoft SQL Server. More recently, Microsoft added Azure Data Factory (ADF) to its stable of enterprise ETL tools. In this post, I’ll be comparing SSIS and Azure Data Factory to share how they are alike and…


SSIS Layout Formatting Options

Most folks I know go to one extreme or the other when it comes to the visual layout of SQL Server Integration Services packages: either they don’t care a tiny bit about the appearance, or they insist on an easy-to-read SSIS layout before a project is considered complete. I am definitely in the second group, and will almost always spend…


Using the SSIS Error Output On the Data Flow

When working in the SSIS data flow, you’ll notice that many sources and transformations and some destinations have a built-in output to handle errors. The error output allows the SSIS developer to create a separate path through which error rows can be directed. In this SSIS Basics post, we’ll briefly discuss the essentials and design patterns for using SSIS error…


Using the SSIS Term Extraction for Data Exploration

Data exploration is an essential piece of any new ETL (extraction-transformation-load) process. Knowing the structure, type, and even the semantics of data sources can help the ETL developer or architect to make better decisions on data type and length settings, transformation logic, and error handling procedures. Additionally, there are situations in which the exploration of the data is the principal…


Using Project Connections in SSIS

In SQL Server Integration Services, connection managers are used as gateways for most any external read and write operation. Connection managers are type- and format-specific, and in the case of relational database connection managers, they are usually specific to vendor (Oracle, DB2, etc.) as well. In most use cases, the same connection will be used across multiple packages in the…


Get Started with the For Loop Container in SSIS

SQL Server Integration Services is equipped with tasks and containers to make it easy to design and maintain the flow of ETL: which logic should be executed, when should it be executed, and how many times should it occur. Most SSIS developers are familiar with the sequence container and the For Each Loop container, which can be used to group…


SSIS Lookup Cache Modes

In SQL Server Integration Services, the lookup component is one of the most frequently used tools for data validation and completion. The lookup component is provided as a means to virtually join one set of data to another to validate and/or retrieve missing values. Properly configured, it is reliable and reasonably fast. To get the most out of this component,…


Using Raw Files in SSIS

SQL Server Integration Services does a great job of retrieving and processing data on the fly, directly in the data flow pipeline. However, there are circumstances that occasionally require the persistence of result sets in SSIS for use during package execution. For these such cases, one option is to use SSIS raw files. In this SSIS Basics post, I will…


SSIS Precedence Constraints

In the control flow for SQL Server Integration Services, the “lines” we commonly use to connect tasks to each other are actually smart controls. These SSIS precedence constraints allow the ETL developer to customize the program flow from one task to another. The most common use of precedence constraints is to simply connect two tasks with the default constraint, which…


How Much Memory Does SSIS need?

One of the most common questions I am asked about ETL configuration is how much memory SSIS requires. When configuring a new instance of SQL Server running SSIS, or adding SSIS to an existing SQL Server installation, it is important to understand how SSIS uses memory, and what factors in your package design will impact the amount of memory used…