/// <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 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"];
}
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 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);
}
}
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(AppFolder, 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 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);
}
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);
}
/// <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");
}
}
}
private void EmitFlatFilePipelineReader(SsisObject pipeline, string indent)
{
// Make sure to include CSV
ProjectWriter.UseCsvFile = true;
// Produce a function header
SourceWriter.WriteLine();
SourceWriter.WriteLine(@"{0}// {1}", indent, Attributes["name"]);
// Get the connection string GUID: it's this.connections.connection
string conn_guid = this.GetChildByType("connections").GetChildByType("connection").Attributes["connectionManagerID"];
SsisObject flat_file_obj = GetObjectByGuid(conn_guid).Children[0].Children[0];
// Some sensible checks
if (flat_file_obj.Properties["Format"] != "Delimited") {
SourceWriter.Help(flat_file_obj, "The flat file data source is not delimited - DESSIST doesn't have support for this file type!");
}
// Retrieve what we need to know about this flat file
string filename = flat_file_obj.Properties["ConnectionString"];
// Generate the list of column headers from the connection string object
List<SsisObject> columns = new List<SsisObject>();
foreach (var child in flat_file_obj.Children) {
if (child.DtsObjectType == "DTS:FlatFileColumn") {
columns.Add(child);
}
}
// Now produce the list of lineage objects from the component
List<SsisObject> outputs = new List<SsisObject>();
foreach (var child in this.GetChildByType("outputs").GetChildByType("output").GetChildByType("outputColumns").Children) {
if (child.DtsObjectType == "outputColumn") {
outputs.Add(child);
}
}
if (columns.Count != outputs.Count) {
SourceWriter.Help(this, "The list of columns in this flat file component doesn't match the columns in the data source.");
}
// Now pair up all the outputs to generate header columns and lineage objects
StringBuilder headers = new StringBuilder("new string[] {");
for (int i = 0; i < columns.Count; i++) {
string name = columns[i].DtsObjectName;
headers.AppendFormat("\"{0}\", ", name.Replace("\"", "\\\""));
LineageObject lo = new LineageObject(outputs[i].Attributes["lineageId"], "component" + this.Attributes["id"], name);
pipeline._lineage_columns.Add(lo);
}
headers.Length -= 2;
headers.Append(" }");
// This is set to -1 if the column names aren't in the first row in the data file
string qual = FixDelimiter(flat_file_obj.Properties["TextQualifier"]);
string delim = FixDelimiter(columns[0].Properties["ColumnDelimiter"]);
if (flat_file_obj.Properties["ColumnNamesInFirstDataRow"] == "-1") {
SourceWriter.WriteLine(@"{0}DataTable component{1} = CSVFile.CSV.LoadDataTable(""{2}"", {3}, true, '{4}', '{5}');", indent, this.Attributes["id"], filename.Replace("\\", "\\\\"), headers.ToString(), delim, qual);
} else {
SourceWriter.WriteLine(@"{0}DataTable component{1} = CSVFile.CSV.LoadDataTable(""{2}"", true, true, '{3}', '{4}');", indent, this.Attributes["id"], filename.Replace("\\", "\\\\"), delim, qual);
}
}
private void EmitPipelineWriter_TableParam(SsisObject pipeline, string indent)
{
SourceWriter.WriteLine();
SourceWriter.WriteLine(@"{0}// {1}", indent, Attributes["name"]);
// Get the connection string GUID: it's this.connections.connection
string conn_guid = this.GetChildByType("connections").GetChildByType("connection").Attributes["connectionManagerID"];
string connstr = ConnectionWriter.GetConnectionStringName(conn_guid);
string connprefix = ConnectionWriter.GetConnectionStringPrefix(conn_guid);
// Report potential problems - can we programmatically convert an OleDb connection into an ADO.NET one?
string fixup = "";
if (connprefix == "OleDb") {
SourceWriter.Help(this, "DESSIST had to rewrite an OleDb connection as an ADO.NET connection. Please check it for correctness.");
connprefix = "Sql";
fixup = @".Replace(""Provider=SQLNCLI10.1;"","""")";
}
// It's our problem to produce the SQL statement, because this writer uses calculated data!
StringBuilder sql = new StringBuilder();
StringBuilder colnames = new StringBuilder();
StringBuilder varnames = new StringBuilder();
StringBuilder paramsetup = new StringBuilder();
StringBuilder TableParamCreate = new StringBuilder();
StringBuilder TableSetup = new StringBuilder();
// Create the table parameter insert statement
string tableparamname = this.GetFunctionName() + "_WritePipe_TableParam";
TableParamCreate.AppendFormat("IF EXISTS (SELECT * FROM systypes where name = '{0}') DROP TYPE {0}; {1} CREATE TYPE {0} AS TABLE (", tableparamname, Environment.NewLine);
// Retrieve the names of the columns we're inserting
SsisObject metadata = this.GetChildByType("inputs").GetChildByType("input").GetChildByType("externalMetadataColumns");
SsisObject columns = this.GetChildByType("inputs").GetChildByType("input").GetChildByType("inputColumns");
// Okay, let's produce the columns we're inserting
foreach (SsisObject column in columns.Children) {
ColumnVariable cv = new ColumnVariable(metadata.GetChildByTypeAndAttr("externalMetadataColumn", "id", column.Attributes["externalMetadataColumnId"]));
// Add to the table parameter create
TableParamCreate.AppendFormat("{0} {1} NULL, ", cv.Name, cv.SqlDbType());
// List of columns in the insert
colnames.AppendFormat("{0}, ", cv.Name);
// List of parameter names in the values clause
varnames.AppendFormat("@{0}, ", cv.Name);
// The columns in the in-memory table
TableSetup.AppendFormat(@"{0}component{1}.Columns.Add(""{2}"");{3}", indent, this.Attributes["id"], cv.Name, Environment.NewLine);
// Find the source column in our lineage data
string lineageId = column.Attributes["lineageId"];
LineageObject lo = pipeline.GetLineageObjectById(lineageId);
// Parameter setup instructions
if (lo == null) {
SourceWriter.Help(this, "I couldn't find lineage column " + lineageId);
paramsetup.AppendFormat(@"{0} // Unable to find column {1}{2}", indent, lineageId, Environment.NewLine);
} else {
paramsetup.AppendFormat(@"{0} dr[""{1}""] = {2};{3}", indent, cv.Name, lo.ToString(), Environment.NewLine);
}
}
colnames.Length -= 2;
varnames.Length -= 2;
TableParamCreate.Length -= 2;
TableParamCreate.Append(")");
// Insert the table parameter create statement in the project
string sql_tableparam_resource = ProjectWriter.AddSqlResource(GetParentDtsName() + "_WritePipe_TableParam", TableParamCreate.ToString());
// Produce a data set that we're going to process - name it after ourselves
SourceWriter.WriteLine(@"{0}DataTable component{1} = new DataTable();", indent, this.Attributes["id"]);
SourceWriter.WriteLine(TableSetup.ToString());
// Check the inputs to see what component we're using as the source
string component = null;
foreach (SsisObject incol in this.GetChildByType("inputs").GetChildByType("input").GetChildByType("inputColumns").Children) {
LineageObject input = pipeline.GetLineageObjectById(incol.Attributes["lineageId"]);
if (component == null) {
component = input.DataTableName;
} else {
if (component != input.DataTableName) {
//SourceWriter.Help(this, "This SSIS pipeline is merging different component tables!");
// From closer review, this doesn't look like a problem - it's most likely due to transformations occuring on output of a table
}
}
}
// Now fill the table in memory
SourceWriter.WriteLine();
SourceWriter.WriteLine(@"{0}// Fill our table parameter in memory", indent);
SourceWriter.WriteLine(@"{0}for (int row = 0; row < {1}.Rows.Count; row++) {{", indent, component);
SourceWriter.WriteLine(@"{0} DataRow dr = component{1}.NewRow();", indent, this.Attributes["id"]);
SourceWriter.WriteLine(paramsetup.ToString());
SourceWriter.WriteLine(@"{0} component{1}.Rows.Add(dr);", indent, this.Attributes["id"]);
SourceWriter.WriteLine(@"{0}}}", indent);
// Produce the SQL statement
sql.AppendFormat("INSERT INTO {0} ({1}) SELECT {1} FROM @tableparam",
GetChildByType("properties").GetChildByTypeAndAttr("property", "name", "OpenRowset").ContentValue,
colnames.ToString());
string sql_resource_name = ProjectWriter.AddSqlResource(GetParentDtsName() + "_WritePipe", sql.ToString());
// Write the using clause for the connection
SourceWriter.WriteLine();
SourceWriter.WriteLine(@"{0}// Time to drop this all into the database", indent);
SourceWriter.WriteLine(@"{0}using (var conn = new {2}Connection(ConfigurationManager.AppSettings[""{1}""]{3})) {{", indent, connstr, connprefix, fixup);
SourceWriter.WriteLine(@"{0} conn.Open();", indent);
// Ensure the table parameter type has been created correctly
SourceWriter.WriteLine();
SourceWriter.WriteLine(@"{0} // Ensure the table parameter type has been created successfully", indent);
SourceWriter.WriteLine(@"{0} CreateTableParamType(""{1}"", conn);", indent, sql_tableparam_resource);
// Let's use our awesome table parameter style!
SourceWriter.WriteLine();
SourceWriter.WriteLine(@"{0} // Insert all rows at once using fast table parameter insert", indent);
SourceWriter.WriteLine(@"{0} using (var cmd = new {2}Command(Resource1.{1}, conn)) {{", indent, sql_resource_name, connprefix);
// Determine the timeout value specified in the pipeline
SsisObject timeout_property = this.GetChildByType("properties").GetChildByTypeAndAttr("property", "name", "CommandTimeout");
if (timeout_property != null) {
int timeout = int.Parse(timeout_property.ContentValue);
SourceWriter.WriteLine(@"{0} cmd.CommandTimeout = {1};", indent, timeout);
}
// Insert the table in one swoop
SourceWriter.WriteLine(@"{0} SqlParameter param = new SqlParameter(""@tableparam"", SqlDbType.Structured);", indent);
SourceWriter.WriteLine(@"{0} param.Value = component{1};", indent, this.Attributes["id"]);
SourceWriter.WriteLine(@"{0} param.TypeName = ""{1}"";", indent, tableparamname);
SourceWriter.WriteLine(@"{0} cmd.Parameters.Add(param);", indent);
SourceWriter.WriteLine(@"{0} cmd.ExecuteNonQuery();", indent);
SourceWriter.WriteLine(@"{0} }}", indent);
SourceWriter.WriteLine(@"{0}}}", indent);
}
private void EmitPipelineWriter(SsisObject pipeline, string indent)
{
SourceWriter.WriteLine();
SourceWriter.WriteLine(@"{0}// {1}", indent, Attributes["name"]);
// Get the connection string GUID: it's this.connections.connection
string conn_guid = this.GetChildByType("connections").GetChildByType("connection").Attributes["connectionManagerID"];
string connstr = ConnectionWriter.GetConnectionStringName(conn_guid);
string connprefix = ConnectionWriter.GetConnectionStringPrefix(conn_guid);
// Report potential problems - can we programmatically convert an OleDb connection into an ADO.NET one?
string fixup = "";
if (connprefix == "OleDb") {
SourceWriter.Help(this, "DESSIST had to rewrite an OleDb connection as an ADO.NET connection. Please check it for correctness.");
connprefix = "Sql";
fixup = @".Replace(""Provider=SQLNCLI10.1;"","""")";
}
// It's our problem to produce the SQL statement, because this writer uses calculated data!
StringBuilder sql = new StringBuilder();
StringBuilder colnames = new StringBuilder();
StringBuilder varnames = new StringBuilder();
StringBuilder paramsetup = new StringBuilder();
// Retrieve the names of the columns we're inserting
SsisObject metadata = this.GetChildByType("inputs").GetChildByType("input").GetChildByType("externalMetadataColumns");
SsisObject columns = this.GetChildByType("inputs").GetChildByType("input").GetChildByType("inputColumns");
// Okay, let's produce the columns we're inserting
foreach (SsisObject column in columns.Children) {
SsisObject mdcol = metadata.GetChildByTypeAndAttr("externalMetadataColumn", "id", column.Attributes["externalMetadataColumnId"]);
// List of columns in the insert
colnames.Append(mdcol.Attributes["name"]);
colnames.Append(", ");
// List of parameter names in the values clause
varnames.Append("@");
varnames.Append(mdcol.Attributes["name"]);
varnames.Append(", ");
// Find the source column in our lineage data
string lineageId = column.Attributes["lineageId"];
LineageObject lo = pipeline.GetLineageObjectById(lineageId);
// Parameter setup instructions
if (lo == null) {
SourceWriter.Help(this, "I couldn't find lineage column " + lineageId);
paramsetup.AppendFormat(@"{0} // Unable to find column {1}{2}", indent, lineageId, Environment.NewLine);
} else {
// Is this a string? If so, forcibly truncate it
if (mdcol.Attributes["dataType"] == "str") {
paramsetup.AppendFormat(@"{0} cmd.Parameters.Add(new SqlParameter(""@{1}"", SqlDbType.VarChar, {3}, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, {2}));
", indent, mdcol.Attributes["name"], lo.ToString(), mdcol.Attributes["length"]);
} else if (mdcol.Attributes["dataType"] == "wstr") {
paramsetup.AppendFormat(@"{0} cmd.Parameters.Add(new SqlParameter(""@{1}"", SqlDbType.NVarChar, {3}, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, {2}));
", indent, mdcol.Attributes["name"], lo.ToString(), mdcol.Attributes["length"]);
} else {
paramsetup.AppendFormat(@"{0} cmd.Parameters.AddWithValue(""@{1}"",{2});
", indent, mdcol.Attributes["name"], lo.ToString());
}
}
}
colnames.Length -= 2;
varnames.Length -= 2;
// Produce the SQL statement
sql.Append("INSERT INTO ");
sql.Append(GetChildByType("properties").GetChildByTypeAndAttr("property", "name", "OpenRowset").ContentValue);
sql.Append(" (");
sql.Append(colnames.ToString());
sql.Append(") VALUES (");
sql.Append(varnames.ToString());
sql.Append(")");
string sql_resource_name = ProjectWriter.AddSqlResource(GetParentDtsName() + "_WritePipe", sql.ToString());
// Produce a data set that we're going to process - name it after ourselves
SourceWriter.WriteLine(@"{0}DataTable component{1} = new DataTable();", indent, this.Attributes["id"]);
// Write the using clause for the connection
SourceWriter.WriteLine(@"{0}using (var conn = new {2}Connection(ConfigurationManager.AppSettings[""{1}""]{3})) {{", indent, connstr, connprefix, fixup);
SourceWriter.WriteLine(@"{0} conn.Open();", indent);
// TODO: SQL Parameters should go in here
// Check the inputs to see what component we're using as the source
string component = null;
foreach (SsisObject incol in this.GetChildByType("inputs").GetChildByType("input").GetChildByType("inputColumns").Children) {
LineageObject input = pipeline.GetLineageObjectById(incol.Attributes["lineageId"]);
if (component == null) {
component = input.DataTableName;
} else {
if (component != input.DataTableName) {
//SourceWriter.Help(this, "This SSIS pipeline is merging different component tables!");
// From closer review, this doesn't look like a problem - it's most likely due to transformations occuring on output of a table
}
}
}
// This is the laziest possible way to do this insert - may want to improve it later
SourceWriter.WriteLine(@"{0} for (int row = 0; row < {1}.Rows.Count; row++) {{", indent, component);
SourceWriter.WriteLine(@"{0} using (var cmd = new {2}Command(Resource1.{1}, conn)) {{", indent, sql_resource_name, connprefix);
int timeout = 0;
SourceWriter.WriteLine(@"{0} cmd.CommandTimeout = {1};", indent, timeout);
SourceWriter.WriteLine(paramsetup.ToString());
SourceWriter.WriteLine(@"{0} cmd.ExecuteNonQuery();", indent);
SourceWriter.WriteLine(@"{0} }}", indent);
SourceWriter.WriteLine(@"{0} }}", indent);
SourceWriter.WriteLine(@"{0}}}", indent);
}
private void EmitPipelineUnion(SsisObject pipeline, string indent)
{
SourceWriter.WriteLine();
SourceWriter.WriteLine(@"{0}// {1}", indent, Attributes["name"]);
// Create a new datatable
SourceWriter.WriteLine(@"{0}DataTable component{1} = new DataTable();", indent, this.Attributes["id"]);
// Add the columns we're generating
int i = 0;
List<SsisObject> transforms = this.GetChildByType("outputs").GetChildByTypeAndAttr("output", "isErrorOut", "false").GetChildByType("outputColumns").Children;
List<string> colnames = new List<string>();
foreach (SsisObject outcol in transforms) {
ColumnVariable cv = new ColumnVariable(outcol);
LineageObject lo = new LineageObject(cv.LineageID, "component" + this.Attributes["id"], cv.Name);
i++;
pipeline._lineage_columns.Add(lo);
// Print out this column
SourceWriter.WriteLine(@"{0}component{1}.Columns.Add(new DataColumn(""{2}"", typeof({3})));", indent, this.Attributes["id"], cv.Name, cv.CsharpType());
DataTable dt = new DataTable();
colnames.Add(cv.Name);
}
// Loop through all the inputs and process them!
SsisObject outputcolumns = this.GetChildByType("outputs").GetChildByType("output").GetChildByType("outputColumns");
foreach (SsisObject inputtable in this.GetChildByType("inputs").Children) {
// Find the name of the table by looking at the first column
SsisObject input_columns = inputtable.GetChildByType("inputColumns");
SsisObject metadata = inputtable.GetChildByType("externalMetadataColumns");
if (input_columns != null) {
SsisObject first_col = input_columns.Children[0];
LineageObject first_input = pipeline.GetLineageObjectById(first_col.Attributes["lineageId"]);
string component = first_input.DataTableName;
// Read through all rows in the table
SourceWriter.WriteLine(@"{0}for (int row = 0; row < {1}.Rows.Count; row++) {{", indent, component);
SourceWriter.WriteLine(@"{0} DataRow dr = component{1}.NewRow();", indent, this.Attributes["id"]);
// Loop through all the columns and insert them
foreach (SsisObject col in inputtable.GetChildByType("inputColumns").Children) {
LineageObject l = pipeline.GetLineageObjectById(col.Attributes["lineageId"]);
// find the matching external metadata column
string outcolname = "";
SsisObject mdcol = metadata.GetChildByTypeAndAttr("externalMetadataColumn", "id", col.Attributes["externalMetadataColumnId"]);
if (mdcol == null) {
SsisObject prop = col.GetChildByType("properties").GetChildByType("property");
SsisObject outcol = outputcolumns.GetChildByTypeAndAttr("outputColumn", "id", prop.ContentValue);
outcolname = outcol.Attributes["name"];
} else {
outcolname = mdcol.Attributes["name"];
}
// Write out the expression
SourceWriter.WriteLine(@"{0} dr[""{1}""] = {2};", indent, outcolname, l.ToString());
}
// Write the end of this code block
SourceWriter.WriteLine(@"{0} component{1}.Rows.Add(dr);", indent, this.Attributes["id"]);
SourceWriter.WriteLine(@"{0}}}", indent);
}
}
}
private void EmitPipelineTransform(SsisObject pipeline, string indent)
{
// Add the columns we're generating
List<SsisObject> transforms = this.GetChildByType("outputs").GetChildByTypeAndAttr("output", "isErrorOut", "false").GetChildByType("outputColumns").Children;
// Check the inputs to see what component we're using as the source
string component = "component1";
SsisObject inputcolumns = this.GetChildByType("inputs").GetChildByType("input").GetChildByType("inputColumns");
if (inputcolumns != null) {
foreach (SsisObject incol in inputcolumns.Children) {
LineageObject input = pipeline.GetLineageObjectById(incol.Attributes["lineageId"]);
if (component == null) {
component = input.DataTableName;
} else {
if (component != input.DataTableName) {
SourceWriter.Help(this, "This SSIS pipeline is merging different component tables!");
}
}
}
}
// Let's see if we can generate some code to do these conversions!
foreach (SsisObject outcol in transforms) {
ColumnVariable cv = new ColumnVariable(outcol);
LineageObject source_lineage = null;
string expression = null;
// Find property "expression"
if (outcol.Children.Count > 0) {
foreach (SsisObject property in outcol.GetChildByType("properties").Children) {
if (property.Attributes["name"] == "SourceInputColumnLineageID") {
source_lineage = pipeline.GetLineageObjectById(property.ContentValue);
expression = String.Format(@"Convert.ChangeType({1}.Rows[row][""{2}""], typeof({0}));", cv.CsharpType(), source_lineage.DataTableName, source_lineage.FieldName);
} else if (property.Attributes["name"] == "FastParse") {
// Don't need to do anything here
} else if (property.Attributes["name"] == "Expression") {
// Is this a lineage column?
expression = FixExpression(cv.CsharpType(), pipeline._lineage_columns, property.ContentValue, true);
} else if (property.Attributes["name"] == "FriendlyExpression") {
// This comment is useless - SourceWriter.WriteLine(@"{0} // {1}", indent, property.ContentValue);
} else {
SourceWriter.Help(this, "I don't understand the output column property '" + property.Attributes["name"] + "'");
}
}
// If we haven't been given an explicit expression, just use this
if (String.IsNullOrEmpty(expression)) {
SourceWriter.Help(this, "I'm trying to do a transform, but I haven't found an expression to use.");
} else {
// Put this transformation back into the lineage table for later use!
LineageObject lo = new LineageObject(outcol.Attributes["lineageId"], expression);
pipeline._lineage_columns.Add(lo);
}
} else {
SourceWriter.Help(this, "I'm trying to do a transform, but I don't have any properties to use.");
}
}
}
private void EmitPipelineReader(SsisObject pipeline, string indent)
{
SourceWriter.WriteLine();
SourceWriter.WriteLine(@"{0}// {1}", indent, Attributes["name"]);
// Get the connection string GUID: it's this.connections.connection
string conn_guid = this.GetChildByType("connections").GetChildByType("connection").Attributes["connectionManagerID"];
string connstr = ConnectionWriter.GetConnectionStringName(conn_guid);
string connprefix = ConnectionWriter.GetConnectionStringPrefix(conn_guid);
// Get the SQL statement
string sql = this.GetChildByType("properties").GetChildByTypeAndAttr("property", "name", "SqlCommand").ContentValue;
if (sql == null) {
string rowset = this.GetChildByType("properties").GetChildByTypeAndAttr("property", "name", "OpenRowset").ContentValue;
if (rowset == null) {
sql = "COULD NOT FIND SQL STATEMENT";
SourceWriter.Help(pipeline, String.Format("Could not find SQL for {0} in {1}", Attributes["name"], this.DtsId));
} else {
sql = "SELECT * FROM " + rowset;
}
}
string sql_resource_name = ProjectWriter.AddSqlResource(GetParentDtsName() + "_ReadPipe", sql);
// Produce a data set that we're going to process - name it after ourselves
SourceWriter.WriteLine(@"{0}DataTable component{1} = new DataTable();", indent, this.Attributes["id"]);
// Keep track of the lineage of all of our output columns
// TODO: Handle error output columns
int i = 0;
foreach (SsisObject outcol in this.GetChildByType("outputs").GetChildByTypeAndAttr("output", "isErrorOut", "false").GetChildByType("outputColumns").Children) {
LineageObject lo = new LineageObject(outcol.Attributes["lineageId"], "component" + this.Attributes["id"], outcol.Attributes["name"]);
i++;
pipeline._lineage_columns.Add(lo);
}
// Write the using clause for the connection
SourceWriter.WriteLine(@"{0}using (var conn = new {2}Connection(ConfigurationManager.AppSettings[""{1}""])) {{", indent, connstr, connprefix);
SourceWriter.WriteLine(@"{0} conn.Open();", indent);
SourceWriter.WriteLine(@"{0} using (var cmd = new {2}Command(Resource1.{1}, conn)) {{", indent, sql_resource_name, connprefix);
// Determine the timeout value specified in the pipeline
SsisObject timeout_property = this.GetChildByType("properties").GetChildByTypeAndAttr("property", "name", "CommandTimeout");
if (timeout_property != null) {
int timeout = int.Parse(timeout_property.ContentValue);
SourceWriter.WriteLine(@"{0} cmd.CommandTimeout = {1};", indent, timeout);
}
// Okay, let's load the parameters
var paramlist = this.GetChildByType("properties").GetChildByTypeAndAttr("property", "name", "ParameterMapping");
if (paramlist != null && paramlist.ContentValue != null) {
string[] p = paramlist.ContentValue.Split(';');
int paramnum = 0;
foreach (string oneparam in p) {
if (!String.IsNullOrEmpty(oneparam)) {
string[] parts = oneparam.Split(',');
Guid g = Guid.Parse(parts[1]);
// Look up this GUID - can we find it?
SsisObject v = GetObjectByGuid(g);
if (connprefix == "OleDb") {
SourceWriter.WriteLine(@"{0} cmd.Parameters.Add(new OleDbParameter(""@p{2}"",{1}));", indent, v.DtsObjectName, paramnum);
} else {
SourceWriter.WriteLine(@"{0} cmd.Parameters.AddWithValue(""@{1}"",{2});", indent, parts[0], v.DtsObjectName);
}
}
paramnum++;
}
}
// Finish up the pipeline reader
SourceWriter.WriteLine(@"{0} {1}DataReader dr = cmd.ExecuteReader();", indent, connprefix);
SourceWriter.WriteLine(@"{0} component{1}.Load(dr);", indent, this.Attributes["id"]);
SourceWriter.WriteLine(@"{0} dr.Close();", indent);
SourceWriter.WriteLine(@"{0} }}", indent);
SourceWriter.WriteLine(@"{0}}}", indent);
}
private void EmitOneChild(SsisObject childobj, string newindent)
{
// Is this a dummy "Object Data" thing? If so ignore it and delve deeper
if (childobj.DtsObjectType == "DTS:ObjectData") {
childobj = childobj.Children[0];
}
// For variables, emit them within this function
if (childobj.DtsObjectType == "DTS:Variable") {
_scope_variables.Add(childobj.EmitVariable(newindent, false));
} else if (childobj.DtsObjectType == "DTS:Executable") {
childobj.EmitFunctionCall(newindent, GetScopeVariables(false));
} else if (childobj.DtsObjectType == "SQLTask:SqlTaskData") {
childobj.EmitSqlStatement(newindent);
// TODO: Handle "pipeline" objects
} else if (childobj.DtsObjectType == "pipeline") {
childobj.EmitPipeline(newindent);
} else if (childobj.DtsObjectType == "DTS:PrecedenceConstraint") {
// ignore it - it's already been handled
} else if (childobj.DtsObjectType == "DTS:LoggingOptions") {
// Ignore it - I can't figure out any useful information on this object
} else if (childobj.DtsObjectType == "DTS:ForEachVariableMapping") {
// ignore it - handled earlier
} else if (childobj.DtsObjectType == "DTS:ForEachEnumerator") {
// ignore it - handled explicitly by the foreachloop
} else {
SourceWriter.Help(this, "I don't yet know how to handle " + childobj.DtsObjectType);
}
}
/// <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);
}
private void PrecedenceChain(SsisObject prior_obj, List<PrecedenceData> precedence, string indent)
{
EmitOneChild(prior_obj, indent);
// We just executed "prior_obj" - find what objects it causes to be triggered
var triggered = (from PrecedenceData pd in precedence where pd.BeforeGuid == prior_obj.DtsId select pd);
// Iterate through each of these
foreach (PrecedenceData pd in triggered) {
// Write a comment
SourceWriter.WriteLine();
SourceWriter.WriteLine("{0}// {1}", indent, pd.ToString());
// Is there an expression?
if (!String.IsNullOrEmpty(pd.Expression)) {
SourceWriter.WriteLine(@"{0}if ({1}) {{", indent, FixExpression("System.Boolean", _lineage_columns, pd.Expression, true));
PrecedenceChain(pd.Target, precedence, indent + " ");
SourceWriter.WriteLine(@"{0}}}", indent);
} else {
PrecedenceChain(pd.Target, precedence, indent);
}
}
}
