PaddingTaskNovember 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.
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).
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 |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
