Programmatic Control of SSIS

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) tasks = gettasks(excbls[0]) pulltask = gettaskobject(tasks, OLEDBTASKTYPE) setsqlforoledb(pulltask, newsql) success = pkg.Execute
 * 1) assumes only one executable -
 * 2) a complex package will have more than one,
 * 3) and will need to be referenced appropriately

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.