Row Numbers and Running Totals in SSIS

During data load or transformation processes, capturing a distinct row number for incoming data can be beneficial for the ETL process itself, as well as for use in the destination database. Having an arbitrary, incrementing row number assigned to each row can help to determine the order in which the rows of data were processed, and can provide a unique…


The SSIS Object Variable and Multiple Result Sets

In my most recent post in this series, I talked about how to use the SSIS object variable as an ADO recordset as a source in a data flow. By loading the result set of a query into this variable, the contents of the variable can be read by an SSIS script component and sent out through the SSIS pipeline….

Internet cable concept

Using the SSIS Object Variable as a Data Flow Source

Object variables in SSIS are incredibly versatile, allowing the storage of almost any type of data (even .NET objects). In my last post on this topic, I demonstrated how an SSIS object variable containing a .NET DataSet object could be used by the for each loop container as an iterator. In this post, I’ll continue the discussion by showing how…

Internet cable concept

Handling Mixed Format Data Files in SSIS

Some time back I wrote about how to use the script component to parse out ragged data files in SSIS. In this post, I’ll continue the discussion to describe how to handle mixed format data files – specifically, those with several different record types in each file. In a perfect world, a flat file will contain a single record type….

SSIS Training with Tim Mitchell

A full day with me, SSIS, and Edgar Allan Poe

When I think about Baltimore, I think about Edgar Allan Poe. Baltimore was Poe’s one-time home, and in fact there is a museum located in a home in which he once lived. I’ve been a Poe fan since high school, and am looking forward to seeing some of the Poe-related sights when I’m in town next month. Why am I…


Best of PASS Summit 2014: Building Bullet-Resistant SSIS Packages

This week, PASS announced the Best of PASS Summit 2014, a collection of the 10 highest-rated sessions from last fall’s PASS Summit in Seattle. I was pleasantly surprised to learn that my session, “Building Bullet-Resistant SSIS Packages”, was among these. All of these sessions (including lots of other content) are available for viewing on the PASS TV channel on YouTube….

Digital program code

Fix Inconsistent Line Terminators in SSIS

There is a flat file processing issue I’ve run into a number of times over the years, and it’s come up again several times recently. The issue relates to the line terminators used in data files. Occasionally, changes to the systems generating these data files, or perhaps even manual edits, can change the way the file marks the end of…


Iterating Through Excel Worksheets with Biml

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…

No Picture

SSIS Parent-Child Architecture in Catalog Deployment Mode

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…


Mile High Tech Con

There’s a brand new business intelligence conference launching next month in Denver, Colorado.  The Mile High Tech Con is a three-day event taking place July 24-26, 2014, and is aimed at business intelligence practitioners, data analysts, and information managers/CIOs. From the event website: Featuring three days of sessions and events focusing on Data Warehousing and Business Intelligence. Mile High Tech…