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."); } } }