ssis , Clean Code , devops
SSIS: Auto-Rebuilding SSIS Script Tasks/SSIS Components to Sync Stale References
Coming from a stronger C# background, I instinctively reach for helper classes to maintain DRY (Don't Repeat Yourself) principles. That instinct served me well until I carried it into SSIS. When I needed to reuse logic across packages, I did the natural thing: I built a shared DLL and registered it in the GAC on the server. It worked fine until I needed to add more optional arguments or change the data type of the argument to the existing method. Every time I need to update that assembly and re-import it, my packages break with a DTS Script Task: Runtime Error. The reason caught me off guard: the precompiled script binaries inside each package were still bound to the old version of the DLL, and a normal build never recompiles them. In today's post, I'll walk through how I tackled this by building a console application that rebuilds the Script Tasks and Script Components automatically.
DTS Script Task: Runtime Error
Behaviour
Before we jump into the implementation, let me show you what's actually happening inside the package because that's what defines our goal. A .dtsx is just an XML file underneath. So I captured it twice: once before I updated the shared DLL, and once after. Diffing the two, the change stood out immediately:
Stale Binary inside the dtsx
That highlighted section is the whole problem; it's the stale binary still bound to the old assembly. So the goal of the tool is exactly this: regenerate that part automatically, for every Script Task and Script Component, without opening a single package by hand.
Solution
To help me build the packages automatically, I created the below .csproj:
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net48</TargetFramework>
<RootNamespace>DtsxRebuilder</RootNamespace>
<AssemblyName>DtsxRebuilder</AssemblyName>
<LangVersion>9.0</LangVersion>
<PlatformTarget>x64</PlatformTarget>
<Prefer32Bit>false</Prefer32Bit>
<Deterministic>true</Deterministic>
<!-- Keep the hand-maintained Properties\AssemblyInfo.cs as the single source
of assembly metadata; do not let the SDK generate a second copy. -->
<GenerateAssemblyInfo>false</GenerateAssemblyInfo>
</PropertyGroup>
<!-- The tool only needs LINQ-to-XML on top of the implicit .NET Framework
references (System, System.Core, System.Xml, mscorlib). No NuGet packages
or SSIS runtime assemblies are required: scripts are recompiled out-of-process
by invoking MSBuild, not by referencing the SSIS object model here. -->
<ItemGroup>
<Reference Include="System.Xml.Linq" />
</ItemGroup>
<!-- Top-level NuGet packages. Their transitive dependencies (System.Memory,
System.Buffers, System.Numerics.Vectors, System.Runtime.CompilerServices.Unsafe,
System.Threading.Tasks.Extensions, System.ValueTuple) are restored
automatically and no longer need to be listed explicitly. -->
<ItemGroup>
<PackageReference Include="Microsoft.Extensions.DependencyInjection.Abstractions" Version="10.0.9" />
<PackageReference Include="Microsoft.Extensions.Primitives" Version="10.0.9" />
<PackageReference Include="Microsoft.Bcl.AsyncInterfaces" Version="10.0.9" />
</ItemGroup>
</Project>
Then, here is the main program with all the methods necessary:
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Xml;
using System.Xml.Linq;
namespace DtsxRebuilder
{
// -------------------------------------------------------------------------
// DtsxRebuilder
//
// Purpose: every SSIS Script Task / Script Component stores BOTH its C#
// source AND a pre-compiled assembly (a base64-encoded DLL) inside the .dtsx.
// When an in-house library the script references (e.g. BlogLibrary.dll) is
// updated, that embedded assembly is STALE: it was linked against the old
// library. This tool walks a folder of .dtsx files, finds every embedded
// script project, recompiles its source with MSBuild against the *current*
// referenced assemblies, and writes the fresh binary back into the package.
//
// The recompile is done exactly the way SSDT/VSTA does it internally: the
// embedded project files are written to a temp folder and built with
// MSBuild. If the build fails, the failure (compiler output) is thrown and
// the package is left untouched. If it succeeds, the new DLL is re-embedded.
//
// Usage: DtsxRebuilder <folder-path> [options]
// -------------------------------------------------------------------------
internal static class Program
{
private static int Main(string[] args)
{
Options opt;
try { opt = Options.Parse(args); }
catch (Exception ex) { Console.Error.WriteLine(ex.Message); Options.PrintUsage(); return 2; }
if (!Directory.Exists(opt.Path))
{
Console.Error.WriteLine($"Folder not found: {opt.Path}");
return 2;
}
BuildToolset toolset;
try { toolset = BuildToolset.Locate(opt); }
catch (Exception ex) { Console.Error.WriteLine(ex.Message); return 3; }
Console.WriteLine($"MSBuild: {toolset.MsBuildExe}");
var files = Directory.EnumerateFiles(opt.Path, "*.dtsx", SearchOption.AllDirectories)
.OrderBy(f => f, StringComparer.OrdinalIgnoreCase)
.ToList();
Console.WriteLine($"Found {files.Count} package(s) under {opt.Path}\n");
int rebuilt = 0, skipped = 0, failed = 0;
foreach (var file in files)
{
try
{
var result = PackageProcessor.Process(file, opt, toolset);
switch (result)
{
case ProcessResult.Rebuilt: rebuilt++; break;
case ProcessResult.NoScripts: skipped++; break;
case ProcessResult.Unchanged: skipped++; break;
}
}
catch (Exception ex)
{
failed++;
Console.Error.WriteLine($"[FAIL] {Rel(opt.Path, file)}");
Console.Error.WriteLine(Indent(ex.Message));
}
}
Console.WriteLine($"\nDone. rebuilt={rebuilt} skipped={skipped} failed={failed}");
return failed > 0 ? 1 : 0;
}
internal static string Rel(string root, string f)
{
try { return f.StartsWith(root, StringComparison.OrdinalIgnoreCase) ? f.Substring(root.Length).TrimStart('\\', '/') : f; }
catch { return f; }
}
internal static string Indent(string s)
=> string.Join(Environment.NewLine, (s ?? "").Replace("\r\n", "\n").Split('\n').Select(l => " " + l));
}
internal enum ProcessResult { Rebuilt, Unchanged, NoScripts }
internal sealed class Options
{
public string Path;
public string Configuration = "Release"; // SSDT builds embedded scripts in Release
public string SsisRefsOverride; // explicit PublicAssemblies\SSIS\<major> folder
public string MsBuildOverride; // explicit MSBuild.exe
public bool DryRun;
public bool NoStamp; // skip VersionBuild/VersionGUID/ProjectId refresh
public bool KeepTemp; // keep temp build folders for inspection
public static Options Parse(string[] a)
{
var o = new Options();
for (int i = 0; i < a.Length; i++)
{
switch (a[i])
{
case "--path": o.Path = a[++i]; break;
case "--configuration": o.Configuration = a[++i]; break;
case "--ssis-refs": o.SsisRefsOverride = a[++i]; break;
case "--msbuild": o.MsBuildOverride = a[++i]; break;
case "--dry-run": o.DryRun = true; break;
case "--no-stamp": o.NoStamp = true; break;
case "--keep-temp": o.KeepTemp = true; break;
case "-h":
case "--help": throw new ArgumentException("");
default:
if (a[i].StartsWith("-")) throw new ArgumentException($"Unknown arg: {a[i]}");
if (o.Path == null) { o.Path = a[i]; break; }
throw new ArgumentException($"Unexpected arg: {a[i]}");
}
}
if (string.IsNullOrWhiteSpace(o.Path)) throw new ArgumentException("A folder path is required.");
o.Path = System.IO.Path.GetFullPath(o.Path);
return o;
}
public static void PrintUsage() => Console.Error.WriteLine(
"Usage: DtsxRebuilder <folder-path> [options]\n" +
" Recompiles every Script Task / Script Component in every .dtsx under <folder-path>\n" +
" against its currently-resolvable references, and re-embeds the fresh binary.\n\n" +
"Options:\n" +
" --configuration <Debug|Release> Build configuration (default: Release, as SSDT uses)\n" +
" --ssis-refs <dir> Override the SSIS reference-assembly folder\n" +
" (…\\Common7\\IDE\\PublicAssemblies\\SSIS\\<major>)\n" +
" --msbuild <path> Override MSBuild.exe location\n" +
" --dry-run Recompile and validate, but do not save packages\n" +
" --no-stamp Do not refresh VersionBuild/VersionGUID/ProjectId\n" +
" --keep-temp Leave temp build folders for inspection");
}
// -------------------------------------------------------------------------
// Locates MSBuild and the SSIS design-time reference assemblies via vswhere.
// -------------------------------------------------------------------------
internal sealed class BuildToolset
{
public string MsBuildExe { get; private set; }
private readonly List<string> _vsInstalls;
private readonly string _ssisRefsOverride;
private readonly Dictionary<int, string> _ssisRefsByMajor = new Dictionary<int, string>();
private BuildToolset(string msbuild, List<string> vsInstalls, string ssisRefsOverride)
{
MsBuildExe = msbuild;
_vsInstalls = vsInstalls;
_ssisRefsOverride = ssisRefsOverride;
}
public static BuildToolset Locate(Options opt)
{
var installs = QueryVsInstalls();
string msbuild = opt.MsBuildOverride;
if (msbuild != null && !File.Exists(msbuild))
throw new FileNotFoundException($"--msbuild not found: {msbuild}");
if (msbuild == null)
{
foreach (var root in installs)
{
foreach (var rel in new[]
{
@"MSBuild\Current\Bin\amd64\MSBuild.exe",
@"MSBuild\Current\Bin\MSBuild.exe"
})
{
var cand = Path.Combine(root, rel);
// Require a complete C# toolset next to it: some installs (e.g. SSMS)
// ship MSBuild without the Roslyn targets and cannot build C# projects.
if (File.Exists(cand) && HasCSharpToolset(cand)) { msbuild = cand; break; }
}
if (msbuild != null) break;
}
}
if (msbuild == null)
throw new InvalidOperationException(
"MSBuild.exe not found. Install Visual Studio (with SSIS/Data Tools) or pass --msbuild <path>.");
return new BuildToolset(msbuild, installs, opt.SsisRefsOverride);
}
private static bool HasCSharpToolset(string msbuildExe)
{
var binDir = Path.GetDirectoryName(msbuildExe);
// amd64 bin nests one level under the main Bin folder where Roslyn lives.
foreach (var probe in new[]
{
Path.Combine(binDir, "Roslyn", "Microsoft.CSharp.Core.targets"),
Path.Combine(Path.GetDirectoryName(binDir) ?? binDir, "Roslyn", "Microsoft.CSharp.Core.targets")
})
{
if (File.Exists(probe)) return true;
}
return false;
}
// Returns the PublicAssemblies\SSIS\<major> folder holding the version-matched
// Microsoft.SqlServer.* reference assemblies, or null if the build can resolve
// them another way (HintPath injection is skipped when null).
public string SsisRefsForMajor(int major)
{
if (_ssisRefsByMajor.TryGetValue(major, out var cached)) return cached;
string found = null;
var candidates = new List<string>();
if (!string.IsNullOrEmpty(_ssisRefsOverride)) candidates.Add(_ssisRefsOverride);
foreach (var root in _vsInstalls)
candidates.Add(Path.Combine(root, "Common7", "IDE", "PublicAssemblies", "SSIS", major.ToString()));
foreach (var dir in candidates)
{
if (Directory.Exists(dir) &&
File.Exists(Path.Combine(dir, "Microsoft.SqlServer.ManagedDTS.dll")))
{
found = dir;
break;
}
}
_ssisRefsByMajor[major] = found;
return found;
}
private static List<string> QueryVsInstalls()
{
var list = new List<string>();
var vswhere = Path.Combine(
Environment.GetFolderPath(Environment.SpecialFolder.ProgramFilesX86),
@"Microsoft Visual Studio\Installer\vswhere.exe");
if (File.Exists(vswhere))
{
try
{
var psi = new ProcessStartInfo(vswhere,
"-all -prerelease -products * -property installationPath")
{
RedirectStandardOutput = true,
UseShellExecute = false,
CreateNoWindow = true
};
using (var p = Process.Start(psi))
{
string outp = p.StandardOutput.ReadToEnd();
p.WaitForExit();
foreach (var line in outp.Split(new[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries))
if (Directory.Exists(line.Trim())) list.Add(line.Trim());
}
}
catch { /* fall through to manual scan */ }
}
if (list.Count == 0)
{
foreach (var pf in new[]
{
Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles),
Environment.GetFolderPath(Environment.SpecialFolder.ProgramFilesX86)
}.Distinct())
{
var vsRoot = Path.Combine(pf, "Microsoft Visual Studio");
if (!Directory.Exists(vsRoot)) continue;
foreach (var d in Directory.EnumerateDirectories(vsRoot, "*", SearchOption.AllDirectories)
.Where(d => File.Exists(Path.Combine(d, @"MSBuild\Current\Bin\MSBuild.exe"))))
list.Add(d);
}
}
return list;
}
}
// -------------------------------------------------------------------------
// Loads a .dtsx, rebuilds each embedded script, and saves it.
// -------------------------------------------------------------------------
internal static class PackageProcessor
{
public static ProcessResult Process(string file, Options opt, BuildToolset toolset)
{
var rel = Program.Rel(opt.Path, file);
// Read raw text but remember the original BOM so we can write it back
// unchanged. All edits below are surgical string replacements so the
// package keeps SSIS's exact formatting (one attribute per line, LF
// newlines) -- only the rebuilt binaries and version stamps change.
byte[] bytes = File.ReadAllBytes(file);
bool bom = bytes.Length >= 3 && bytes[0] == 0xEF && bytes[1] == 0xBB && bytes[2] == 0xBF;
string raw = new UTF8Encoding(false).GetString(bom ? bytes.Skip(3).ToArray() : bytes);
var doc = new XmlDocument { PreserveWhitespace = true };
doc.LoadXml(raw);
var units = ScriptUnit.FindAll(doc).ToList();
if (units.Count == 0)
{
Console.WriteLine($"[skip] {rel} - no script tasks/components");
return ProcessResult.NoScripts;
}
Console.WriteLine($"{rel} ({units.Count} script project(s))");
foreach (var unit in units)
{
var refDir = toolset.SsisRefsForMajor(unit.SsisMajor);
Console.WriteLine($" rebuilding {unit.Kind} '{unit.Name}' " +
$"[{unit.AssemblyName}, SSIS {unit.SsisMajor}]");
byte[] dll = ScriptCompiler.Compile(unit, opt, toolset, refDir);
string oldBinary = unit.OldBinaryText;
if (string.IsNullOrEmpty(oldBinary) || !raw.Contains(oldBinary))
throw new InvalidOperationException(
$"Could not locate the embedded binary span for '{unit.Name}' to replace it.");
string newline = oldBinary.Contains("\r\n") ? "\r\n" : "\n";
raw = raw.Replace(oldBinary, WrapBase64(dll, newline));
Console.WriteLine($" ok -> {dll.Length:n0} bytes");
}
if (!opt.NoStamp) raw = Stamp(raw);
if (opt.DryRun)
{
Console.WriteLine($" [dry-run] would save {rel}");
return ProcessResult.Rebuilt;
}
File.WriteAllText(file, raw, new UTF8Encoding(bom));
Console.WriteLine($" [saved] {rel}");
return ProcessResult.Rebuilt;
}
// Base64, wrapped at 76 columns with no trailing newline -- the shape SSIS
// uses for embedded assemblies. The newline style matches the surrounding
// package so line endings stay consistent.
private static string WrapBase64(byte[] data, string newline)
{
string b = Convert.ToBase64String(data);
var sb = new StringBuilder(b.Length + (b.Length / 76 + 8) * newline.Length);
for (int i = 0; i < b.Length; i += 76)
{
if (i > 0) sb.Append(newline);
sb.Append(b, i, Math.Min(76, b.Length - i));
}
return sb.ToString();
}
// Mirror what a save in SSDT does: bump the package build counter, mint a
// fresh package version GUID, and regenerate each VSTA project id.
private static string Stamp(string raw)
{
raw = Regex.Replace(raw, "(DTS:VersionBuild=\")(\\d+)(\")",
m => m.Groups[1].Value + (int.Parse(m.Groups[2].Value) + 1) + m.Groups[3].Value);
raw = Regex.Replace(raw, "(DTS:VersionGUID=\")\\{[^}]*\\}(\")",
m => m.Groups[1].Value + "{" + NewGuid() + "}" + m.Groups[2].Value);
raw = Regex.Replace(raw, "(<msb:ProjectId>)\\{[^}]*\\}(</msb:ProjectId>)",
m => m.Groups[1].Value + "{" + NewGuid() + "}" + m.Groups[2].Value);
return raw;
}
private static string NewGuid() => Guid.NewGuid().ToString().ToUpperInvariant();
}
// -------------------------------------------------------------------------
// One embedded script project (a Script Task or a Script Component) and the
// hooks needed to read its source and write its rebuilt binary back.
// -------------------------------------------------------------------------
internal sealed class ScriptUnit
{
public string Kind; // "ScriptTask" | "ScriptComponent"
public string Name;
public string AssemblyName;
public string CsprojName;
public int SsisMajor; // e.g. 150
public List<SourceFile> Files = new List<SourceFile>();
private XmlNode _binaryNode; // node whose content holds the base64 DLL
// The exact embedded-binary text as it appears in the package, used as the
// search target for surgical replacement.
public string OldBinaryText => _binaryNode?.InnerText;
public static IEnumerable<ScriptUnit> FindAll(XmlDocument doc)
{
foreach (var u in FindScriptTasks(doc)) yield return u;
foreach (var u in FindScriptComponents(doc)) yield return u;
}
// Script Task: <ScriptProject Name=".." VSTAMajorVersion="15"> with
// <ProjectItem Name="..">source</ProjectItem> children and one <BinaryItem>.
private static IEnumerable<ScriptUnit> FindScriptTasks(XmlDocument doc)
{
foreach (XmlElement sp in XmlUtil.ByLocal(doc, "ScriptProject"))
{
var unit = new ScriptUnit { Kind = "ScriptTask", Name = XmlUtil.Attr(sp, "Name") };
foreach (XmlElement child in XmlUtil.ChildElements(sp))
{
if (child.LocalName == "ProjectItem")
{
var relPath = XmlUtil.Attr(child, "Name");
var content = XmlUtil.GetContent(child);
unit.Files.Add(new SourceFile { RelPath = relPath, Content = content });
}
else if (child.LocalName == "BinaryItem")
{
unit._binaryNode = child;
}
}
if (!Finalize(unit, fallbackMajorAttr: XmlUtil.Attr(sp, "VSTAMajorVersion")))
continue;
yield return unit;
}
}
// Script Component: a <component> in a data-flow <pipeline> with a
// "SourceCode" array property (filename/encoding/content triples) and a
// "BinaryCode" array property ([dll-name, base64-dll]).
private static IEnumerable<ScriptUnit> FindScriptComponents(XmlDocument doc)
{
foreach (XmlElement comp in XmlUtil.ByLocal(doc, "component"))
{
var propsParent = XmlUtil.ChildByLocal(comp, "properties");
if (propsParent == null) continue;
XmlElement sourceProp = null, binaryProp = null;
foreach (XmlElement p in XmlUtil.ChildrenByLocal(propsParent, "property"))
{
var pn = XmlUtil.Attr(p, "name");
if (pn == "SourceCode") sourceProp = p;
else if (pn == "BinaryCode") binaryProp = p;
}
if (sourceProp == null || binaryProp == null) continue;
var unit = new ScriptUnit { Kind = "ScriptComponent", Name = XmlUtil.Attr(comp, "name") };
var srcElems = XmlUtil.ArrayElements(sourceProp);
for (int i = 0; i + 2 < srcElems.Count; i += 3)
{
var relPath = XmlUtil.GetContent(srcElems[i]);
var content = XmlUtil.GetContent(srcElems[i + 2]);
unit.Files.Add(new SourceFile { RelPath = relPath, Content = content });
}
var binElems = XmlUtil.ArrayElements(binaryProp);
if (binElems.Count >= 2) unit._binaryNode = binElems[1];
if (!Finalize(unit, fallbackMajorAttr: null)) continue;
yield return unit;
}
}
private static bool Finalize(ScriptUnit unit, string fallbackMajorAttr)
{
if (unit._binaryNode == null) return false;
var csproj = unit.Files.FirstOrDefault(f => f.RelPath != null &&
f.RelPath.EndsWith(".csproj", StringComparison.OrdinalIgnoreCase));
if (csproj == null) return false;
unit.CsprojName = csproj.RelPath;
unit.AssemblyName = ExtractTag(csproj.Content, "AssemblyName")
?? Path.GetFileNameWithoutExtension(csproj.RelPath);
unit.SsisMajor = DetectMajor(csproj.Content, fallbackMajorAttr);
return true;
}
private static int DetectMajor(string csproj, string fallbackAttr)
{
var m = Regex.Match(csproj, @"Microsoft\.SqlServer\.[A-Za-z]+,\s*Version=(\d+)\.");
if (m.Success && int.TryParse(m.Groups[1].Value, out var v)) return v * 10;
if (int.TryParse(fallbackAttr, out var fa)) return fa * 10;
return 150;
}
private static string ExtractTag(string xml, string tag)
{
var m = Regex.Match(xml, $@"<{tag}>\s*(.*?)\s*</{tag}>", RegexOptions.Singleline);
return m.Success ? m.Groups[1].Value.Trim() : null;
}
}
internal sealed class SourceFile
{
public string RelPath;
public string Content;
}
// -------------------------------------------------------------------------
// Reconstructs the embedded project on disk and builds it with MSBuild.
// Throws on build failure (with the MSBuild output as the message).
// -------------------------------------------------------------------------
internal static class ScriptCompiler
{
public static byte[] Compile(ScriptUnit unit, Options opt, BuildToolset toolset, string ssisRefDir)
{
string tempRoot = Path.Combine(Path.GetTempPath(),
"DtsxRebuild_" + Guid.NewGuid().ToString("N"));
Directory.CreateDirectory(tempRoot);
try
{
foreach (var f in unit.Files)
{
var rel = f.RelPath.Replace('/', '\\');
var full = Path.Combine(tempRoot, rel);
Directory.CreateDirectory(Path.GetDirectoryName(full));
var content = f.RelPath.Equals(unit.CsprojName, StringComparison.OrdinalIgnoreCase)
? InjectReferenceHintPaths(f.Content, ssisRefDir)
: f.Content;
File.WriteAllText(full, content, new UTF8Encoding(false));
}
string csprojPath = Path.Combine(tempRoot, unit.CsprojName.Replace('/', '\\'));
string outDir = Path.Combine(tempRoot, "__out");
Directory.CreateDirectory(outDir);
string outForMsbuild = outDir.Replace('\\', '/').TrimEnd('/') + "/";
string args =
$"\"{csprojPath}\" /nologo /v:minimal /m " +
$"/p:Configuration={opt.Configuration} /p:Platform=AnyCPU " +
$"/p:OutputPath=\"{outForMsbuild}\" " +
"/p:RunPostBuildEvent=Never";
var (exit, output) = RunMsBuild(toolset.MsBuildExe, args, tempRoot);
if (exit != 0)
throw new InvalidOperationException(
$"Rebuild FAILED for {unit.Kind} '{unit.Name}' (assembly {unit.AssemblyName}).\n" +
$"MSBuild exit code {exit}. Compiler output:\n{output}");
string dllPath = Path.Combine(outDir, unit.AssemblyName + ".dll");
if (!File.Exists(dllPath))
throw new InvalidOperationException(
$"Rebuild reported success but {unit.AssemblyName}.dll was not produced.\n" +
$"MSBuild output:\n{output}");
return File.ReadAllBytes(dllPath);
}
finally
{
if (!opt.KeepTemp)
try { Directory.Delete(tempRoot, true); } catch { /* best effort */ }
else
Console.WriteLine($" [keep-temp] {tempRoot}");
}
}
// SSIS references (Microsoft.SqlServer.*) are version-pinned (e.g. 15.0.0.0)
// and live in PublicAssemblies\SSIS\<major>, which is not on MSBuild's default
// search path. Pin each via an explicit HintPath so RAR resolves the exact
// version. The in-house library keeps its own absolute HintPath, so the build
// links against whatever is on disk there now -- that is the reference sync.
private static string InjectReferenceHintPaths(string csprojXml, string ssisRefDir)
{
if (string.IsNullOrEmpty(ssisRefDir)) return csprojXml;
XNamespace ns = "http://schemas.microsoft.com/developer/msbuild/2003";
XDocument xd;
try { xd = XDocument.Parse(csprojXml, LoadOptions.PreserveWhitespace); }
catch { return csprojXml; }
foreach (var r in xd.Descendants(ns + "Reference").ToList())
{
var include = (string)r.Attribute("Include");
if (string.IsNullOrEmpty(include)) continue;
var simple = include.Split(',')[0].Trim();
if (!simple.StartsWith("Microsoft.SqlServer.", StringComparison.OrdinalIgnoreCase)) continue;
var dll = Path.Combine(ssisRefDir, simple + ".dll");
if (!File.Exists(dll)) continue;
r.Elements(ns + "HintPath").Remove();
r.Elements(ns + "SpecificVersion").Remove();
r.Add(new XElement(ns + "SpecificVersion", "False"));
r.Add(new XElement(ns + "HintPath", dll));
}
return xd.Declaration != null
? xd.Declaration + Environment.NewLine + xd.ToString(SaveOptions.DisableFormatting)
: xd.ToString(SaveOptions.DisableFormatting);
}
private static (int exit, string output) RunMsBuild(string msbuild, string args, string workingDir)
{
var psi = new ProcessStartInfo(msbuild, args)
{
RedirectStandardOutput = true,
RedirectStandardError = true,
UseShellExecute = false,
CreateNoWindow = true,
WorkingDirectory = workingDir
};
var sb = new StringBuilder();
using (var p = new Process { StartInfo = psi })
{
p.OutputDataReceived += (s, e) => { if (e.Data != null) lock (sb) sb.AppendLine(e.Data); };
p.ErrorDataReceived += (s, e) => { if (e.Data != null) lock (sb) sb.AppendLine(e.Data); };
p.Start();
p.BeginOutputReadLine();
p.BeginErrorReadLine();
p.WaitForExit();
return (p.ExitCode, sb.ToString().Trim());
}
}
}
// -------------------------------------------------------------------------
// Small XML helpers that work regardless of namespace prefixes and preserve
// CDATA vs text content when writing back.
// -------------------------------------------------------------------------
internal static class XmlUtil
{
public static IEnumerable<XmlElement> ByLocal(XmlNode root, string local)
=> root.SelectNodes($".//*[local-name()='{local}']")?.Cast<XmlNode>().OfType<XmlElement>()
?? Enumerable.Empty<XmlElement>();
public static IEnumerable<XmlElement> ChildElements(XmlNode n)
=> n.ChildNodes.Cast<XmlNode>().OfType<XmlElement>();
public static XmlElement ChildByLocal(XmlNode n, string local)
=> ChildElements(n).FirstOrDefault(c => c.LocalName == local);
public static IEnumerable<XmlElement> ChildrenByLocal(XmlNode n, string local)
=> ChildElements(n).Where(c => c.LocalName == local);
public static string Attr(XmlNode n, string local)
=> n.Attributes?.Cast<XmlAttribute>().FirstOrDefault(a => a.LocalName == local)?.Value;
// property -> arrayElements -> arrayElement[]
public static List<XmlElement> ArrayElements(XmlElement property)
{
var ae = ChildByLocal(property, "arrayElements");
return ae == null ? new List<XmlElement>() : ChildrenByLocal(ae, "arrayElement").ToList();
}
public static string GetContent(XmlNode node) => node.InnerText;
}
}
Here's the step-by-step summary of what the code does:
- Parse args (Options.Parse): read the folder path and options; validate that the folder exists.
- Locate the build toolset (BuildToolset.Locate): find a Visual Studio MSBuild that has the Roslyn C# toolset (via vswhere), and the …\PublicAssemblies\SSIS<major> reference folder.
- Enumerate packages: recursively find every *.dtsx under the folder.
- For each package (PackageProcessor.Process):
- Read the file as raw text (remembering BOM/line endings) and load a parse-only XML copy.
- Find scripts (ScriptUnit.FindAll): locate each Script Task (<ScriptProject>) and Script Component (<component> with SourceCode/BinaryCode); skip the file if none.
- For each script (ScriptCompiler.Compile):
- Extract the embedded project files to a temp folder.
Inject HintPaths so the SSIS references resolve (the in-house DLL keeps its own HintPath → relinks against the current library = the sync). - Run MSBuild in Release.
- On failure → throw the compiler output (package left untouched); on success → read the fresh DLL bytes.
- Re-embed via surgical text replacement: swap the old base64 blob for the new one (76-col wrap, matching newline).
- Extract the embedded project files to a temp folder.
- Stamp the package (Stamp): bump VersionBuild, new VersionGUID, regenerate ProjectIds (unless --no-stamp).
- Write the raw text back (unless --dry-run).
- Report & exit: print rebuilt/skipped/failed; return non-zero if any package failed.
Demo
Here is the Demo of the code:
Basically, what I'm showing in the above:
- Show the BlogLibrary project, which has SayHello.SampleMethod method that is being used in the CodeScript.dtsx package.
- Installed the BlogLibrary using gacutil -i so the system understands the library.
- Run the package, and it shows success.
- Update the SayHello.SampleMethod argument and installed the changes using gacutil -i
- Run the package again, and we're getting an error DTS Script Task: Runtime Error
- Run the DtsxRebuilder package, and it detects the CodeScript.dtsx package and rebuilds it.
- Rebuild the LearnSSIS project (mandatory) > run the CodeScript.dtsx > this time it succeeded because the code stale already resolved.
Hope this helps! Happy CRM-ing 🚀!
Leave a comment
Your comment is sent privately to the author and isn't published on the site.