Does anyone else have experience using Perl with SQL Server DTS? I've been using it successfully with SQL Server 2000, and decided to search perlmonks to see if there were any related postings, but I didn't find any. I'm just looking for any tips or cautions that anyone might have to offer.

Searchs on both Google and MSDN yielded few results. About the only useful information I found was that MS claims that VBScript is the fastest language for DTS, followed by JScript [sic] and then PerlScript. One site mentioned that MS had disclaimed PerlScript for DTS since it had not fully tested it; however, I couldn't find anything on MSDN to this effect.

For those who are curious about how you use Perl with DTS, it works like this: If you use the ActivePerl build for Win32, it includes PerlScript, which is an ActiveX scripting engine that enables the use of Perl with anything that supports ActiveX script languages. This includes things like Active Scripting Pages, Windows Scripting Host and SQL Server DTS.

Here are a few things I have learned:

• The 'use strict' pragma seems to cause problems. It appears that when DTS calls the Main() subroutine which is a required element of any ActiveX script in DTS, this violates the strict pragma. However, 'use warnings' works fine.
  
• If you want to have variables with global scope that are visible across DTS tasks, you have to use the DTSGlobalVariables collection. Calling this object every time you want to access a global variable can be a pain, so I wrote the following functions to help out.

  sub getDTSGlobal
  #gets DTS global variable values
  #returns scalar or list depending on calling context
  {
      my @vals;
      for (@_) {
          push @vals, $DTSGlobalVariables->Item($_)->{Value};
      }
      return wantarray ? @vals : $vals[0];
  }
  [download]

  sub setDTSGlobal
  #sets DTS global variable values
  #repeats for as many name, value pairs as it receives
  {
      my %pairs = @_;
      for (keys %pairs) {
          $DTSGlobalVariables->Item($_)->{Value} = $pairs{$_};
      }
      return;
  }
  [download]

  
  
• Data transformations can also involve a lot of typing, so I also wrote some similar functions for that purpose.

  sub getDTSSource
  #gets DTS source values
  #returns scalar or list depending on calling context
  {
      my @vals;
      for (@_) {
          push @vals, $DTSSource->Item($_)->{Value};
      }
      return wantarray ? @vals : $vals[0];
  }
  [download]

  sub setDTSDestination
  #sets DTS destination values
  #repeats for as many name, value pairs as it receives
  {
      my %pairs = @_;
      for (keys %pairs) {
          $DTSDestination->Item($_)->{Value} = $pairs{$_};
      }
      return;
  }
  [download]

So, if anyone else has experience with using Perl with DTS, I'd appreciate hearing from you.

---jehuni