#!/usr/local/bin/perl
#
# Library for importing data into SQL Server using DTS
# dtsBulkLoad.pm
#

package dtsBulkLoad;

use strict;
use warnings;
use DBI qw(:sql_types);
use Win32::OLE;
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;

# Return a new Win32::OLE DTS.Package object
sub getPackage {
	my ($name) = @_;
	my $Package = new Win32::OLE 'DTS.Package';
	$Package->{Name} = $name;
	$Package->{FailOnError} = 0;
	return $Package;
}

# Create a connection object to a source file and add to package object
sub connectToFile {
	my ($Package,$name,$id,$dataFile, $rowDelim, $colDelim, $textQual) = @_;
	my $Conn = $Package->Connections->New("DTSFlatFile");
	$Conn->{Name} = $name;
	$Conn->{ID} = $id;
	$Conn->{DataSource} = $dataFile;
	$Conn->{Reusable} = 1;
	$Conn->{ConnectImmediate} = 0;
	$Conn->{ConnectionTimeout} = 600;
	$Conn->{UseTrustedConnection} = 0;
	$Conn->{UseDSL} = 0;
	$Conn->ConnectionProperties->{"Data Source"}->{Value} = $dataFile;
	$Conn->ConnectionProperties->{"File Format"}->{Value} = 1;
	$Conn->ConnectionProperties->{"Row Delimiter"}->{Value} = $rowDelim || "\r\n";
	$Conn->ConnectionProperties->{"Column Delimiter"}->{Value} = $colDelim || ",";
	$Conn->ConnectionProperties->{"Text Qualifier"}->{Value} = $textQual || "\"";
	$Package->Connections->Add($Conn);
}

# Create a connection object to a destination table and add to package object
sub connectToDb {
	my ($Package,$name,$id,$server,$database) = @_;
	my $Conn = $Package->Connections->New("SQLOLEDB");
	$Conn ->{"Name"} = $name;
	$Conn ->{"ID"} = $id;
	$Conn ->{"DataSource"} = $server;
	$Conn ->{"Catalog"} = $database;
	$Conn->{"UseTrustedConnection"} = 1;
	$Conn->ConnectionProperties->{"Data Source"}->{Value} = $server;
	$Conn->ConnectionProperties->{"Initial Catalog"}->{Value} = $database;
	$Conn->ConnectionProperties->{"Integrated Security"}->{Value} = "SSPI";
	$Conn->ConnectionProperties->{"Persist Security Info"}->{Value} = 1;
	$Package->Connections->Add($Conn);
}

# Create a step object and add to package object
sub addStep {
	my ($Package,$stepName,$taskName) = @_;
	my $Step = $Package->Steps->New();
	$Step->{Name} = $stepName;
	$Step ->{TaskName} = $taskName;
	$Step ->{ExecuteInMainThread} = 1;
	$Package->Steps->Add($Step);
}

# Create a Task object and add a custom task, with source and destination details
sub addTask {
	my ($Package,$taskName,$SourceObj,$sourceId,$DestObj,$destId, $rColDefs) = @_;
	my $Task = $Package->Tasks->New("DTSDataPumpTask");
	$Task ->{Name} = $taskName;
	my $CustomTask = $Task->{CustomTask};
	$CustomTask ->{Name} = $taskName;
	$CustomTask ->{SourceConnectionID} = $sourceId;
	$CustomTask ->{SourceObjectName} = $SourceObj;
	$CustomTask ->{DestinationConnectionID} = $destId;
	$CustomTask ->{DestinationObjectName} = $DestObj;
	# Create a transformation object, with column details and add to custom task
	my $Transform = $CustomTask ->Transformations->New("DTS.DataPumpTransformCopy");
	$Transform ->{Name} = "TransformData"; 
	$Transform ->{TransformFlags} = 20;  # AllowNullChange and AllowStringTruncation
	addColumns($Transform, $rColDefs);   # Add columns using information in $rColDefs
	$CustomTask->Transformations->Add($Transform);
	# Add task to package
	$Package->Tasks->Add($Task);
}

# Add columns to the Transformations object using column information in $rColDefs
sub addColumns {
	my ($Transform, $rColDefs) = @_;
	my $colNum = 1;
	
	# For each entry from the column definitions array
	foreach my $def (@{$rColDefs}) {
		
		# Add source column info for the text file
		my $colName = sprintf("Col%03d", $colNum);
		my $SourceColumn = $Transform->SourceColumns->New($colName , 1);
		$SourceColumn->{Name} = $colName; 
		$SourceColumn->{DataType} = 129; 
		$Transform->SourceColumns->Add($SourceColumn);
		
		# Add destination column info for the database
		my $dataType = ($def->[1] ==  4) ? 3: 129;
		my $DestColumn = $Transform->DestinationColumns->New($def->[0] , 1);
		$DestColumn->{DataType} = $dataType; 
		$DestColumn->{Nullable} = 1; 
		$Transform->DestinationColumns->Add($DestColumn);
		
		# Increment column number
		$colNum++;
	}
}

# Add column definitions for a datatable to an array and return the array reference  
sub getColumnName {
	my ($database, $tableName) = @_;
	my @columnDefs = ();
	my $dbh = DBI->connect("DBI:ODBC:$database");
#	my $sql = "select * from $tableName";
	my $sql = "select top 1 * from $tableName";
	my $sth = $dbh->prepare($sql) or return 0;
	$sth->execute();
	for (my $count = 0; $count < scalar @{$sth->{NAME}}; $count++) {
		push @columnDefs, [$sth->{NAME_lc}[$count], $sth->{TYPE}[$count]];
	}
	if (scalar @columnDefs > 0) {
		return \@columnDefs;
	} else {
		return 0;
	}
}

1;



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.