Article:		Faster Filtered SQL Server Imports
Subject:		Questions about BCP and CPAN
Date:		2007-01-06 23:17:21
From:		glasswalk3r
Hello there, Nice article! I really liked it but I cannot avoid asking two questions about the process you described: 1 - Why not using bcp program? One could try to execute bcp directly using system function or any other. Just supposing, this would not be faster than using Win32::OLE to get access to DTS API? The Perl module could generate a configuration file for BCP in a fly. 2 - Are you thinking about in creating this module of yours as a generic API in CPAN? Thanks!

Showing messages 1 through 2 of 2.

• Questions about BCP and CPAN
  2007-01-15 01:21:41  CPG [Reply | View]
  
  
  As an example of the flexibility of using this DTS module, you only need to adjust two parameters to import fixed width data from a file. In the connectToFile() subroutine, set the ‘File Format’ to 2, and replace the ‘Column Delimiter’ parameter with ‘Column Lengths’, set to the lengths of the fixed width fields. For example, if there are three fields of length 6, 25 and 25, use the following settings (passed as parameters):-
  
  $Conn->ConnectionProperties->{"File Format"}->{Value} = 2;
  $Conn->ConnectionProperties->{"Column Lengths"}->{Value} = "6,25,25";
  

• Questions about BCP and CPAN
  2007-01-08 08:06:09  CPG [Reply | View]
  
  
  Thanks for the questions.
  
  1. The simple answer is that, before I wrote this module, I used DTS rather than BCP for my data loading. BCP probably would have been quicker for bulk loading data from flat-files, but it did not have the flexibility of DTS for controlling the different data sources, performing data transformations, etc. The article gives an example of working with the DTS object model but, due to space considerations, does not use the full range of available DTS features.
  
  2. I had not planned to put this on CPAN, but if people were interested, I could do.