Creating a DTS package with a Perl Script

#This perl script creates and executes a DTS Package in RAM to move table data
#The Package is set to use IAFastLoad which means it will run as fast as
#a BCP command.  All with the added bonus of no files on disk!
#Future enhancement : a feature to write the DTS Package to disk or SQL Server

use strict;
use Win32::OLE;
use Getopt::Std;
use Win32::OLE::Const 'Microsoft DTSPackage Object Library';
use Win32::OLE::Const 'Microsoft DTS Custom Tasks Object Library';
use Win32::OLE::Const 'Microsoft DTSDataPump Scripting Object Library';
use Win32::OLE::Variant;

my $Err = 0;
my $ErrMsg = "";


#Set the arguments
my (%Args, $Args); 
getopts('EU:P:S:D:O:s:d:o:', \%Args);
my $SourceServer = $Args{S};  
my $SourceDb = $Args{D};  
my $SourceObj = $Args{O}; 
my $DestServer = $Args{s}; 
my $DestDb = $Args{d};  
my $DestObj = $Args{o};
my $UserId = $Args{U};
my $Password = $Args{P};

#Create the Package
my $Package = new Win32::OLE 'DTS.Package';
$Package->{Name} = "DataMover";
$Package->{FailOnError} = 1;

#Create Connection objects and add them to the Package
my $Conn = $Package->Connections->New("SQLOLEDB");
$Conn->{Name} = "Source";
$Conn->{ID} = 1;
$Conn->{DataSource} = $SourceServer;
$Conn->{Catalog} = $SourceDb;
if ($Args{E})  
{
$Conn->{UseTrustedConnection} = 1;
} else 
{
$Conn->{UserId} = $UserId;
$Conn->{Password} = $Password;
}
$Package->Connections->Add($Conn);
$Conn=undef;
$Conn = $Package->Connections->New("SQLOLEDB");
$Conn->{Name} = "Destination";
$Conn->{ID} = 2;
$Conn->{DataSource} = $DestServer;
$Conn->{Catalog} = $DestDb;
if ($Args{E})  
{
$Conn->{UseTrustedConnection} = 1;
} else 
{
$Conn->{UserId} = $UserId;
$Conn->{Password} = $Password;
}
$Package->Connections->Add($Conn);
$Conn=undef;

#Create the DataPumpTask with IAFastLoad enabled (blazing fast)
my $Task = $Package->Tasks->New("DTSDataPumpTask");
$Task->{Name} = "CopyColumns";
my $CustomTask = $Task->{CustomTask};
$CustomTask->{SourceConnectionID} = 1;
$CustomTask->{SourceObjectName} = $SourceObj;
$CustomTask->{DestinationConnectionID} = 2;
$CustomTask->{DestinationObjectName} = $DestObj;
$CustomTask->{UseFastLoad} = 1;
$CustomTask->{FastLoadOptions} = 1;

#Create the Transformation and add it to the task
my $Transform = $CustomTask->Transformations->New("DTSPump.DataPumpTransformCopy");
$Transform->{Name} = "TransformData"; 
$Transform->{TransformFlags} = DTSTransformFlag_AllowLosslessConversion;

$CustomTask->Transformations->Add($Transform);

#Add the task to the Package
$Package->Tasks->Add($Task);
$Task=undef;

#Create the Package step and add it to the Package
my $Step = $Package->Steps->New();
$Step->{Name} = "Step1";
$Step->{TaskName} = "CopyColumns";
$Step->{ExecuteInMainThread} = 1;
$Package->Steps->Add($Step);
#$Step=undef;

#Execute the Package
$Package->Execute();
print Win32::OLE->LastError ."\n";

#Check for Errors
($ErrMsg, $Err) = &StepErrors;

if ($Err == -1)
{
print "$ErrMsg\n";
} else 
{
print "Package Completed Sucessfully\n";
}
$Package=undef;
exit $Err;

#Loop through the steps and look for Errors based on the ExecutionResult
sub StepErrors 
{
my $ErrorCode=Variant(VT_I4|VT_BYREF, "-1");
my $Source=Variant(VT_BSTR|VT_BYREF, "<none >");
my $Description=Variant(VT_BSTR|VT_BYREF, "<none >");
foreach my $Steps (in {$Package->{Steps}})
{
if ($Steps->{ExecutionStatus} == 4)
{
if ($Steps->{ExecutionResult} == 1)
{
$Err = -1;
$Steps->GetExecutionErrorInfo($ErrorCode,$Source,$Description);
$ErrMsg = "$ErrMsg \n Step $Steps->{Name} failed, Error:\n  $ErrorCode \n $Description \n";
}
}
}
return $ErrMsg, $Err;
}