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.
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.
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.
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
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.
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.
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.
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.