The "copy from another VS solution" workaround doesn't always work, at least not for me.
I created a little C# routine that brute-force repairs the .dtsx XML by performing some basic text processing. The Script component assembly definitions are found in groups, with each one starting and ending with grep-able RegEx patterns. The general idea is to iterate over the lines of the file, and:
1. When you see the starting RegEx Pattern, parse out the Assembly ID and generate a new Assembly ID ("SC_" followed by a 32-char GUID)
2. Until you find the ending RegEx Pattern, replace the old Assembly ID with the new Assembly ID
NOTE: There are placeholders, but no support, for SQL Server versions other than 2012.
As with everything, use this code at your own risk. And feel free to let me know if you see any errors:
/// <summary>Repair a .dtsx file with conflicting AssemblyIDs in Script copmonents</summary>
/// <remarks>
/// A simple text-processing routine to repair the result of the Visual Studio bug that causes conflicting assembly names to
/// appear when copying a Script component into an SSIS Data Flow container.
///
/// Input: A corrupted .dtsx file containing an SSIS package with Script components having duplicate Assembly IDs
/// Output: A repaired .dtsx file with each Script component having a unique Assembly ID
/// </remarks>
/// <param name="inputDtsxFile">The full path to the .dtsx package to repair</param>
/// <param name="outputDtsxFile">The full path name of the repaired .dtsx package. Optional -
/// Null or default results in a file in the same folder as the source file, with "_repairedNNNN" appended to the file name, incrementing NNNN by 1 each time.</param>
/// <param name="startRegEx">Optional - Overrides the default RegEx for the version of SQL Server found in parameter targetVersion</param>
/// <param name="endRegEx">Optional - Overrides the default RegEx for the version of SQL Server found in parameter targetVersion</param>
/// <param name="targetVersion">Optional - The version of SQL Server the package build target is for. Default (and only version currently supported) is "SQL Server 2016"</param>
private void RepairDtsxScriptComponentCopyError(string inputDtsxFile, string outputDtsxFile = null, string targetVersion = null, string startRegEx = null, string endRegEx = null)
{
//Default the target version to "SQL Server 2016"
if (targetVersion == null)
targetVersion = "SQL Server 2016";
//Make sure if start or end RegEx patters are supplied, BOTH are supplied
if (startRegEx != null || endRegEx != null)
{
if (startRegEx == null)
{
Console.WriteLine("If either start or end regex is specified, both must be specified");
return;
}
}
//Set any variables specific to a target version of Visual Studio for SSIS
switch (targetVersion)
{
case "SQL Server 2012":
Console.WriteLine("SQL Server 2012 target version not supported yet.");
return;
case "SQL Server 2014":
Console.WriteLine("SQL Server 2014 target version not supported yet.");
return;
case "SQL Server 2016":
startRegEx = "\\[assembly: AssemblyTitle\\(\"SC_[a-zA-Z0-9]{32}\"\\)\\]";
endRegEx = "typeConverter=\"NOTBROWSABLE\"";
break;
case "SQL Server 2018":
Console.WriteLine("SQL Server 2018 target version not supported yet.");
return;
}
try
{
//Variables for output stream:
string folderName = "";
string fileName = "";
string fileExt = "";
//If no output file name is supplied, use the folder where the input file is located,
// look for files with the same name as the input file plus suffix "_repairedNNNN"
// and increment NNNN by one to make the new file name
// e.g. fixme.dtsx --> fixme_repared0000.dtsx (the first time it's cleaned)
// fixme.dtsx --> fixme_repared0001.dtsx (the second time it's cleaned)
// and so on.
if (outputDtsxFile == null || String.IsNullOrEmpty(outputDtsxFile) || String.IsNullOrWhiteSpace(outputDtsxFile))
{
folderName = Path.GetDirectoryName(inputDtsxFile);
fileName = Path.GetFileNameWithoutExtension(inputDtsxFile) + "_repaired";
fileExt = Path.GetExtension(inputDtsxFile);
int maxserial = 0;
//Output file will be in the form originalname_NNNN.dtsx
//Each run of the program will increment NNNN
//First, find the highest value of NNNN in all the file names in the target folder:
foreach (string foundFile in Directory.GetFiles(folderName, fileName + "_*" + fileExt))
{
string numStr = Regex.Replace(Path.GetFileNameWithoutExtension(foundFile), "^.*_", "");
int fileNum = -1;
if (int.TryParse(numStr, out fileNum))
maxserial = Math.Max(maxserial, fileNum);
}
//Increment by 1
maxserial++;
//Create new file name
fileName = Path.Combine(folderName, fileName + "_" + maxserial.ToString("0000") + fileExt);
}
else //Use the value passed in as a parameter
fileName = outputDtsxFile;
//Create the new StreamWriter handle for the output file
Stream outputStream = File.OpenWrite(fileName);
StreamWriter outputWriter = new StreamWriter(outputStream);
Console.WriteLine("----START----");
//Open the input file
StreamReader inputFile = new StreamReader(inputDtsxFile);
//Set up some variables
string line = "";
int linepos = 1;
int matchcount = 1;
int assyCount = 0;
string assyname = "";
string oldGuidLC = "";
string oldGuidUC = "";
string newGuidLC = "";
string newGuidUC = "";
Boolean inAssembly = false;
while ((line = inputFile.ReadLine()) != null)
{
//Look for the start of a section that contains the assembly name:
if (!inAssembly && Regex.IsMatch(line, startRegEx))
{
//Get the new GUID
assyname = Regex.Match(line, "SC_[a-zA-Z0-9]{32}").ToString();
oldGuidLC = assyname;
oldGuidUC = "SC_" + assyname.Substring(3, 32).ToUpper();
newGuidLC = "SC_" + Guid.NewGuid().ToString().Replace("-", "");
newGuidUC = newGuidLC.ToUpper();
//Set the "in Assembly" flag
inAssembly = true;
Console.WriteLine("Found Assembly " + assyname + " at line " + linepos.ToString());
Console.WriteLine("Old GUID (LC): " + oldGuidLC);
Console.WriteLine("Old GUID (UC): " + oldGuidUC);
Console.WriteLine("New GUID (LC): " + newGuidLC);
Console.WriteLine("New GUID (UC): " + newGuidUC);
assyCount++;
}
//Substitute the old GUID for the new GUID, but only bother doing it when in an assembly section
if (inAssembly && Regex.IsMatch(line, "SC_[a-zA-Z0-9]{32}"))
{
line = line.Replace(oldGuidLC, newGuidLC);
line = line.Replace(oldGuidUC, newGuidUC);
Console.WriteLine(linepos.ToString("000000") + "/" + assyCount.ToString("0000") + "/" + matchcount++.ToString("0000") + "/" + assyname + ": " + line);
}
//Look for the end of the assembly section
if (inAssembly && Regex.IsMatch(line, endRegEx) && Regex.IsMatch(line, "SC_[a-zA-Z0-9]{32}"))
{
inAssembly = false;
}
//Output the line
outputWriter.WriteLine(line);
linepos++;
}
inputFile.Close();
outputWriter.Close();
outputStream.Close();
Console.WriteLine("----DONE----");
}
catch (Exception ex)
{
Console.WriteLine("ERROR: " + ex.Message);
Console.WriteLine("----DONE----");
}
}
