/// <summary>
/// Produce Documentation of an SSIS package
/// </summary>
/// <param name="o"></param>
private static void ProduceSsisDocumentation(SsisObject o, string filename, string htmlOutputFilename)
{
// First find all the connection strings
var connstrings = from SsisObject c in o.Children where c.DtsObjectType == "DTS:ConnectionManager" select c;
//ConnectionWriter.WriteConnections(connstrings, filename);
// Find all the Variables
var variables = from SsisObject c in o.Children where c.DtsObjectType == "DTS:Variable" select c;
// Next, get all the executable functions
var functions = from SsisObject c in o.Children where c.DtsObjectType == "DTS:Executable" select c;
if (!functions.Any())
{
var executables = from SsisObject c in o.Children where c.DtsObjectType == "DTS:Executables" select c;
List <SsisObject> flist = new List <SsisObject>();
foreach (var exec in executables)
{
flist.AddRange(from e in exec.Children where e.DtsObjectType == "DTS:Executable" select e);
}
if (flist.Count == 0)
{
Console.WriteLine("No functions ('DTS:Executable') objects found in the specified file.");
return;
}
functions = flist;
}
WriteDocumentation(o, variables, connstrings, functions, filename);
ConvertToHtml(filename, htmlOutputFilename);
}
public static void ParseSsisPackage(string ssis_filename, string output_folder, SqlCompatibilityType SqlMode = SqlCompatibilityType.SQL2008, bool UseSqlSMO = true)
{
XmlReaderSettings set = new XmlReaderSettings();
set.IgnoreWhitespace = true;
SsisObject o = new SsisObject();
gSqlMode = SqlMode;
// Make sure output folder exists
Directory.CreateDirectory(output_folder);
// Set the appropriate flag for SMO usage
ProjectWriter.UseSqlServerManagementObjects = UseSqlSMO;
// TODO: Should read the dtproj file instead of the dtsx file, then produce multiple classes, one for each .DTSX file
// Read in the file, one element at a time
XmlDocument xd = new XmlDocument();
xd.Load(ssis_filename);
ReadObject(xd.DocumentElement, o);
// Now let's produce something meaningful out of this mess!
ProduceSsisDotNetPackage(Path.GetFileNameWithoutExtension(ssis_filename), o, output_folder);
}
public static void Help(SsisObject obj, string message)
{
// Figure out what to display
string s = null;
if (obj == null)
{
s = "Help! " + message;
}
else
{
Guid g = obj.GetNearestGuid();
if (g == Guid.Empty)
{
s = String.Format("File {0} Line {1}: {2}", "program.cs", LineNumber, message);
}
else
{
s = String.Format("File {0} Line {1}: {2} (DTSID: {3})", "program.cs", LineNumber, message, obj.GetNearestGuid());
}
}
WriteLine("{0}// ImportError: {1}", new string(' ', IndentSize), message);
// Log this problem
_help_messages.Add(s);
// Emit a comment
Console.WriteLine(s);
}
/// <summary>
/// Attempt to read an SSIS package and produce a meaningful C# program
/// </summary>
/// <param name="ssis_filename"></param>
/// <param name="output_folder"></param>
public static SsisObject ParseSsisPackage(string ssis_filename, SqlCompatibilityType SqlMode = SqlCompatibilityType.SQL2008, bool UseSqlSMO = true)
{
XmlReaderSettings set = new XmlReaderSettings();
set.IgnoreWhitespace = true;
SsisObject o = new SsisObject();
gSqlMode = SqlMode;
// Set the appropriate flag for SMO usage
ProjectWriter.UseSqlServerManagementObjects = UseSqlSMO;
// Read in the file, one element at a time
XmlDocument xd = new XmlDocument();
xd.Load(ssis_filename);
XmlNodeReader xl = new XmlNodeReader(xd);
DataSet d = new DataSet();
d.ReadXml(xl, XmlReadMode.IgnoreSchema);
ReadObject(xd.DocumentElement, o);
return(o);
}
/// <summary>
/// Produce C# files that replicate the functionality of an SSIS package
/// </summary>
/// <param name="o"></param>
private static void ProduceSsisDotNetPackage(string projectname, SsisObject o, string output_folder)
{
ProjectWriter.AppFolder = output_folder;
// First find all the connection strings and write them to an app.config file
var connstrings = from SsisObject c in o.Children where c.DtsObjectType == "DTS:ConnectionManager" select c;
ConnectionWriter.WriteAppConfig(connstrings, Path.Combine(output_folder, "app.config"));
// Next, write all the executable functions to the main file
var functions = from SsisObject c in o.Children where c.DtsObjectType == "DTS:Executable" select c;
if (!functions.Any())
{
var executables = from SsisObject c in o.Children where c.DtsObjectType == "DTS:Executables" select c;
List <SsisObject> flist = new List <SsisObject>();
foreach (var exec in executables)
{
flist.AddRange(from e in exec.Children where e.DtsObjectType == "DTS:Executable" select e);
}
if (flist.Count == 0)
{
Console.WriteLine("No functions ('DTS:Executable') objects found in the specified file.");
return;
}
functions = flist;
}
var variables = from SsisObject c in o.Children where c.DtsObjectType == "DTS:Variable" select c;
WriteProgram(variables, functions, Path.Combine(output_folder, "program.cs"), projectname);
// Next write the resources and the project file
ProjectWriter.WriteResourceAndProjectFile(output_folder, projectname);
}
public override string ToString()
{
if (String.IsNullOrEmpty(Expression))
{
return(String.Format(@"After **{0}** EXECUTE **{1}**", SsisObject.GetObjectByGuid(BeforeGuid).GetFunctionName(), SsisObject.GetObjectByGuid(AfterGuid).GetFunctionName()));
}
else
{
return(String.Format(@"After **{0}**, IF ({2}), EXECUTE **{1}**", SsisObject.GetObjectByGuid(BeforeGuid).GetFunctionName(), SsisObject.GetObjectByGuid(AfterGuid).GetFunctionName(), Expression));
}
}
/// <summary>
/// Get the connection string name when given a GUID
/// </summary>
/// <param name="conn_guid_str"></param>
/// <returns></returns>
public static string GetConnectionStringName(string conn_guid_str)
{
SsisObject connobj = SsisObject.GetObjectByGuid(Guid.Parse(conn_guid_str));
string connstr = "";
if (connobj != null)
{
connstr = connobj.DtsObjectName;
}
return(connstr);
}
public override string ToString()
{
if (String.IsNullOrEmpty(Expression))
{
return(String.Format(@"After ""{0}"" execute ""{1}""", SsisObject.GetObjectByGuid(BeforeGuid).GetFunctionName(), SsisObject.GetObjectByGuid(AfterGuid).GetFunctionName()));
}
else
{
return(String.Format(@"After ""{0}"", if ({2}), ""{1}""", SsisObject.GetObjectByGuid(BeforeGuid).GetFunctionName(), SsisObject.GetObjectByGuid(AfterGuid).GetFunctionName(), Expression));
}
}
public PrecedenceData(SsisObject o)
{
// Retrieve the two guids
SsisObject prior = o.GetChildByTypeAndAttr("DTS:Executable", "DTS:IsFrom", "-1");
BeforeGuid = Guid.Parse(prior.Attributes["IDREF"]);
SsisObject posterior = o.GetChildByTypeAndAttr("DTS:Executable", "DTS:IsFrom", "0");
AfterGuid = Guid.Parse(posterior.Attributes["IDREF"]);
// Retrieve the expression to evaluate
o.Properties.TryGetValue("Expression", out Expression);
}
public static void EmitScriptProject(SsisObject o, string indent)
{
// Find the script object child
var script = o.GetChildByType("DTS:ObjectData").GetChildByType("ScriptProject");
// Create a folder for this script
string project_folder = Path.Combine("C://SSIS//Scripts", o.GetFolderName());
Directory.CreateDirectory(project_folder);
// Extract all the individual script files in this script
foreach (SsisObject child in script.Children)
{
string fn = project_folder + child.Attributes["Name"];
string dir = Path.GetDirectoryName(fn);
Directory.CreateDirectory(dir);
if (child.DtsObjectType == "BinaryItem")
{
byte[] contents = System.Convert.FromBase64String(child.ContentValue);
File.WriteAllBytes(fn, contents);
}
else if (child.DtsObjectType == "ProjectItem")
{
File.WriteAllText(fn, child.ContentValue);
}
// Handle DLL files specially - they are binary! Oh yeah base64 encoded
if (fn.EndsWith(".dll"))
{
DllFiles.Add(fn);
// Note this as a potential problem
SourceWriter.Help(o, "The Visual Basic project " + child.Attributes["Name"] + " was embedded in the DTSX project. Visual Basic code cannot be automatically converted.");
// Show the user that this is how the script should be executed, if they want to fix it
SourceWriter.WriteLine(@"{0}//{1}.ScriptMain sm = new {1}.ScriptMain();", indent, Path.GetFileNameWithoutExtension(fn).Replace("scripttask", "ScriptTask"));
SourceWriter.WriteLine(@"{0}//sm.Main();", indent);
// Is this a project file?
}
else if (fn.EndsWith(".vbproj") || fn.EndsWith(".csproj"))
{
ProjectFiles.Add(fn);
}
else
{
AllFiles.Add(fn);
}
}
}
public ColumnVariable(SsisObject o)
{
int.TryParse(o.Attributes["id"], out ID);
Name = o.Attributes["name"];
SsisTypeName = o.Attributes["dataType"];
if (o.Attributes.ContainsKey("lineageId"))
{
LineageID = o.Attributes["lineageId"];
}
Precision = o.Attributes["precision"];
Scale = o.Attributes["scale"];
CodePage = o.Attributes["codePage"];
Length = o.Attributes["length"];
}
/// <summary>
/// Recursive read function
/// </summary>
/// <param name="xr"></param>
/// <param name="o"></param>
private static void ReadObject(XmlElement el, SsisObject o)
{
// Read in the object name
o.DtsObjectType = el.Name;
// Read in attributes
foreach (XmlAttribute xa in el.Attributes)
{
o.Attributes.Add(xa.Name, xa.Value);
}
// Iterate through all children of this element
foreach (XmlNode child in el.ChildNodes)
{
// For child elements
if (child is XmlElement)
{
XmlElement child_el = child as XmlElement;
// Read in a DTS Property
if (child.Name == "DTS:Property" || child.Name == "DTS:PropertyExpression")
{
ReadDtsProperty(child_el, o);
// Everything else is a sub-object
}
else
{
SsisObject child_obj = new SsisObject();
ReadObject(child_el, child_obj);
child_obj.Parent = o;
o.Children.Add(child_obj);
}
}
else if (child is XmlText)
{
o.ContentValue = child.InnerText;
}
else if (child is XmlCDataSection)
{
o.ContentValue = child.InnerText;
}
else
{
Console.WriteLine("Help");
}
}
}
/// <summary>
/// Write a program file that has all the major executable instructions as functions
/// </summary>
/// <param name="variables"></param>
/// <param name="functions"></param>
/// <param name="p"></param>
private static void WriteDocumentation(SsisObject o, IEnumerable <SsisObject> variables, IEnumerable <SsisObject> connstrings, IEnumerable <SsisObject> functions, string filename)
{
//Get Package Creator Name
var creatorName = "CreatorName - " + o.Properties["CreatorName"];
//Get Version GUID
string versionGuid = "VersionGUID - " + o.Properties["VersionGUID"];
//Get Package Name
var packageName = o.DtsObjectName + ".dtsx";
//var packageName = "PackageName - " + o.DtsObjectName;
using (SourceWriter.SourceFileStream = new StreamWriter(filename, false, Encoding.UTF8))
{
SourceWriter.WriteLine(packageName.ToMarkdownHeader());
SourceWriter.WriteLine(creatorName.ToMarkdownSubHeader());
SourceWriter.WriteLine(versionGuid.ToMarkdownSubHeader());
WriteVariables(variables);
WriteConnectionManagers(connstrings);
SourceWriter.WriteLine(@"");
SourceWriter.WriteLine(@"");
//Write each executable out as a function
SourceWriter.WriteLine(@"Executables".ToMarkdownSubHeader());
SourceWriter.WriteLine(@"* #### Root Level Executables");
foreach (SsisObject v in functions)
{
SourceWriter.WriteLine(@" * {0}", v.DtsObjectName);
SourceWriter.WriteLine(@" * {0}", v.Description);
}
SourceWriter.WriteLine(@"* #### Executable Flows");
foreach (SsisObject v in functions)
{
v.EmitFunctionsAsSequence("\t", new List <ProgramVariable>());
}
SourceWriter.WriteLine(@"* #### Executables");
foreach (SsisObject v in functions)
{
v.EmitFunction("\t", new List <ProgramVariable>());
}
}
}
/// <summary>
/// Read in a DTS property from the XML stream
/// </summary>
/// <param name="xr"></param>
/// <param name="o"></param>
private static void ReadDtsProperty(XmlElement el, SsisObject o)
{
string prop_name = null;
// Read all the attributes
foreach (XmlAttribute xa in el.Attributes)
{
if (String.Equals(xa.Name, "DTS:Name", StringComparison.CurrentCultureIgnoreCase))
{
prop_name = xa.Value;
break;
}
}
// Set the property
o.SetProperty(prop_name, el.InnerText);
}
public static void Main()
{
string package = "";
//package = "C:\\SSIS\\packages\\Load_CMOR_DELINQUENCY_GROUP.dtsx";
//package = "C:\\SSIS\\packages\\Datafeed_Quandis_Main.dtsx";
//package = "C:\\SSIS\\packages\\Load_CMOR_COMMUNICATION_REF_GROUP.dtsx";
package = "C:\\SSIS\\packages\\Source_HHF.dtsx";
SsisObject ssisobj = new SsisObject();
ssisobj = ParseSsisPackage(package);
string filename = "C:\\SSIS\\program.md";
string htmlOutputFilename = "C:\\SSIS\\program.html";
ProduceSsisDocumentation(ssisobj, filename, htmlOutputFilename);
}
/// <summary>
/// Get the connection string name when given a GUID
/// </summary>
/// <param name="conn_guid_str"></param>
/// <returns></returns>
public static string GetConnectionStringPrefix(string conn_guid_str)
{
SsisObject connobj = SsisObject.GetObjectByGuid(Guid.Parse(conn_guid_str));
if (connobj == null)
{
return("Sql");
}
string objecttype = connobj.Properties["CreationName"];
if (objecttype.StartsWith("OLEDB"))
{
return("OleDb");
}
else if (objecttype.StartsWith("ADO.NET:System.Data.SqlClient.SqlConnection"))
{
return("Sql");
}
else
{
SourceWriter.Help(null, "I don't understand the database connection type " + objecttype);
}
return("");
}
public ProgramVariable(SsisObject o, bool as_global)
{
// Figure out what type of a variable we are
DtsType = o.GetChildByType("DTS:VariableValue").Attributes["DTS:DataType"];
CSharpType = null;
DefaultValue = o.GetChildByType("DTS:VariableValue").ContentValue;
Comment = o.Description;
Namespace = o.Properties["Namespace"];
IsGlobal = as_global;
VariableName = o.DtsObjectName;
// Here are the DTS type codes I know
if (DtsType == "3")
{
CSharpType = "int";
}
else if (DtsType == "8")
{
CSharpType = "string";
if (!String.IsNullOrEmpty(DefaultValue))
{
DefaultValue = "\"" + DefaultValue.Replace("\\", "\\\\").Replace("\"", "\\\"") + "\"";
}
}
else if (DtsType == "13")
{
CSharpType = "DataTable";
DefaultValue = "new DataTable()";
}
else if (DtsType == "2")
{
CSharpType = "short";
}
else if (DtsType == "11")
{
CSharpType = "bool";
if (DefaultValue == "1")
{
DefaultValue = "true";
}
else
{
DefaultValue = "false";
}
}
else if (DtsType == "20")
{
CSharpType = "long";
}
else if (DtsType == "7")
{
CSharpType = "DateTime";
if (!String.IsNullOrEmpty(DefaultValue))
{
DefaultValue = "DateTime.Parse(\"" + DefaultValue + "\")";
}
}
else
{
SourceWriter.Help(o, "I don't understand DTS type " + DtsType);
}
}
