Programmatic Control of SSIS

From IronPython Cookbook

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

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

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.
    """
    try:
        return tasks[objectname]
    except KeyError:
        return False

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

    Sets the SQL string to sqlstring for the task.
    """
    task.CustomPropertyCollection['SqlCommand'].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.