SQL Server 2005

I was surprised at how technically "unsafe" certain very common aspects of managed code use were under .NET Framework 1.0 and 1.1. Particularly, any kind of Interop activity in a multi-threaded environment has some potential resource leaks and even security holes.

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.

Some things I learned today about SSIS, script tasks, .NET assembly references, and the GAC. (Includes updated comments.)

I noticed today that if you (in BIDS) change the name or type of a user variable in SSIS, that change will not auto-propagate to any package configurations you have.

I wasted about two hours of my day today trying to figure out why I was getting an extra fire of my package-level OnError event handler in SQL Server 2005 SSIS. At first I thought it was firing once for the original error occurrence (which I had inside of a Try block) and another time for the call to FireError(). This was not the case, however. Turns out the extra call happens when you set Dts.TaskResult to Failure.

Some things I learned while experimenting with exception handling in SSIS today.