No need to call Support for these common challenges in SSIS

My hard-working teammate and fellow technical writer, Carla Sabotta, recently scanned the records of an eye-crossing 900 Microsoft Support incidents involving SQL Server Integration Services 2005 and 2008. She was looking for issues that the customer could easily have answered with nothing more than the right Help topic from SQL Server Books Online. The percentage turned out to be quite small. I'm not certain how to interpret this small number, but these seem to be my options:

  • "Glass is half empty" version: BOL doesn't adequately explain most of the things that customers have problems with.
  • "Glass if half full" version: Customers are finding answers in BOL (and other publicly available content) instead of calling Support.

While I try to decide which story will give me more leverage with the boss in asking for more resources, please make sure you check out these BOL topics and other resources before you make an unnecessary call to Microsoft Support:

(I have listed the SQL Server 2008 version of BOL topics below, since I can find them more quickly. For the most part, the same information applies to SQL Server 2005.)

Clustering SSIS


Running packages using SQL Server Agent

Did you know that this has been the top DTS/SSIS support incident, unchallenged since the days of SQL Server 7.0 until the present day? Apparently too few of us (and I'm including myself here) understand the end-to-end permissions requirements in the mess of accounts, proxies, roles, owners, jobs, connections, ACL's and more!

How to:


Deploying packages

Accessing a remote Integration Services server

Configuring Integration Services service to work with named instance of the Database Engine

Implementing database maintenance plans (which use SSIS under the covers)

Understanding which pieces of Integration Services are in each edition or SKU of SQL Server

(This is one of the most painful BOL topics to update for each release.)

The short answer is:

  • In 2008, you get the Import and Export Wizard with all editions of SQL Server 2008.
  • You need Standard or Enterprise (or an Enterprise equivalent like Developer) to develop packages.
  • Only in Enterprise do you get the Analysis Services and Data Mining/text mining related components and the 2 Fuzzies.

Running DTS packages on a 64-bit computer

I'm not absolutely sure whether this line in Carla's report means DTS literally, or SSIS (and since it's Easter Sunday afternoon, I guess I can't ask her). The short answer on DTS is that it's 32-bit only. But let's cover both bases:

Migrating DTS packages

Running the Fuzzy Lookup transformation against a SQL Server 2000 table

BOL is incomplete on this one, until the revisions that I just made are published in early May (2009). In fact, I had to ask the developer in Microsoft Research for clarification. Here's the summary of what I learned:

"The Fuzzy Lookup transformation requires you to select a reference table from an instance of SQL Server 2000 or later. However, the optional Maintain stored index option requires SQL-CLR integration, and therefore requires a reference table from an instance of SQL Server 2005 or later, with CLR integration enabled."

I hope that these links are helpful for anyone who's struggling with these features of Integration Services!

Please comment on my blog!

You don't have to sign up or sign in - anonymous comments are enabled. I "moderate" the comments to delete spam before you see it, but otherwise, I publish your comments immediately. You can also email me at dougbert@dougbert.com. Thank you!


Comments (2) -

Duane Douglas

I moderate the MSDN SSIS forum.  I'm not surprised at the number of support calls for common challenges because I've seen quite a few questions that could've quickly and easily been answered by clicking a link to the BOL from a google search.  Apparently, many SSIS developers refrain from such endeavors.

Chris Randall

Thanks for this - a link to this post will be provided to all students in my SSIS classes from now on!

Pingbacks and trackbacks (1)+