Published on

November 29, 2008

Exploring SQL Server with PerlDTS

SQL Server is a powerful database management system that offers various features for managing ETL processes. One of the tools that can be used with SQL Server is DTS (Data Transformation Services), which provides automation, scheduling, and notification capabilities. While DTS is often used as a RAD (Rapid Application Development) tool, its true potential can be unlocked by utilizing its exposed API.

In this article, we will explore PerlDTS, a project that aims to simplify the usage of DTS API with Perl. Perl is an interpreted programming language that supports COM, making it a suitable choice for executing DTS packages outside of Enterprise Manager or scheduling tasks.

PerlDTS provides a set of Perl classes that represent DTS classes in a more natural way for Perl programmers. By using PerlDTS, tasks that were previously tedious to solve with click-and-drag procedures in DTS designer can now be easily automated using Perl code.

Let’s take a look at some examples to get a better understanding of PerlDTS. In the first example, we will connect to a SQL Server database and search for DTS packages with a name that matches a regular expression:

use warnings;
use strict;
use XML::Simple;
use DTS::Application;

my $xml_file = 'modify.xml';
my $xml = XML::Simple->new();
my $config = $xml->XMLin($xml_file);
my $app = DTS::Application->new( $config->{credential} );

my $pkg_info;
print 'Enter a regex for the package name: ';
my $regex = <STDIN>;
chomp $regex;

my $list_ref = $app->regex_pkgs_names($regex);
map { print $_, "\n" } @{$list_ref};

In this code, we first load the necessary modules and create an instance of the DTS::Application class. We then prompt the user to enter a regular expression to match the package name. The method regex_pkgs_names is called on the DTS::Application object to retrieve a list of package names that match the provided regex. Finally, we print the list of package names.

In another example, we will backup DTS packages by saving them in a zip file:

use warnings;
use strict;
use XML::Simple;
use DTS::Application;
use constant XML_FILE => 'modify.xml';
use constant BACKUP_DIR => 'c:\\DTS-backup';
use Archive::Zip qw(:ERROR_CODES :CONSTANTS);
use DateTime;
use Cwd;

my $xml = XML::Simple->new();
my $config = $xml->XMLin(XML_FILE);
my $app = DTS::Application->new( $config->{credential} );

my $pkgs_list = [ $config->{package} ];
my $counter = 0;

foreach my $pkg_name ( @{$pkgs_list} ) {
    my $pkg;
    print 'Saving ', $pkg_name, '... ';
    eval {
        $pkg = $app->get_db_package({ id => '', version_id => '', name => $pkg_name } );
    };
    if ($@) {
        warn $@, "\n";
    } else {
        $pkgs_list->[$counter] = $pkg_name . '.dts';
        $pkg->save_to_file( BACKUP_DIR, $pkgs_list->[$counter] );
        print 'done.', "\n";
        $counter++;
    }
}

pack_files($pkgs_list);

sub pack_files {
    my $pkg_list = shift;
    my $zip = Archive::Zip->new();
    my $old_path = getcwd();

    chdir(BACKUP_DIR);
    foreach my $file ( @{$pkg_list} ) {
        $zip->addFile($file);
    }

    die "Could not create the ZIP file: $!\n" unless ( $zip->writeToFileNamed( get_backup_filename() ) == AZ_OK );

    foreach my $file ( @{$pkg_list} ) {
        unlink $file or warn "Cannot remove file $file in " . BACKUP_DIR . ': ' . "$!\n";
    }

    chdir($old_path);
}

sub get_backup_filename {
    my $today = DateTime->now();
    return $today->year() . $today->month() . $today->day() . $today->hour() . $today->minute() . $today->second() . '.zip';
}

In this example, we first load the necessary modules and create an instance of the DTS::Application class. We then retrieve the list of package names from the configuration file and iterate over each package. For each package, we fetch it as a DTS::Package object and save it to a file using the save_to_file method. We also add the package file to a zip archive using the Archive::Zip module. Finally, we remove the individual package files and create a zip file with a timestamp in the filename.

These examples demonstrate the power and flexibility of PerlDTS in working with DTS packages. Whether it’s searching for packages, backing them up, or modifying their properties, PerlDTS provides a convenient and efficient way to interact with SQL Server.

It’s important to note that PerlDTS is not intended to replace the DTS designer for creating new packages. Instead, it complements the designer by providing additional capabilities for executing packages, scheduling tasks, and generating reports. By leveraging the strengths of both Perl and DTS, developers can achieve more efficient and powerful data transformations.

If you’re interested in exploring PerlDTS further, you can find the project documentation and UML diagrams on the PerlDTS project website. The documentation is complementary to the official Microsoft SQL Server documentation and provides additional insights into using Perl with DTS.

In conclusion, PerlDTS offers a valuable toolset for working with SQL Server and DTS packages. Whether you’re a system administrator or a programmer, PerlDTS can simplify your ETL processes and enhance your productivity. Give it a try and see how it can streamline your SQL Server tasks!

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.