Exemple #1
0
        /// <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);
        }
Exemple #7
0
        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);
        }
Exemple #8
0
        /// <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);
            }
        }
Exemple #10
0
        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);
        }
Exemple #11
0
        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);
        }
Exemple #12
0
        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);
                }
            }
        }
Exemple #13
0
        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.");
                }
            }
        }
Exemple #14
0
        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);
        }
Exemple #15
0
        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);
            }
        }
Exemple #16
0
        /// <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);
        }
Exemple #17
0
        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);
                }
            }
        }