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 5 of 5.

• 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.
  
• Publishing on CPAN
  2007-01-16 11:26:51  glasswalk3r [Reply | View]
  
  
  Hello there,
  
  I've been working in a API layer over Win32::OLE and MS SQL Server API to fetch data related to DTS packages. This API hides all complex structures required from Win32::OLE and, with some degree of luck, the need to convert VBscript from the SQL Server documentation.
  
  Probably I'll take a namespace simple as "DTS". Till now, I have just some classes representing the DTS tasks like SendMailTask and ExecutePackageTask. I had used this API successfully to implement unit tests in the DTS packages I had to develop as ETL interfaces in a project I had worked.
  
  I'll probably publish it on CPAN in a week or two. Maybe you would like to extend the DTS::Task class to implement the BulkInsertTask object. ;-)
  
• Publishing on CPAN
  2007-03-06 06:35:18  glasswalk3r [Reply | View]
  
  
  All right guys, here it is: http://search.cpan.org/search?query=dts&mode=all
  
  It would be nice to received some feedback about the new API.
• Publishing on CPAN
  2007-03-06 07:24:50  CPG [Reply | View]
  
  
  Hi,
  
  Sorry for not replyint to your last email; we have just gone through a merger! I will try to take a look at this and get back to you.
  
  CPG

• 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";