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.
- 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.
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
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).
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.
Version 0.1.27 (14/11/1999) – First Public Release Version