DTS Custom Task: Office XML Writer

dougbert — Sat, 21 Jun 2008 20:02:19 GMT

This Zip file contains the Visual Basic.NET source code for a DTS (not SSIS) custom task that I wrote in 2004 and only recently (2008) rediscovered. I wrote this code (1) to learn to write a DTS custom task (2) and to demonstrate how to write out Excel data without using the Excel driver or the Excel application. I threw in creating a Word table just to prove that I could.

The task lets you choose whether you want a new Excel workbook and worksheet, a new Word document and table, or both. It also lets you choose whether to display the output by launching (1) Notepad to view the XML output as text, or (2) Excel and Word to view the output in the intended applications, or both. Needless to say this latter feature was intended for a development and testing environment, and not for production!

I'm uploading the file since it may be of interest to anyone who still writes DTS custom tasks, or who's interested in writing out Office XML.

Disclaimers: This isn't production code, I haven't upgraded it for the current version of Visual Studio, the Excel and Word XML standard may have evolved (especially Excel), and so forth.

-Doug

DTS Application Properties

dougbert — Mon, 26 May 2008 20:40:26 GMT

There's a dialog box of DTS application-wide settings that disappeared from SQL Server 2005 and 2008. It used to live on the context (right-click) menu of the DTS node in SQL Server 2000 Enterprise Manager. One day in a moment of spare time I set about to re-create it for use on a SQL Server 2005 or later computer with DTS support installed. The Visual Basic.NET source code is contained in this file. Here's now my Windows Form (right) compares to the original DTS dialog on the left...not bad, huh? I even gave it an accurate title!

I suspect that the "refresh cache" functionality is not useful, because I assume that the data is cached in memory in the process of my little standalone app, and can't be used by the DTS runtime...but at least you see the DTS API calls.

Data Transformation Services downloads

DTS Custom Task: Office XML Writer

DTS Application Properties