THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Self Modifying Packages in SSIS?

Many visitors to the SSIS forum ask if it is possible to modify an executing package using a Script Task within that package; this was a fairly common scenario in the predecessor Data Transformation Services (DTS) so its not surprising that people ask this. The simple answer is “no” and in days of yore I would direct people to a blog post written by Kirk Haselden (former Development head honcho for SSIS) entitled “Self Modifying Packages in SSIS?” in which he explained the why and wherefores of this scenario. That original blog has since disappeared never to be seen again however I did stumble upon it on a plagiarist’s blog earlier this week and figured it might be better if it were re-published somewhere where (a) it had Kirk’s permission (b) was a bit more relevant and (c) was more likely to stick around for a while. Hence, I contacted Kirk and asked him if I could re-publish that blog post here on SQLBlog; he agreed and so you can find the entirety of that blog post below. Bear in mind that this was written for SSIS version 1 however it is still very much relevant today.

Without further ado…


Self Modifying Packages in SSIS? by Kirk Haselden

Yeah, thought that might get your attention. :)

First, packages cannot modify themselves during execution. There is no package pointer passed to the tasks any longer, so you can’t traverse the package object model with the script task any longer. That is, you can’t traverse the package object model for package in which the script task resides. You CAN however open and modify other packages, including those that the parent package is about to execute with the Execute Package task. This is the same model as self modifying packages in DTS, except it’s safer because you’re not attempting to change the package as it is running.

Here’s the script from a chapter of my book that shows you how to modify a Transfer Objects Task to move some tables. There is no error handling code for clarity, bla bla bla. The usual caveats apply, check for errors, handle exceptions.

The Script

Public Sub Main()
  Dim application As Microsoft.SqlServer.Dts.Runtime.Application = New Application()
  Dim packagename As Object = Dts.Connections(“Tables”).AcquireConnection(Nothing)
  Dim package As Microsoft.SqlServer.Dts.Runtime.Package = application.LoadPackage(packagename.ToString(), Nothing)
  Dim th As TaskHost
  th = package.Executables(“TablesToMove”)
  
  Dim sc As StringCollection = New StringCollection()
  sc.Add(Dts.Variables(“Tables1″).Value.ToString())
  sc.Add(Dts.Variables(“Tables2″).Value.ToString())
  th.Properties(“TablesList”).SetValue(th, sc)
  application.SaveToXml(packagename, package, Nothing)
  Dts.TaskResult = Dts.Results.Success
End Sub

This is some quick and dirty code that uses the package object model to open the child package and modify it.

Table Names

In the parent package there are a couple of variables I use, Tables1 and Tables2, to change the table I want to move. Now, you can change this how you want. Stick them all in a comma delimited string if you want and parse them out in script or stick them in a variable one-by-one with a ForEach Nodelist Enumerator. It doesn’t really matter how you get the table names into the script.

The Package Connection

The script uses the same Connection Manager as the Execute Package task to find the name of the package to open and modify.

Modifying the Package

Now the property type for the Transfer Objects Task for the TableList is a StringCollection. So, I create a StringCollection in script and populate it. Then I open the child package, the one that will be moving tables, and set the property on the Transfer Objects Task and save the package again.

Published Saturday, July 16, 2011 1:48 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

It's too bad that Kirk has forgotten that he has another blog... on THIS SITE!

http://sqlblog.com/blogs/knightreign/

July 19, 2011 10:20 AM
 

swapnil said:

Article doesn't provide full information.

November 15, 2011 6:37 AM
 

jamiet said:

Neither does your comment.

November 15, 2011 6:47 AM
 

Diwakar kumar said:

Hi friends

i am new at ssis and and using multiple .dtsx xml file by for each loop container as my source and joining multiple outputs of xml nodes in a target. my problem is that when all node exists in xml source the package works fine but if in any of the source one particular node does not exist then pipeline of that node does not get proceed and join operation is not taking place for that.

I want null value for the node which is not existing.

Please help me with proper solution if any of you have.

Thanks,

Diwakar

June 18, 2012 7:21 AM
 

Stelios said:

This piece of info (and code snippet) was exactly what I was looking for (otherwise I would have to scrap the project and revert to a .Net solution)

So, my thanks to you and the original poster (Kirk Haselden).

October 23, 2013 10:38 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement