May
24
2008
[UPDATED WITH A BETTER CODE SAMPLE FOR THE 2ND SCENARIO TUE 6/10/08]
The move in SQL Server 2008 Integration Services from VSA to VSTA for scripting doesn't just give us C# and a better IDE and Add Reference dialog. It has also made programmatic manipulation of the Script task easier and more supportable.
This week I got some sample code from Evgeny Koblov, one of our SSIS testers recently hired from Russia. His English is remarkably good...and makes me ashamed that I'm still confused by the Cyrillic alphabet after several quarters of evening Russian classes at the local community college. Anyway, I haven't had an opportunity to play with his code, so I'm just going to copy and paste what he sent me.
<Evgeny>
Actually you can add VSTA script task to package programmatically. There are several typical scenarios for that:
1. You have preliminary created script solution
private string VB_SCRIPT { get { return VSTAScriptLanguages.GetDisplayName("VisualBasic"); } }
private string CS_SCRIPT { get { return VSTAScriptLanguages.GetDisplayName("CSharp"); } }
Package pkg = new Package();
pkg.Executables.Add("STOCK:ScriptTask");
TaskHost th = (TaskHost)exec;
ScriptTask task = (ScriptTask)th.InnerObject;
task.ScriptProjectName = "YourProjectName.csproj”; // (or vbproj if it’s a visual basic project)
th.Name = name;
task.ScriptLanguage = CS_SCRIPT;
task.ReadWriteVariables = “User::Var1,User::Var2”; //Comma separated list of variables which should be available in Script
task.ReadOnlyVariables = rvars;
task.ScriptingEngine.LoadScriptSolution(path); // This method loads solution and builds it
task.ScriptingEngine.CloseIDE(true); // This one closes IDE and saves binary code inside script task
2. You want to add source files dynamically
<revised>
// Add a Script Task to the package.
scriptTaskHost = package.Executables.Add(@"STOCK:SCRIPTTASK") as TaskHost;
scriptTaskHost.Properties["Name"].SetValue(scriptTaskHost,
@"Script Task");
scriptTaskHost.Properties["Description"].SetValue(scriptTaskHost,
@"Script Task");
scriptTask = scriptTaskHost.InnerObject as ScriptTask;
// Initialize a new script project.
scriptTask.ScriptProjectName = "testscript";
// The next line is required.
scriptTask.ScriptLanguage = VSTAScriptLanguages.GetDisplayName("CSharp");
scriptTask.ScriptingEngine.InitNewScript("CSharp",
scriptTask.ScriptProjectName, ".csproj");
// The next line generates a default solution,
// but does not make the designer visible.
scriptTask.ScriptingEngine.ShowDesigner(false);
// Add custom project references
scriptTask.ScriptingEngine.AddProjectReference("Microsoft.SqlServer.ConnectionInfo,
Version=10.0.0.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL");
...
// Replace ScriptMain.cs file with our file
scriptTask.ScriptingEngine.AddCodeFile("ScriptMain.cs",
Properties.Resources.CodeFile);
if (!scriptTask.ScriptingEngine.Build())
throw new Exception("Failed to build project");
scriptTask.ScriptingEngine.SaveScriptToStorage();
scriptTask.ReadOnlyVariables = @"User::TableObject";
scriptTask.ReadWriteVariables = @"User::SchemaName,User::TableName";
</revised>
Structure of the solution should be exactly the same is it is created by script task when you press edit script. So you can save empty script task project to some location and use it as a template. I’d recommend to start experiments from the first approach so that you can get the idea of how the whole project looks like and after that you can try the second approach. But when you add or replace a file with script entry point – it should be appropriate (I mean it should have all the code generated during initialization of new script).
...I’d suggest to create a script task in BIDs and edit a script there – so you will see the generated solution. From opened designer you can save it to any location and use it as a template.
</Evgeny>
Have fun experimenting!
-Doug