Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Nov 15, 1999

PaddingTask

By Darren Green

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM