批量查找和替换后，需要以编程方式重新编译DTSX包中的所有脚本任务 [英] Need to Programmatically Recompile all Script Tasks in DTSX Packages after Mass Find-And-Replace
问题描述
数据仓库的一项新举措要求我们更改DTSX软件包中VBA脚本任务中的许多连接字符串和UNC文件路径。
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.
我们已经执行了大量查找和替换，但是使用此方法更改脚本任务时，DTSX包执行期间运行的二进制文件不会在运行时重新编译，导致查找和替换的更改未反映在脚本任务的执行中。
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.
我已经找到了一些有关如何在SQL Server 2008和2012中执行此操作的文章，但我们使用的是SQL Server 2014，此处的代码示例不起作用对我来说：（ https://blogs.msdn.microsoft.com/jason_howell/2013/03/05/script-component-recompile-in-sql-server-2012-integration-services- ssis-to-refresh-metadata / ）。
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/).
评论中的某些问题谈到我的问题，但我以这种方式解决[导航到路径并包含引用]对我来说都没有用-我看不到这些程序集，以及2008年至2012年之间的更改，现在我们2014年，我不确定这些库是否也包含在我的发行版中……
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...
因此，我在各个子目录中都有一堆DTSX文件，它们需要它们重新编译脚本任务，以便我们可以进行这些更改。
我希望不必手动打开每个软件包中的每个脚本任务来强制构建每个任务。
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!
推荐答案
我创建一个 Console 应用程序，其代码为使用 Visual Studio 2013 dtsx 包中的每个 ScriptTask >和 C＃到两个级别。代码中的注释中包含了需要引用的每个程序集的路径。 pkgLocation 是包的路径（最终，我构建了一个 Windows Form 应用程序，但这是基础工作代码：
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();
}
}
}
我希望这对外面的很多人有帮助。如果您需要的话，我也有
Visual Basic版本。
这篇关于批量查找和替换后，需要以编程方式重新编译DTSX包中的所有脚本任务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
