public LineageToken(ref string s, ExpressionData parent)
{
Match m = LINEAGE_REGEX.Match(s);
if (m.Success)
{
var li = (from LineageObject l in parent.Lineage where l.LineageId == m.Groups[1].Value select l).FirstOrDefault();
if (li != null)
{
s = s.Substring(m.Index + m.Length);
LineageRef = li;
}
else
{
SourceWriter.Help(null, "Unable to find lineage reference #" + s);
}
}
}
public LineageToken(ref string s, ExpressionData parent)
{
Match m = LINEAGE_REGEX.Match(s);
if (m.Success) {
var li = (from LineageObject l in parent.Lineage where l.LineageId == m.Groups[1].Value select l).FirstOrDefault();
if (li != null) {
s = s.Substring(m.Index + m.Length);
LineageRef = li;
} else {
SourceWriter.Help(null, "Unable to find lineage reference #" + s);
}
}
}
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 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);
}
}
