Win32::SqlServer::DTS - Perl classes to access Microsoft SQL Server
    2000 DTS Packages


    Although it's possible to use all features here by using only
    Win32::OLE module, Win32::SqlServer::DTS (being more specific, it's
    childs classes) provides a much easier interface (pure Perl) and
    (hopefully) a better documentation.

    The API for this class will give only read access to a package
    attributes. No write methods are available are directly available at
    this time, but could be executed since at each DTS object created a
    related object is passed as an reference to new object. This related
    object is a MS SQL Server DTS object and has all methods and properties
    as defined by the MS API. This object reference is kept as an "private"
    property called _sibling and generally can be obtained with a
    get_sibling method call. Once the reference is recovered, all methods
    from it are available.

    The Win32::SqlServer::DTS class does not much: it will server only as
    an interface class, since it cannot be instancied or the available
    methods be called directly (as an abstracted class). The inheritance
    will help only to make available easier (and globally) access to the
    methods kill_sibling and get_sibling.

 Why having all this trouble?

    You may be asking yourself why having all this trouble to write such
    API as an layer to access data thought Win32::OLE module.

    The very simple reason is: MS SQL Server 2000 API is terrible to work
    with (lots and lots of indirection), the documentation is not as good
    as it should be and one has to convert examples from it of VBScript
    code to Perl.

    Win32::SqlServer::DTS API was created to provide an easier (and more
    "perlish") way to fetch data from a DTS package. One can use this API
    to easily create reports or implement automatic tests using a module as
    Test::More (see EXAMPLES directory in the tarball distribution of this

    Current development state should be considered BETA, despite the API is
    already usable. There is a high chance that the interface changes
    during next releases, so be careful when updating.





    Returns the relationed DTS object. All objects holds an reference to
    the original DTS object once is instantiated, unless the kill_sibling
    is executed.

    If the reference is not available, it will abort program execution with
    an error.


    Validates if the attribute _sibling is defined and has a valid value.
    Returns true if it's ok, false otherwise.


    This method will simple delete the key (or attribute, if you prefer)
    _sibling from the hash reference used by all classes that inherints
    from DTS class. Once the key is removed, the Perl garbage collector
    will remove the related object created using the MS SQL Server 2000.

    The reasons of why doing such thing is described in CAVEATS.


    Uses the Data::Dumper Dumper function to print to STDOUT the properties
    of a given object that inherints from Win32::SqlServer::DTS (almost of
    all classes in the API).

    The way this is implemented is to do a dirty clone of the original
    object, but without the _sibling attribute. This allows to quickly
    check the object state. This is not as good as it could be, but
    sometimes the Perl debugger dies while checking DTS objects, so it's
    better than nothing.

    Maybe in the future this method is replaced to turn on debug mode for
    all methods calls using Log::Log4perl module.


    All objects under DTS distribution cannot be created without a
    reference to the original DTS object they mimic: at the current
    development state, object can only be recovered from a MS SQL Server
    database. Some classes may have methods to change their inner
    attributes, other classes don't. Check the POD for each class to be
    sure, but future releases should have write methods for all classes

    DTS distribuition replicates several DTS classes, but it is still
    INCOMPLETE. There are many classes there were not replicated, like Bulk
    Insert Task or Transformation. Check the UML in the project website for
    an overview of which classes are implemented.

    The sibling object, kept as an reference, sometimes is quite annoying.
    This because the MS SQL Server API uses a lot of indirection. Using
    Data::Dumper, for example, seems impossible. Using the x (eval) command
    in the debugger sometimes also shows a interesting visual effect, but
    is equally useless (setting maxdepth should help).

    There are serious problems using the Perl debugger, since it seems to
    crash everytime there is more than an object instantied (if there is a
    _sibling attribute involved). The solution until now is use the debug
    method or using a module like Log4Perl to detect issues in the code.

    If you need to persist any object created, first remove the sibling
    object using the kill_sibling method. As said before, it was detected
    issues with the Data::Dumper Dumper function, but there are no
    garantees that invoking kill_sibling will solve the issue, since this
    probably also depends on Perl garbage collector. Anyway, persisting a
    DTS object will do no good if you need to execute methods that depends
    on the sibling attribute since those methods are based on remote
    requests with COM.

    kill_sibling probably will help also regarding memory using, althought
    this was not tested formally.

    Once this API is built over Win32::OLE module, one will only be able to
    use Win32::SqlServer::DTS modules in a MS Windows operational system
    that also supports the installation of the MS SQL Server Enterprise
    Manager, at least the client part of the application, to be able to use
    the original DTS API that comes with the MS SQL Server client. All
    issues from Win32::OLE applies too. Since release 0.04, DTS
    distribution will die if used in any other operational system. See
    Devel::AssertOS for more implementation details.

    Win32::SqlServer::DTS modules were tested with MS SQL Server 8 (or
    2000, if you prefer) so maybe some methods will fail if tried on
    previous versions of MS SQL Server.


      * Win32::OLE at perldoc.

      * Data::Dumper at perldoc.

      * MSDN on Microsoft website and MS SQL Server 2000 Books Online are a
      reference about using DTS' object hierarchy, but one will need to
      convert examples written in VBScript to Perl code.

      * README file in the module distribuition about how to enable
      extended tests for the API.

      * Project website at for more
      information, including UML diagrams and Subversion repository.


    Alceu Rodrigues de Freitas Junior, <>


    Copyright (C) 2006 by Alceu Rodrigues de Freitas Junior

    This library is free software; you can redistribute it and/or modify it
    under the same terms as Perl itself, either Perl version 5.8.8 or, at
    your option, any later version of Perl 5 you may have available.