Kirk Haselden

Free XML Editor...

Roman's blog mentioned a nifty little free XML editor.

I have XMLSpy, but the license doesn't allow me to use it everywhere I want.

So for lightweight, one off stuff, this one comes in pretty handy. Give it a try.

It's here.

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden

Managing complexity in packages?

<<Yeah, I know. I'm blogging a lot today. I've had a few backed up since .Text has been tweaky lately.>>

We've had several customers send mail asking what was the best way to approach some common problems, which are basically, “How do I organize my complex packages so that they're easier to manage?”

You've got a few options:

• Use more containers - That's what they're there for, to reduce complexity. Especially the sequence container.
• For large dataflows, find logical intervals where you can stage into raw adapters. Enhances restartability.
• Use more subpackages - This is the ultimate because you can share the packages in different solutions. (Code reuse) Faster design time as well. Easier to debug child packages alone than large packages.
• Use property expressions - A lot of folks are using script tasks for simple things like catenating strings etc. Use property expressions for these types of simple jobs instead.
• Use enable/disable options on containers to isolate pieces of a package while debugging.

These are just a few examples of ways you can eliminate complexity in your IS solutions.

Thanks
Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden

Programmatically creating tasks...

Someone had a question on an internal alias about the names of the “stock” tasks and how to know which is a stock task and which isn't. But, first let me back up a bit and give some history.

When we first started writing tasks, and the SSIS runtime was in its infancy, we had a number of problems related to creating tasks.

• Tasks written in managed code had fully qualified names that were very long and very hard to remember. (They still do)
• Task names were changing a lot. There was a lot of churn.
• Our task creation story hadn't stabilized yet.
• Task versions were changing a lot.
• We were writing a lot of ad hoc testing code.

Because of these issues, it was quite difficult to create a task programmatically. Plus, the code just looked gnarly.

With fully qualified names, the code looks something like this:

TaskHost execPkghost = (TaskHost)pkg.Executables.Add(“Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91”);

We wanted some way to shortcut the name. So we came up with stock names.

With stock names, the code to create a task looks something like this:

TaskHost execPkghost = (TaskHost)pkg.Executables.Add("STOCK:ExecuteSQLTask ");

A little easier to read and write, yes? The problem we have now is that there is code out there that uses the stock “monikers”. So, these names are here to stay. However, they aren't available for every task. Only tasks that the Integration Services team writes. They weren't designed to be extensible, but they are still useful. Maybe in a future version, we'll make it possible for everyone to use this convention. But for now, these are the only tasks that support the stock name:

• SQLTask
• ScriptTask
• ExecuteProcessTask
• ExecutePackageTask
• PipelineTask
• FTPTask
• SendMailTask
• MSMQTask
• FileSystemTask
• BulkInsertTask
• ActiveXScriptTask
• XMLTask
• WmiDataReaderTask
• WmiEventWatcherTask
• TransferStoredProceduresTask
• TransferLoginsTask
• ExecutePackageTask
• Exec80PackageTask
• WebServiceTask
• TransferObjectsTask
• TransferDatabaseTask

So, place a “Stock” qualifier before the names you see above like this, “STOCK:WmiDataReaderTask” and you can create the WMI Data Reader task.

Hope this helps.

Setting fonts...

There are a number of custom settings for the BI Dev. Studio that effect the look of the designer and how it works. Dev. studio is fairly complex though, and sometimes it's hard to find some settings.

Case in point:

How do you change the font the designer uses in the data flow data viewers.

• Select Tools | Options from the main menu
• In the resulting dialog box, select the Environment node
• Select Fonts and Colors
• In the drop down under "Show settings for:" there is an option to select "Business Intelligence Data Viewers".
• You can change the font there. I like "Rage Italic" myself. :)

There are a few other settings in the options dialog that effect the BI designer as well. Take a few minutes when you get a second and explore it.

Thanks,
Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden