Dougbert.com

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

Upgrade mapping schema for custom components, SSIS 2005 to 2008

dougbert — Tue, 10 Jun 2008 14:02:40 GMT

This is the schema for the mapping file that must be provided by developers of custom components to their customers, to support the automated upgrade of packages that contain their custom objects from the 2005 package and component version to the 2008 version.

For more information, see the Books Online topic Upgrading Custom Objects for SQL Server 2008 Integration Services.

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.

Excel Driver Issues and Tips from the Microsoft KB

dougbert — Mon, 26 May 2008 20:13:28 GMT

This text file contains a categorized list of Microsoft Knowledge Base articles related to the use of the Excel database driver distributed with the Jet database engine. The lists contains both known issues and how to articles. It has not been updated since 2003, and may not contain more recent articles.

There is a finite number of requirements for, and known issues with, the Excel driver, and I'm confident that all of them have been uncovered already during its long life. However they manifest themselves differently depending on the client application -- ADO or ADO.Net code, DTS or Integration Services, Access, or SQL Server linked servers and distibuted queries. The answer is somewhere in this list.

-Doug

Enhanced Error Output

dougbert — Sat, 10 May 2008 16:32:50 GMT

This sample demonstrates one approach for adding both ErrorDescription and ErrorColumnName to an Integration Services Error Output. The zip file contains a Visual Studio solution with 3 main items:

a script to create the SQL Server reference table for storing column information;
a console application in VB.NET that populates the reference table;
a package that demonstrates how to look up the column information and populate the 2 additional columns of error information

To run this sample on your computer, you have to make some minor edits to each item:

In the script, enter your database name in the USE statement and change the table name if you prefer.
In the console application, change the string constants at the top of the code for the database name, and the folder location of the packages that you want to use as a test.
In the package, fix the connection manager to connect to your test database and table.

OPML file for multiple SSIS blogs, April 2008

dougbert — Tue, 29 Apr 2008 02:14:43 GMT

This OPML file that you can import into Internet Explorer, Outlook or another tool lets you subscribe all at once to all the Integration Services blogs that I was able to locate in April, 2008. Please let me know about blogs that I've overlooked!