static void Main(string[] args) { // Dont try and make sense of any of this code....I still don't get it....this was a 2 day hack....seriously, rewrite it if you want to make it into a useful tool // WARNING: I catch general exceptions. TA-DA!! int result = -2; OleDbConnection conn = null; OleDbCommand entire_excel_file = null; OleDbDataReader config_data_reader = null; // truncate bobby tables System.IO.StreamWriter bt = new StreamWriter("bobby_tables.sql"); bt.Close(); // populate the variables. num_cols = Enum.GetValues(typeof(ConfigColumnNames)).Length; //args = new string[] { "", @"C:\Users\koln01\Desktop\ESPN\AD_Scope\pip_config.xls" }; app = new Microsoft.SqlServer.Dts.Runtime.Application(); // parameter check, pretty lame one at that. if (args.Length != 1) { Console.WriteLine("This program takes a single argument. The path to an Excel (xlsx) file with config parameters"); System.Environment.Exit(0); } //query the excel file to get the entire result set so we can traverse it. string config_cs = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + args[0] + ";Extended Properties=\"Excel 12.0;HDR=YES\";"; try { conn = new OleDbConnection(config_cs); conn.Open(); } catch (Exception) { Console.WriteLine("There was an error opening the connection to the excel file you gave, make sure you gave the correct path to the correct config"); Console.WriteLine("This is the path I have: {0}", args[0]); Console.WriteLine("IF that is the correct path, Make sure there isn't a lock on the file (ie. it is open by another program)"); System.Environment.Exit(0); } try { entire_excel_file = new OleDbCommand("select * from [PIP$] where TableName is not NULL", conn); config_data_reader = entire_excel_file.ExecuteReader(); } catch (Exception) { Console.WriteLine("I tried to query all of the info in PIP and I couldn't."); Console.WriteLine("Do you have a tab named 'PIP' in the config file? PS: no trailing spaces or hidden chars?"); Console.WriteLine("Do you have a header called 'TableName'? PS: no trailing spaces or hidden chars?"); System.Environment.Exit(0); } //begin the traversal of the excel file. Keeping the current and previous so we know when we encounter a new table. string[] current_row = new string[num_cols]; string[] previous_row = new string[num_cols]; // hashtable that hashes to a hashtable with values of string...to map the column name to its respective datatypes. Dictionary<string, Dictionary<string, string>> hash = new Dictionary<string, Dictionary<string, string>>(); Dictionary<string, Dictionary<string, string>> metas = new Dictionary<string, Dictionary<string, string>>(); // Print all table that are about to be dropped and ask "are you sure?" int count = 0; string previous = ""; string current = ""; while (config_data_reader.Read()) { current = config_data_reader[ConfigColumnNames.TableName.ToString()].ToString(); if (config_data_reader["DropTable"].ToString().Trim().ToUpper() == "TRUE" && current != previous) { Console.WriteLine("\t" + config_data_reader["TableName"].ToString()); count++; } previous = config_data_reader[ConfigColumnNames.TableName.ToString()].ToString(); } if (count > 0) { Console.WriteLine("Um, you are about to drop all of these tables....are you sure you know what you are doing? [y/n/idk]"); string response = Console.ReadLine().Trim().ToLower(); while (response != "y" && response != "n" && response != "idk") { Console.WriteLine("Invalid Response. I am expecting one of these: y, n, idk"); response = Console.ReadLine().Trim().ToLower(); } switch (response) { case "y": break; case "n": Console.WriteLine("Whew, that was close."); System.Environment.Exit(0); break; case "idk": Console.WriteLine("Well, then you probably shouldn't be doing this should you."); System.Environment.Exit(0); break; default: Console.WriteLine("Ummmm.....Sum Ting Wong. You should never see dis."); System.Environment.Exit(0); break; } config_data_reader.Close(); } // Start of the GUTS of PIP try { entire_excel_file = new OleDbCommand("select * from [PIP$] where TableName is not NULL", conn); config_data_reader = entire_excel_file.ExecuteReader(); } catch (Exception) { Console.WriteLine("I tried to query all of the info in PIP and I couldn't."); Console.WriteLine("Do you have a tab named 'PIP' in the config file? PS: no trailing spaces or hidden chars?"); Console.WriteLine("Do you have a header called 'TableName'? PS: no trailing spaces or hidden chars?"); System.Environment.Exit(0); } while (config_data_reader.Read()) { // this seems a bit retarded...because it is. I don't know now to cast this to some form of an array that I can keep a previous and current on. // someone who knows the wonderful world of .NET could probably do this some justice. foreach (ConfigColumnNames col in Enum.GetValues(typeof(ConfigColumnNames))) { string col_value = ""; try { col_value = config_data_reader[col.ToString()].ToString(); } catch (Exception) { Console.WriteLine("More than likely, this column '{0}' doesn't exist in PIP config...and PIP expects it to.", col.ToString()); Console.WriteLine("Otherwise, if you are 100% sure the column exists EXACTLY as you see it spelled in this error, check for leading/trailing spaces"); Console.WriteLine("Before and after the column name or any phantom character that don't get displayed...If you have tried all of this and it STILL fails"); Console.WriteLine("This might be worthy of a bug report."); System.Environment.Exit(0); } current_row[(int)col] = col_value; } if (current_row[(int)ConfigColumnNames.TableName] != previous_row[(int)ConfigColumnNames.TableName]) { #region "High Level Algo" //TODO: this is where we build the create table script & populate the columns in the ssis package with the correct data types. //This is also where we build the batchfile, using current_row as the list of parameters to obtain. //Console.WriteLine("{0} is not equal to {1}", current_row[1], previous_row[1]); // open the template package of currentrow[18] // update flatfile connection manager with flatfile=currentrow[0] // x = select * from [packagebot$] where TableName = currentrow[1] // iterate through the result set. // update flatfile columns // update SSIS object model columns // build create table script. execute create table script. // save and execute the package. // load template package so we have something to work off of. #endregion if (previous_row[(int)ConfigColumnNames.TemplateSSISPackage] != null) { hash.Add(previous_row[(int)ConfigColumnNames.FieldName], new Dictionary<string, string>() { {"SSIS", previous_row[(int)ConfigColumnNames.SSISFieldFormat]}, {"SQL", previous_row[(int)ConfigColumnNames.SqlFieldFormat]}, {"Default", previous_row[(int)ConfigColumnNames.ClientFieldFormat]}, {"rowdelim", ConvertHexToString(previous_row[(int)ConfigColumnNames.RowDelimiter])}, {"coldelim", ConvertHexToString(previous_row[(int)ConfigColumnNames.ColumnDelimiter])}, {"precision", previous_row[(int)ConfigColumnNames.Precision]}, {"scale", previous_row[(int)ConfigColumnNames.Scale]}, {"textqualified", previous_row[(int)ConfigColumnNames.Text_Qualifier]}, {"TruncationRowDisposition", previous_row[(int)ConfigColumnNames.TruncationRowDisposition]}, {"Description", previous_row[(int)ConfigColumnNames.Field_Comments]}}); //packagename = previous_row[27]; // this isn't the real index of package name....change it when you change the config file // this needs to be an option in the config file string connection_string = previous_row[(int)ConfigColumnNames.ConnectionString]; // this isn't the real value, need to change it. //"Data Source=10.1.0.110;User ID=app_etl;Password=clt2010!;Initial Catalog=" + previous_row[22] + ";Provider=NZOLEDB;Persist Security Info=True;"; bool execute = false; // check config file to see if we should drop this table bool drop = false; if (previous_row[(int)ConfigColumnNames.DropTable].ToUpper().Trim() == "TRUE") { drop = true; } // Drop Recreate Reload Table if (previous_row[(int)ConfigColumnNames.Drop_Recreate_Reload].ToUpper().Trim() == "TRUE") { string tablename = previous_row[(int)ConfigColumnNames.TableName]; string new_name = tablename + "_TEMP"; string cmd = "ALTER TABLE " + tablename + " RENAME TO " + new_name + ";"; string cmd2 = "INSERT INTO " + tablename + " (select * from " + new_name + ");"; StreamWriter sw = new StreamWriter(new_name + ".sql"); sw.WriteLine(cmd); sw.WriteLine("-- CREATE TABLE is called here...but I am reusing this script {0}", tablename + ".sql"); sw.WriteLine(cmd2); sw.Close(); // Execute the Command: this will be in a file with tablename + _TEMP.sql in the current directory OleDbConnection connection = new OleDbConnection(connection_string); connection.Open(); OleDbCommand command = new OleDbCommand(cmd, connection); // Rename Table Console.Write("Creating Temp Table {0}_TEMP...", tablename); try { result = command.ExecuteNonQuery(); if (result != -1) {//FIXME: Really you should query the number of records in the table, check to see it that matches result Console.WriteLine("\nThe world will come to and end. The DB should return -1 for this command and it returned {0}", result); Console.WriteLine("This was the statement '{0}'", command.CommandText); System.Environment.Exit(0); } } catch (Exception e) { Console.Write("Failed.\n"); Console.WriteLine("Could not alter table {0}", tablename); Console.WriteLine("Tried to execute this statement '{0}' --- Exception is below:", command.CommandText); Console.WriteLine(e.Message); System.Environment.Exit(0); } Console.Write("Done.\n"); // Create New Table string tf = previous_row[(int)ConfigColumnNames.TableFooter].ToString(); CreateSqlTableScript(hash, metas, tablename,connection_string, tf, conn, true, false); Console.Write("Inserting records into Table {0}...", tablename); // Insert Old Records into new table command = new OleDbCommand(cmd2, connection); try { result = command.ExecuteNonQuery(); if (result < 0) {//FIXME: Really you should query the number of records in the table, check to see it that matches result Console.WriteLine("\nBUG!! DB Returned {0}", result); Console.WriteLine("This was the statement '{0}'", command.CommandText); System.Environment.Exit(0); } } catch (Exception e) { Console.Write("\nFailed.\n"); Console.WriteLine("Could not insert records."); Console.WriteLine("Tried to execute this statement '{0}' --- Exception is below:", command.CommandText); Console.WriteLine(e.Message); System.Environment.Exit(0); } Console.Write("Done.\nInserted {0} Records\n", result); // Drop Temp Table Console.Write("Dropping Temp Table {0}...", tablename); try { command = new OleDbCommand("DROP TABLE " + tablename + "_TEMP;", connection); result = command.ExecuteNonQuery(); if (result != -1) {//FIXME: Really you should query the number of records in the table, check to see it that matches result Console.WriteLine("\nBUG!! DB Returned {0} Should have been -1", result); Console.WriteLine("This was the statement '{0}'", "DROP TABLE " + tablename + "_TEMP;"); System.Environment.Exit(0); } } catch (Exception e) { Console.Write("Failed.\n"); Console.WriteLine("Dropping {0}_TEMP Failed", tablename); Console.WriteLine("Tried to execute this statement '{0}' --- Exception is below:", "DROP TABLE " + tablename + "_TEMP;"); Console.WriteLine(e.Message); System.Environment.Exit(0); } Console.Write("Done.\n"); // Clean up connection.Close(); sw.Close(); } // check config file to see if we should execute this table if (previous_row[(int)ConfigColumnNames.CreateTable].ToUpper().Trim() == "TRUE") { execute = true; string tf = previous_row[(int)ConfigColumnNames.TableFooter]; CreateSqlTableScript(hash, metas, previous_row[(int)ConfigColumnNames.TableName], connection_string, tf, conn, execute, drop); } // modify the metadata structure if (previous_row[(int)ConfigColumnNames.UpdateMetadata].ToUpper().Trim() == "TRUE") { string msource_connection_string = previous_row[(int)ConfigColumnNames.MetaDataConnectionString]; // this insn't the right index...needs to change InsertMSourceEntity(msource_connection_string, previous_row); } // Build the package if (previous_row[(int)ConfigColumnNames.BuildPackage].ToUpper().Trim() == "TRUE") { //reset the flat file connection manager with settings in the excel file Console.Write("Building Package {0}.....", previous_row[(int)ConfigColumnNames.NewSSISPackage]); p = app.LoadPackage(previous_row[(int)ConfigColumnNames.TemplateSSISPackage], null); //GetDataFlowTask(p.Executables, p.Executables.GetEnumerator()); Microsoft.SqlServer.Dts.Runtime.Sequence seq = null; Microsoft.SqlServer.Dts.Runtime.TaskHost th = null; // looking for sequence container....this shit is very specific to package structure....I have a recursive function // That can handle this...but not in this code. try { seq = p.Executables["Sequence Container"] as Microsoft.SqlServer.Dts.Runtime.Sequence; } catch (Exception) { Console.WriteLine("Failed.\nCould not find the Sequence Container in the Package"); Console.WriteLine("the Sequence container MUST BE NAMED: 'Sequence Container'"); Console.WriteLine("Otherwise, you will always see this error message"); System.Environment.Exit(0); } // Looking for data flow task try { th = seq.Executables["Data Flow Task"] as Microsoft.SqlServer.Dts.Runtime.TaskHost; } catch (Exception) { Console.WriteLine("Failed.\nCould not find the Data Flow Task in the Package"); Console.WriteLine("the Sequence container MUST BE NAMED: 'Data Flow Task'"); Console.WriteLine("Otherwise, you will always see this error message"); System.Environment.Exit(0); } // Error Checking if (seq == null || th == null) { Console.WriteLine("Failed.\nSum Ting Wong. You should never see this error message"); System.Environment.Exit(0); } data_flow_task = th.InnerObject as MainPipe; try { cm_destination = p.Connections["destination"]; } catch (Exception) { Console.WriteLine("Failed\nERROR: your destination connection manager MUST BE NAMED: 'destination'"); System.Environment.Exit(0); } // uncomment for packages with source component, should use a try catch //cm_source = p.Connections["source"]; //p.PackagePassword = "******"; //p.Variables.Remove("filePathName"); //p.Variables.Add("filePathName", false,"User", previous_row[21]); cm_destination.ConnectionString = connection_string; string coldelim = ConvertHexToString(previous_row[(int)ConfigColumnNames.ColumnDelimiter]); string rowdelim = ConvertHexToString(previous_row[(int)ConfigColumnNames.RowDelimiter]); string ssis_var = "@[User::filePathName]"; // uncomment the flat file stuff when working with staging that deals with flatfiles ResetFlatFileConnectionManager(ssis_var, rowdelim, coldelim, ssis_var, previous_row[(int)ConfigColumnNames.Text_Qualifier], p.Connections); // reset the column properties in the FlatFile Source Component IDTSConnectionManagerFlatFile100 cmff = cm_flat_file.InnerObject as IDTSConnectionManagerFlatFile100; ResetFlatFileColumnProperties(cmff, hash); // modify columns; types; reinitialize source and destination components; remap columns string table = previous_row[(int)ConfigColumnNames.TableName].Trim().ToUpper(); ModifyComponents(hash, metas, table, table); Console.Write("{0}\n", p.Validate(p.Connections, p.Variables, null, null)); try { app.SaveToXml(previous_row[(int)ConfigColumnNames.NewSSISPackage], p, null); } catch (Exception) { Console.WriteLine("Failed.\nCould not save the package. Is your path name in the config correct? I have '{0}'", previous_row[(int)ConfigColumnNames.NewSSISPackage]); Console.WriteLine("Is this package '{0}' open somewhere else?", previous_row[(int)ConfigColumnNames.NewSSISPackage]); Console.WriteLine("Do you have the correct permissions?"); Console.WriteLine("Is there something else going on that would cause a lock?"); System.Environment.Exit(0); } } if (previous_row[(int)ConfigColumnNames.ExecutePackage].ToUpper().Trim() == "TRUE") { if (p == null) p = app.LoadPackage(previous_row[(int)ConfigColumnNames.NewSSISPackage], null); Console.Write("Executing Package {0}...", previous_row[(int)ConfigColumnNames.NewSSISPackage]); Console.WriteLine(p.Execute()); } hash = new Dictionary<string, Dictionary<string, string>>(); metas = new Dictionary<string, Dictionary<string, string>>(); } // END IF } // END IF else { hash.Add(previous_row[(int)ConfigColumnNames.FieldName], new Dictionary<string, string>() { {"SSIS", previous_row[(int)ConfigColumnNames.SSISFieldFormat]}, {"SQL", previous_row[(int)ConfigColumnNames.SqlFieldFormat]}, {"Default", previous_row[(int)ConfigColumnNames.ClientFieldFormat]}, {"rowdelim", ConvertHexToString(previous_row[(int)ConfigColumnNames.RowDelimiter])}, {"coldelim", ConvertHexToString(previous_row[(int)ConfigColumnNames.ColumnDelimiter])}, {"precision", previous_row[(int)ConfigColumnNames.Precision]}, {"scale", previous_row[(int)ConfigColumnNames.Scale]}, {"textqualified", previous_row[(int)ConfigColumnNames.Text_Qualifier]}, {"TruncationRowDisposition", previous_row[(int)ConfigColumnNames.TruncationRowDisposition]}, {"Description", previous_row[(int)ConfigColumnNames.Field_Comments]}}); } // copy the values in current row to previous row. for (int i = 0; i < num_cols; i++) { previous_row[i] = current_row[i]; } } // END While Console.WriteLine("Done."); Console.ReadLine(); }
static void Main(string[] args) { string testDTSX = "LargeExample.dtsx"; string path = Path.Combine(SSISFolder, testDTSX); // Traverse through every single EDS and save a snapshot of it //foreach (EDSSettings EDS in SettingsStore.EDSSettings.Where(e => e.Name != "Template")) //{ // MetaDataSnapshot metaDataSnapshot = new MetaDataSnapshot(EDS.ConnectionString); // Console.WriteLine(metaDataSnapshot); // MetaDataStore.Provider.SaveSnapshot(EDS, metaDataSnapshot); //} // Key = EDS connection string Dictionary <string, DatabaseMetaChange> latestChanges = MetaDataStore.GetLatestChanges(SettingsStore.EDSSettings); _databaseMetaChange = latestChanges.Values.First(); //TODO: change this later. // Print out changes Console.WriteLine(_databaseMetaChange); // Create a new application to host packages, and load a package within it Application application = new Application(); Package package = application.LoadPackage(path, null); Graph g = new Graph(application, package, testDTSX, new Options.Options()) { Options = new Options.Options() }; // Print attributes, which is used in graph List <Dictionary <string, Attribute> > list = g.AttributeTable.Values.SelectMany(d => d.Values).ToList(); List <Attribute> attributes = list.SelectMany(d => d.Values).ToList(); string s = string.Join("\n", attributes.Select(a => $" -{a.Id}/{a.AttributeRef?.ID}: {a.Name}")); Console.WriteLine(s); // Iterate through all meta changes foreach (TableMetaChange tableMetaChange in _databaseMetaChange.Tables.Values) { foreach (ColumnMetaChange columnMetaChange in tableMetaChange.Columns.Values.Where(c => c.ColumnChanges > 0)) { foreach (ColumnChanges change in columnMetaChange.ListChanges()) { g.Alter(columnMetaChange, change); } } } // Validate package before saving it package.Validate(package.Connections, package.Variables, new PackageValidateErrorEvent(), null); // Save package application.SaveToXml(Path.Combine(TestOutputFolder, "packages", testDTSX), package, null); Console.WriteLine("Done"); Console.ReadKey(); }