SSIS (SQL Server Integration Services)

Unfortunately, one of the most annoying SSIS bugs did not make the cut for the SQL Server 2005 SP2 bug fix list: namely, when you add additional output columns to a Derived Column Transformation (and maybe other kinds as well) that is upstream from one or more Union All Transformations, the new columns you add get "stuck" behind the Union All transformations--that is, they do not show up as input columns in the Union All, and consequently do not flow past the Union All.

This post describes some initial troubles I had inserting into a SQL Server table with an Identity-based PK with SSIS while keeping the surrogate PK values from my source data, which happily led to a solution suggested by commenters to the original post.

One of the first of many pains-in-the-butt that SQL Server Integration Services developers will encounter is how from a Script Task in the Control of Flow to write to and read from package variables. I have blogged a bit about the topic of SSIS variables in script tasks in the past, but in this post I'd like to share two custom functions that I copy and paste into all of my script tasks to simplify dealing with variables. If you use these functions you will not need the Script Task Edtior's ReadWriteVariables field to make your package variables available to the script.

The problem is that BIDS keeps forcing a checkout when I open the other package (the one my teammate is working on). It's happened once when I drilled into a data flow task, and another time when I was just looking at the variables window. Suddenly a window pops up announcing that a checkout is taking place, and before I know it I've got a mess on my hands... (Post updated 3/30/2006.)

I discovered a nice SQL 2K5 Integration Services trick today with the OLE-DB Command transformation (and I would imagine the same thing works in other similar contexts). I'm referring to the ability to include a comment inside of the custom SQL statement used with an OLE-DB Command. This perhaps seems obvious, but it wasn't something that occurred to me at first. Here's what I mean:

I think this series of introductory SSIS articles may be--at this moment, before the wave of SSIS books hits the market--the closest thing we have to an introductory SSIS book.

This one might be obvious, I suppose, to people with experience with the Bulk Insert task from previous SQL Server versions, but this information it was new to me.

In my mind there are two separate but intertwined concepts at work in my hypothetical system: the "physical" concepts of execution and success and the "logical" concept of a "step" and whether or not the step "ran" (by which I mean, did the task or tasks that make up this step actually do any work).

Here's a SQL2K5 Integration Services control-of-flow design quandry I've encountered more than once: I have a task (any task, say for instance an FTP task) that I want to execute conditionally, but it does not have a direct parent task. For example, my hypothetical FTP task could be the first task inside of a sequence container, and as such has no direct parent. Since you can't have a precedence constraint (as far as I know) between a container and one of it's children, you're stuck without a place to put an expression to control whether your conditional task should execute.

I have figured out two different causes for this error from the FTP task. In my direct experience I was using IsLocalPathVariable = True with an SSIS variable mapped to the LocalPath property. But after thinking about it, I imagine both of the following scenarios would be possible even if you were not using a variable (please add a comment if you can confirm this).

SSIS is a leap forward in capability, power, and, it appears, performance; and I still feel that in the end we're going to find that building our system in SSIS was a good decision. My main point is that the learning curve can be steep if your SSIS-based system will be at all complex. Plan for it.

This post contains some information about how you can access the results of an SSIS Execute SQL task in a downstream Script task.

Turns out the problem was not data type mismatching (though there are documented problems with BigInt mapping as a string) but rather that the indexes I was using were off. Silly me, I assumed that the first column in my SELECT list would be index 0. Nope. It's index 1. (See my update to this post to see why my original conclusion was wrong.)

Unless I'm totally missing something, I've uncovered a pretty annoying bug design feature in the Package Configuration wizard in BIDS (the SQL Server 2005 Business Intelligence Design Studio). Whenever you walk through the wizard it throws away any values that you've placed in the dtsConfig XML file.

In this post I will document some of the observations and conclusions I have come to in the process of hammering on the event handler functionality of SSIS, trying to see how it ticks, making sure I understand I can use it properly in my system, which is a multi-package system with some specific logging, exception handling, and monitoring requirements.