You can use the SQL Server Integration Services API to reconfigure a package dynamically, or even to create an entire package from scratch. Books Online doesn't say much about this. But the SSIS product team has provided the Package Generation Sample, and various bloggers and authors have described the process and the code.
What if you want to add Script Tasks or Script Components programmatically? I blogged about this in 2008 - Adding a VSTA Script task programmatically.
And now the big question. What if you want to recompile the VSTA scripts?
The information in this blog post applies to SQL Server 2008 and 2008 R2. After publishing this post, I learned from SSIS developer Matt Masson that the sequence of API calls to recompile VSTA scripts has changed for SQL Server 2012 "Denali." Matt will publish a blog post with this new information. I will post a link here when that information becomes available.
1. How to recompile VSTA scripts programmatically
The answer is implicit in the code sample that I provided – just load the script into the VSTA IDE, then close the IDE. Here are the critical lines from the code sample in my old post, where task is a ScriptTask:
// This method loads solution and builds it
// This one closes IDE and saves binary code
This question was also answered in the SSIS forum by SSIS developer Silviu Guea. Here's an excerpt, where task is again a ScriptTask:
VSTATaskScriptingEngine script =
// load existing script from it's storage and load it in the designer
// re-save it triggering a rebuild
// close the designer... don't forget this or you'll leak processes
Recently a customer from Brazil asked me how to recompile. After some unkind thoughts about his familiarity with search, I Binged "ssis recompile script programmatically." In fact, I could not find a satisfactory answer, and I found the question unanswered in several places. (Hint: Search for "precompile." What a difference 1 letter makes.)
2. A command-line app for recompiling all scripts in a package
I pestered my buddy Silviu to ask whether there was more to say on this subject. In return he sent me the attached C# command-line application to rebuild all the Script Tasks and Script Components in a package. You may be able to compile the code and use it "as is," or you may want to study it to learn the steps in the process.
updatescripts.zip (4.26 kb)
2.1 Launching the command-line app
Here is a sample command line:
updatescripts.exe c:\work\mypackage.dtsx c:\temp\output.dtsx /tasks /components
2.2 Rebuilding Script Tasks
Here are the lines of code from the app that rebuild each Script Task, where task is again a ScriptTask:
VSTATaskScriptingEngine dte = new VSTATaskScriptingEngine(task.ScriptStorage);
2.3 Rebuilding Script Components
Here are the lines of code from the app that rebuild each Script Component, where comp is a ScriptComponentHost:
(There are some important lines of code that come before these simple steps, to determine whether the IDTSComponentMetaData100 is in fact a Script Component, to instantiate its CManagedComponentWrapper, and then to retrieve the ScriptComponentHost that is its InnerObject.)
I hope you find this application and sample code useful.
Let me know how I'm doing! Please comment on my blog, or send email to firstname.lastname@example.org.
For technical support, however, I recommend the SSIS forum on MSDN.
updatescripts.zip (4.26 kb)