Programmatic Control of SSIS

From IronPython Cookbook

Disclaimers:

1) Run from the server hosting MSSQL 2005 (valid license).

2) Tested under Windows 2003 Server, IronPython 2.0, .Net framework 2.0 SP 1

  Changed from IronPython 1.1 version - SSIS objects do not allow straight 
  Python name and index lookup (e.g., sequencex['foo'] or sequencex[3]). 

3) Not tested with Mono

This is a very simple example of programmatic control of an SSIS package. The package is stored in a file. Its function is a simple legacy copy column dump as would be done in SQL Server 2000's COM-driven DTS package.

"""
Collection of utilities for programmatically
controlling SQL 2005 SQL Server Integration
Services (SSIS) packages with IronPython-2.0.
"""

import clr

clr.AddReference('Microsoft.SQLServer.ManagedDTS')

from Microsoft.SqlServer.Dts import Runtime

def getpackage(packagename):
    """
    Returns SSIS package with given name.
    Loads package from file.
    """
    app = Runtime.Application()
    return app.LoadPackage(packagename, None)

def getssisexecutables(package):
    """
    Returns collection of SSIS packages' Executables
    from a given SSIS package. 

    Example Executable:  DataFlow
    """
    return package.Executables 

def gettasks(executable):
    """
    Returns collection of SSIS tasks for a
    given executable. 
    
    Example - DataFlow Executable yields collection of 
    OLE DB Source, Copy Column, and Flat File Destination.
    """
    innobj = executable.InnerObject
    return innobj.ComponentMetaDataCollection
 
def gettaskobject(tasks, objectname):
    """
    Looks for object with objectname in
    collection of tasks (ComponentMetaDataCollection).
    Returns object if it finds it,
    False otherwise.
    """
    for task in tasks:
        if task.Name == objectname:
            return task
    return 0 

def setsqlforoledb(task, sqlstring):
    """
    Specific to OLEDB - directly manipulating
    SQL input to task.

    Sets the SQL string to sqlstring for the task.
    """
    for prop in task.CustomPropertyCollection:
        if prop.Name == 'SqlCommand':
            prop.Value = sqlstring
    return 0

This is how it is run:

OLEDBTASKTYPE = 'OLE DB Source'

newsql = 'SELECT * FROM mysmalltable'
 
print 'running SSIS package . . .'
pkg = getpackage('mypackagefilename.dtsx')
excbls = getssisexecutables(pkg)
# assumes only one executable -
# a complex package will have more than one,
# and will need to be referenced appropriately
tasks = gettasks(excbls[0])
pulltask = gettaskobject(tasks, OLEDBTASKTYPE) 
setsqlforoledb(pulltask, newsql)
success = pkg.Execute()

SSIS is a fairly comprehensive tool, with additions and enhancements to the old DTS functionality of pre-SQL 2005 Server releases. This simple example should serve to get someone trying to call SSIS from IronPython started.

Back to Contents.