#!/usr/local/bin/perl
#
# Program dtsBulkLoad.pl for using Win32::OLE to create and run a 
# dts package for importing a text file into SQL Server
	
use strict;
use dtsBulkLoad;
use Win32::OLE::Variant;

my ($server, $database, $table, $dataFile);

# Check for 4 arguments; server, database, table and data-file
if (scalar @ARGV == 4){
	($server, $database, $table, $dataFile) = @ARGV;
} else {
	die "Call with 4 arguments: server, database, table and data-file\n";
}

# Define variables
my $dataTable= "[$database].[dbo].[$table]";
my $sourceId = 1;
my $destId = 2;
my $rowDelim = "\r\n";
my $colDelim = ",";
my $textQual = "\"";

###  Create the package object
my $Package = dtsBulkLoad::getPackage("DataLoader");

### Create Source and Destination Connection objects and add to package object
dtsBulkLoad::connectToFile($Package,"Source",$sourceId,$dataFile,$rowDelim,$colDelim,$textQual);
dtsBulkLoad::connectToDb($Package,"Destination",$destId,$server,$database);

### Create a step object and add to package object
dtsBulkLoad::addStep($Package,"Step1","CopyColumns");

### Get the column definitions for the table 
(my $rDefs = dtsBulkLoad::getColumnName($database, $table)) || die "No def\n";

### Create a task object using column definitions to add columns to the task
### Add the task to package object
dtsBulkLoad::addTask($Package,"CopyColumns",$dataFile,$sourceId,$dataTable,$destId,$rDefs);

#Execute the package
$Package->Execute();

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

if ($Err == -1){
	print "$ErrMsg\n";
} else {
	print "Package Completed Successfully\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, "");
	my $Description=Variant(VT_BSTR|VT_BYREF, "");
	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;
}


Colin



The information in this email transmission and any files transmitted with it are intended solely for the use of the individual or entity to which they are addressed and may contain confidential and privileged information. Any third party dissemination, distribution, copying or use of this communication, without prior permission, is strictly prohibited. Any attachments may contain viruses that could damage your computer. While we have taken every reasonable precaution to minimise this risk, we cannot accept liability for such damage and you should carry out your own virus checks before opening any attachments. If you have received this email in error please notify the sender as soon as possible and delete all copies of this email.

Copyright (C): 2006 By Solexa Ltd. All Rights Reserved.