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…
Wrangling large or complex Excel workbooks in SSIS can be a challenge. From managing data types (more about that in this post by Koen Verbeeck) to addressing multiple worksheets in a single document, configuring SSIS to properly read from or write to Excel documents is tedious at best. While there are no silver bullets to completely solve these problems, I’ve…
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…
Recently I was building a new virtual machine for presentations, and loaded up my usual battery – Windows 7, SQL Server 2012, and SSDT-BI. The installation of these pieces went as expected, and everything appeared to work well – until I attempted to execute an SSIS package on this new setup. When I did so, I received the following cryptic…
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…
It’s a little over a week until this year’s SQL Saturday festivities kick off in Lisbon, Portugal, and I’m very excited to be a part of it. Registration is nearly full, so if you’re in the area and are planning on attending, register now! For this event, I’m delivering a full day workshop entitled “Real World SSIS: A Survival Guide”,…
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 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 World SSIS:…
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…