SQL Server Integration Services (SSIS) has been a staple of the Microsoft business intelligence stack since 2005. Descended from Data Transformation Services (DTS), SSIS is an enterprise-ready, fast, and easy-to-use ETL system built on top of Microsoft SQL Server. Most anyone can learn the SSIS basics in just a few hours.
SSIS Basics
While SSIS makes it relatively easy to get started moving data, there are some nuances within ETL tools in general and in SSIS in particular that require a bit more explanation. On this page I intend to fill in that gap by sharing some useful getting started information for SSIS learners. Most of the links are to posts on this site, but where appropriate I have included links to external resources as well.
I am always adding new content to this collection. If you have an introductory topic you’d like to see covered here, send me a note and I’ll consider adding it to the queue.
Control Flow
The control flow is the starting point for any package, containing all of the tasks (including data flow tasks described below), precedence constraint, and any containers used in the package. Below are a few links to help you get started with the control flow.
SSIS Parameters
SSIS Precedence Constraints
Continue Package Execution After Error In SSIS
Conditional File Processing In A ForEach Loop
Skipping Items in an SSIS ForEach Loop
Get Started with the For Loop Container in SSIS
Data Flow
The data flow is where most of the data movement takes place. The data flow contains sources, destinations, and transformations to allow you connect to a variety of data sources and manipulate or cleanse the data. The data flow is as simple or as complicated as you need, and is robust enough to handle many millions of rows of data.
Processing Multiple Files In SSIS With The Foreach Loop
Using The SSIS Multiple Flat Files Connection Manager
Using Custom File Delimiters In SSIS
Using the SSIS Object Variable as a Data Flow Source
Space Sensitivity In SSIS Lookups
SSIS Lookup Cache Modes
SSIS Catalog
The SSIS catalog was introduced in 2012 as a complete overhaul of how packages are stored, executed, and logged. The SSIS catalog helps remove a lot of manual work that was required when building packages and projects in older versions of SQL Server Integration Services.
Introduction to the SSIS Catalog (video)
Creating The SSIS Catalog
SSIS Catalog Logging Tables
SSIS Custom Logging Levels
Deleting A Package From The SSIS Catalog
SSIS Catalog Project Versioning
Other Operations
You can use SSIS for ETL as well as the operations supporting ETL. Tasks such as zipping or unzipping files and interacting with an FTP or SFTP server are often required as part of the extract, transform, and load process.
Create ZIP Files in SSIS
Unzip Files in SSIS
Accessing SFTP with SSIS
Retrieve A List Of Files From FTP Using SSIS
SSIS Expression Language Conditional Operator
Administration
Integration Services does require care and feeding beyond the initial package development. Taking care of your memory needs, scheduling tools, and other considerations is part of the maintenance of SSIS.
A Better Way To Execute SSIS Packages With T-SQL
How Much Memory Does SSIS Need?
Using Parameters and Expressions in SSIS (video)
Using Project Connections in SSIS
Need more help?
In addition to the above SSIS Basics resources, I have a comprehensive set of training classes available. I also provide professional mentoring and consulting if you need personalized guidance or hands-on assistance.