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

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.