Need to Programmatically Recompile all Script Task

Question

An upcoming move of our Data Warehouse has us needing to change many Connection Strings and UNC File Paths located in VBA Script Tasks within DTSX Packages.

We've performed a mass find-and-replace but when changing Script Tasks using this method, the binaries run during DTSX Package execution don't get recompiled at run time, resulting in the find-and-replaced changes not being reflected in the Script Task's execution.

I've found some articles on how to do it in SQL Server 2008 and 2012, but we're using SQL Server 2014 and the code examples here aren't working for me:(https://blogs.msdn.microsoft.com/jason_howell/2013/03/05/script-component-recompile-in-sql-server-2012-integration-services-ssis-to-refresh-metadata/).

Some of the questions in the comments speak to my problem but none of the "I fixed this this way [navigate to path and include references]" are working for me -- I don't see these assemblies, and with the changes between 2008 to 2012, and now us on 2014, I'm uncertain whether these libraries are even included in my distribution...

So, I have a whole bunch of DTSX files in various sub directories that require their script tasks be recompiled in order for us to go live with these changes. I'm hoping to not-have to open every script task in every package manually to force the build of each task.

Thanks in advance for any potential solutions!

Answer 1

I create a Console application with the code necesary to recompile every ScriptTask inside a dtsx package, using Visual Studio 2013 and C# up to two levels. Path for every assembly that needs to be referenced are included as comments in the code. pkgLocation is the path to the package(in the ends I build a Windows Form app, but this is the base and working code:

  using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Text;
  using System.Threading.Tasks;

  using Microsoft.SqlServer.Dts.Design;
  using Microsoft.SqlServer.Dts.Runtime;
  using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
  using Microsoft.SqlServer.Dts.Pipeline;
  using Microsoft.SqlServer.VSTAHosting;
  using Microsoft.SqlServer.IntegrationServices.VSTA;
  using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
  using System.IO;

  //Libraries
  //C:\windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.Dts.Design\v4.0_12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Dts.Design.dll
  //C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.DTSPipelineWrap.dll
  //C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SQLServer.DTSRuntimeWrap.dll
  //C:\windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.IntegrationServices.VSTA\v4.0_12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.IntegrationServices.VSTA.dll
  //C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll
  //C:\windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.PipelineHost\v4.0_12.0.0.0__89845dcd8080cc91\Microsoft.SQLServer.PipelineHost.dll
  //C:\windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.ScriptTask\v4.0_12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ScriptTask.dll
  //C:\windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.TxScript\v4.0_12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.TxScript.dll
  //C:\windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.VSTAScriptingLib\v4.0_12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.VSTAScriptingLib.dll

  namespace recompApp
  {
      class Program
      {
          static void Main(string[] args)
          {
              string pkgLocation;
              Package pkg;
              Application app; 

              //This is the folder where the test package lives!!
              pkgLocation =
                @"C:\TestPackage.dtsx";
              app = new Application();
              pkg = app.LoadPackage(pkgLocation, null);
              //It's Alive!!!!!!!
              try
              {

                  Executables pExecs = pkg.Executables;

                  foreach (Executable pExec in pExecs)
                  {
                      switch (pExec.GetType().Name)
                      {
                          case "TaskHost":{
                              TaskHost taskHost = (TaskHost)pExec;
                              Console.WriteLine("Executable name = " + taskHost.Name); 
                              //Script Task Outside of a Sequence
                              if (taskHost.InnerObject.ToString().Equals("Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask"))
                              {
                                  ScriptTask task = (ScriptTask)taskHost.InnerObject;
                                  //Load the script project, build and save
                                  task.ScriptingEngine.LoadProjectFromStorage();
                                  task.ScriptingEngine.VstaHelper.Build("");
                                  task.ScriptingEngine.SaveProjectToStorage();
                                  //Cleanup
                                  task.ScriptingEngine.DisposeVstaHelper();
                              }                            
                              break;
                          }
                          case "Sequence":{
                              Executables seqExecs = ((Microsoft.SqlServer.Dts.Runtime.Sequence)(pExec)).Executables;
                              foreach(Executable seqExec in seqExecs){
                                  switch (seqExec.GetType().Name)
                                  {
                                      case "TaskHost":
                                          {
                                              TaskHost taskHost = (TaskHost)seqExec;        
                                              //Script Task inside a Sequence Container
                                              if (taskHost.InnerObject.ToString().Equals("Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask"))
                                              {
                                                  Console.WriteLine("Executable name = " + taskHost.Name);
                                                  ScriptTask task = (ScriptTask)taskHost.InnerObject;
                                                  //Load the script project, build and save
                                                  task.ScriptingEngine.LoadProjectFromStorage();
                                                  task.ScriptingEngine.VstaHelper.Build("");
                                                  task.ScriptingEngine.SaveProjectToStorage();
                                                  //Cleanup
                                                  task.ScriptingEngine.DisposeVstaHelper();
                                              }
                                              break;
                                          }
                                  }
                              }
                              break;
                          }                        
                      }                    
                  }
                  //Save the updated xml in the package 
                  string xml;
                  pkg.SaveToXML(out xml, null);
                  File.WriteAllText(pkgLocation, xml);
              }
              catch (Exception e)
              {
                  Console.WriteLine(e.Message.ToString());
              }

              Console.WriteLine("Press any key to exit...");
              Console.ReadKey();
          }

      }
  } 

I hope this helps a lots of people outside. I also have the Visual Basic version if you need it.