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();
}