public ConversionToken(ref string s, ExpressionData parent)
{
Match m = CONVERT_REGEX.Match(s);
if (m.Success)
{
s = s.Substring(m.Index + m.Length);
TypeRef = ColumnVariable.LookupSsisTypeName(m.Groups[1].Value);
TokenToConvert = parent.ConsumeToken(ref s);
}
else
{
SourceWriter.Help(null, "Unable to parse conversion token " + 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 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 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.");
}
}
}
