May
24
2008

Adding a VSTA Script task programmatically (revised)

[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
 
 
 

Comments (6) -

Liam Walsh

Hi,


Can you tell us exactly which assemblies to reference for the above code?


So far I am using:


C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.ScriptTask.dll


C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.VSTAScriptingLib.dll


Is this correct?


dougbert

Liam,


Good question! Here is the response that I received promptly from the developer. Only the 1st assembly is required in call cases:


That should be all. You don’t even need the scripting library if you don’t try to manipulate the script inside the task I believe.


Let me know if you need more help with this. Here is a sample from our tests that are building script tasks programmatically:


       public ScriptTask AddScriptTask(string name, string path, string lang, string rvars, string rwvars, string newlang, Package pkg, bool expException)


       {


           Executable exec = GetExecutable(pkg, name);


           if (exec == null)


               exec = pkg.Executables.Add("STOCK:ScriptTask");


           TaskHost th = (TaskHost)exec;


           ScriptTask task = (ScriptTask)th.InnerObject;


           try


           {


               if (name != null)


               {


                   task.ScriptProjectName = name;


                   th.Name = name;


               }


               task.ScriptLanguage = lang;


               if (rwvars != null)


               {


                   task.ReadWriteVariables = rwvars;


                   PackageUtils.VerifyValue(task.ReadWriteVariables, rwvars);


               }


               if (rvars != null)


               {


                   task.ReadOnlyVariables = rvars;


               }


                // the following 2 API don’t work on IA64


                task.ScriptingEngine.LoadScriptSolution(path);


                task.ScriptingEngine.CloseIDE(true);


               if (newlang != null)


                   task.ScriptLanguage = newlang;


           }


           catch (Exception ex)


           {


               if (!expException)


                   throw ex;


           }


           return task;


       }


Siddiq

Thanks for the nice code.


The code works perfectly in windows and console applications.


When invoked from windows service the below step generates warning in event viewer and suspended the service.


scriptTask.ScriptingEngine.InitNewScript("CSharp",


scriptTask.ScriptProjectName, ".csproj");


Event Viewer Message:


Event Type:  Warning


Event Source:  Visual Studio - VsTemplate


Event Category:  None


Event ID:  1


Date:    4/17/2009


Time:    1:34:45 AM


User:    N/A


Computer:  AITTSQL40


Description:


The global template information is out of date. Regenerate the templates by running 'VSTA.exe /installvstemplates' or reinstalling the application.  Note: corrective action requires Administrator privileges.


For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp">go.microsoft.com/.../events.asp.


Please help.


dougbert

Siddiq,


I regret that I don't know the answer offhand, and a single blogger is not the best source for technical support. I encourage you to post your question in the SSIS forum on MSDN at social.msdn.microsoft.com/.../">social.msdn.microsoft.com/.../threads, where you'll reach a huge audience of SSIS experts including the product team at Microsoft.


However I have also sent your question to someone who may have a suggestion, and I will post any response that I receive.


Thank you,


-Doug


dougbert

Here is a response that I received to Siddiq's question from a developer who's knowledgeable about VSTA in SSIS.


As usual, the issue seems to be "permissions" when the problem description is "works in one context, not in another".


----


I believe the reason is that the windows service runs as a local system account without desktop interaction. The process of creating a new script relies heavily on the VSTA designer that most likely needs access to the logon profile including registry, application data folder etc. which the local system account does not have.


One workaround might be to run that service as a specific user instead of local system, a user account that they know it is properly configured to be able to programmatically create scripts.


---------------


-Doug


Muhmud

Thanks for this!


Very much appreciated Smile


Pingbacks and trackbacks (3)+