When processing data files using SQL Server Integration Services, it is not uncommon to find files with different end-of-line markers for each line in the file. In this post, I will demonstrate how to fix inconsistent line terminators in SSIS to avoid ETL errors. Fix Inconsistent Line Terminators in SSIS In every text file, there are unprintable characters called line…
I’m very excited to offer a new course entitled “Advanced SSIS” in the Dallas area this spring. My friend and colleague Andy Leonard and I will be delivering this new 3-day course March 9-11, 2015 at the Microsoft offices in Irving, Texas. This course is intended for those who have experience using Integration Services who are looking to take their…
This is the third in a series of posts about SSIS parent-child architecture. You can find the index page here. In my previous posts on SSIS parent-child package architecture, I described the benefits of the parent-child package design pattern and demonstrated the implementation of such a pattern in package deployment mode. In this post, I will demonstrate the parent-child pattern…
In my first post in this series, I covered the essentials of SSIS parent-child design patterns, including discussing a number of advantages for using such patterns. In this post, I’m going to demonstrate how to build parent-child structures using package deployment mode. SSIS Parent-Child Architecture in Package Deployment Mode Package deployment mode is available (though not the default behavior) in…
This is the first in a series of technical posts on using parent-child SSIS architecture. In this post, I will provide an overview of the architecture and describe the benefits of implementing a parent-child design pattern in SQL Server Integration Services structures. Parent-Child SSIS Architecture The simplest definition of SSIS parent-child architecture is that it consists of packages executing other…
I’m happy to announce that I’ll be teaming up with my Linchpin People colleague Bill Pearson for a day of BI and SSIS training next month. On Wednesday, February 12th, we’ll each be delivering a full-day presentation in Baton Rouge, after which we’ll be joining the Baton Rouge SQL Server User Group for their monthly meeting. I’ll be presenting Real…
I’m kicking off a new series of blog posts discussing the topic of parent-child architectures in SQL Server Integration Services. The links to the posts in this series are below. Parent-Child SSIS Architecture SSIS Parent-Child Architecture in Package Deployment Mode SSIS Parent-Child Architecture in Catalog Deployment Mode I still remember the first SSIS package I ever deployed to a production environment. …
When it comes to ETL, I’m a pessimist. Until proven otherwise, I assume that all data is bad, all connections are volatile, and all transformation logic is suspect. As such, I spent a lot of time addressing how to prepare for and handle errors in the ETL pipeline with SSIS. Building packages to expect and properly handle errors is a…
In the first post in this series, I covered the basics of object typed variables in SQL Server Integration Services, along with a brief examination of some potential use cases. In this installment, I’m going to illustrate the most common use of object typed variables in SSIS: using an object variable as an ADO recordset within a loop container to…
Note: This will be the first post in a short series on using object typed variables (also known as SSIS object variables) in SQL Server Integration Services. When defining variables in SSIS, the ETL developer has several data type options to choose from depending on the information to be stored in each variable. Included in the options are String, Boolean,…