Apr
28
2009

HTML Table Destination: An (incomplete) SSIS custom data flow component

I had fun recently creating a custom data flow destination for Integration Services - an HTML Table Destination that writes out the data from the data flow to an HTML file as an HTML table. No applause, please - I copied SSIS developer Matt Masson's code from Codeplex to get started, including his handy helper functions for the tedious stuff like adding custom properties and caching input columns. Plus, it's not rocket science to output tabular data to a well-known text-based markup format. You too could be... [More]
Apr
27
2009

Off topic? Pretty charts, and SSIS in Siberia

I was crushed by the news that Microsoft AdCenter Analytics was being discontinued. As a beta user, I'd enjoyed the pretty charts about visits to my blog. Where was I going to get pretty charts now (without implementing BI of my own, at least)? So I added the free ClustrMaps widget to my page (you'll see it in the right-hand sidebar if you scroll down far enough), and I think the results are fascinating: Random observations... See that dot out there in Siberia, on the Mongolian border? Wouldn... [More]
Apr
13
2009

Logging custom events for Integration Services tasks

All Integration Services tasks and containers support a default set of events for logging. These events typically represent different stages in the "lifetime" of the object at run time, such as "OnPreExecute", "OnPostExecute", and "OnError". Many tasks also support custom log entries that may be of more interest to you than the generic events. You can find a list here in BOL (although I see that the list isn't 100% up to date): Custom Messages for Logging. A handful of the interesting custom log entries are... [More]
Apr
12
2009

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 emp... [More]
Apr
9
2009

SSIS and clustering: What you should do instead

Lots of customers ask about configuring SQL Server Integration Services in a failover cluster. We recommend that you DON'T configure SSIS as a cluster resource. There are almost no benefits to doing so, and you can gain many of the benefits that you want by simple configuration changes. By editing the configuration file for the SSIS service on each node of a cluster, you can manage the packages on any node from any other node. For more information, please see the Books Online topic, Configuring Integrat... [More]
Apr
7
2009

Understand how SSIS package configurations are applied

This week I'm cleaning up our BOL documentation about how SSIS package configurations are applied, and how the behavior has changed between SQL Server 2005 and SQL Server 2008 Integration Services. These changes affect what you can and can't do to change your design-time settings at run time. My cleanup was prompted, as is often the case, by a kick in the butt from Microsoft Support and its customers. I admit that it has taken me a while to figure out all the ifs, ands and buts. SQL Server 2008... [More]
Apr
7
2009

Setting the DataType of IDTSParameterBinding objects (Execute SQL Task)

If you're writing Integration Services code that uses the Execute SQL task, you also have to create parameters programmatically. The ParameterBindings property of the ExecuteSQLTask returns an IDTSParameterBindings collection of IDTSParameterBinding objects, each of which has a DataType property. But the DataType is just an integer. Where do the values come from? Why doesn't it just get its values from an enumeration that's clearly documented? Well, the values for the DataType of an IDTSParameterBinding dep... [More]
Mar
31
2009

Import from a macro-enabled Excel 2007 workbook (*.xlsm) (CORRECTED)

CORRECTED ON 4/2/09 FROM ORIGINAL POSTING ON 3/31/09 The Office 12 ACE Provider recognizes the .XLSM extension for a macro-enabled Excel 2007 workbook. However, the Excel Connection Manager (in SQL Server 2008 Integration Services) only recognizes the .XLSX file extension for Excel 2007 files. Therefore you have to use the OLE DB Connection Manager (and Source and Destination) with Excel 2007 files that have a file type and file extension other than .XLSX. Importing. You can connect to, and import from... [More]
Mar
27
2009

Get looped, with your own custom Foreach Enumerator for SSIS

The lack of control flow structures such as looping was one of the main shortcomings of  Data Transformation Services (DTS). If you're nostalgic, just look at the scripting hoops that you had to jump through to loop, by disabling and re-enabling steps in the DTS package. Then Integration Services came along and blessed us with the Foreach Loop Container! Now your package can loop over most of the things you want to, just by selecting the right enumerator and setting properties in the dia... [More]
Mar
17
2009

Find the top Knowledge Base articles for DTS

Here are the top Microsoft Support Knowledge Base articles that SQL Server Data Transformation Services (DTS) customers have been reading. You may find them useful also. This time I've simply sorted the list from most viewed to least view, for the cumulative timeframe of FY06 through the incomplete F09. Yet one more big thank-you to my buddy Jason Howell, the SSIS guru in Microsoft Support, for sending us this additional list. KB No. Title 269074 INF: How to Run a DTS Package as a S... [More]