dcsimg

PaddingTask

November 15, 1999

Sometimes flat files (fixed width files) have shorter lines than the specification dictates, as some systems do not output spaces for blank fields at the end of the line. This will cause problems for bcp, Bulk Insert and the DTS Text File connections. To overcome this I have produced an ActiveX DLL which can be called directly from T-SQL and a DTS Custom Task for use within the designer.

Thanks to Magnus Karlsson for the news thread DTS importing fixed text files (Deja.com Link), and Euan Garden for the blatent suggestion of writing a custom task. It works better than the old VB exe I used previously.

The Custom Task has a user interface which allows you to select the file in three ways.

  • Text File Connection
  • Bulk Insert Task
  • Specified File
Use the Text File Connection or Bulk Insert Task option if you assign the filename dynamically via an Active Script Task. Provided the PaddingTask is run after this Active script, it will read the most current file from the target object's properties. You can of course specify the file directly.

Padding Task Screen Shot


Installation

  • Download and extract the archive to a suitable location.
  • Register PadFileVB.dll (Run Regsvr32 x:\YourPath\PadFileVB.dll)
  • From within the DTS Designer select Register Custom Task from the Tasks menu
  • Supply a suitable description such as Padding Task
  • Supply the Task location (Find PaddingTask.dll in the extraction directory)
  • Supply the Icon location (Find PaddingTask.ico in the extraction directory)
  • Click OK and you are ready to go.


Usage Notes

The line length parameter is the maximum number of characters according to the file specification. Lines that fall short of this will be padded with spaces to the correct length.

When designing the package, ensure that PaddingTask is executed prior to any flat file transformation or bulk insert operations, using suitable workflow constraints.

PadFileVB.dll is what actually parses the file and appends spaces as required. You can call it from T-SQL, via the sp_OAxxx stored procedures independently of DTS and the custom task. See the sample stored procedure sp_PadFileVB (sp_PadFileVB.sql) in the download archive. This requires the Microsoft OLE Automation Return Codes and Error Information stored procedures as detailed in SQL Server Books Online (msoleerror.sql)

At some stage I would like to replace PadFileVB.dll with C or C++ version to increase performance. (Unfortunately my C++ is not up to much). Keeping it separate from PaddingTask.dll means I've just got to change the line that calls it. If anyone can help with this, then please contact me.

Both the PadFileVB.dll and PaddingTask.dll require Microsoft Visual Basic 5 Runtimes.

MS SQL Server 7 Service Pack 1 is required for this Custom Task. Without SP1 you will encounter automation errors when executing packages from the DTS designer. (See SQL Links).


Download

Please note that this program is provided without any warranty or assurances of any kind. Use it at your own risk. Commercial distribution or distribution for profit in any form is strictly forbidden without prior permission from the author.

Any
comments would be greatly appreciated.
Download PaddingTask (~25K).

Version History

Version 0.1.27 (14/11/1999) - First Public Release Version








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers