public override void Emit(XPathNavigator patternNavigator)
{
string containerName = patternNavigator.SelectSingleNode("@Name", VulcanPackage.VulcanConfig.NamespaceManager).Value;
string helperTableTaskName = "Set Up Helper Table Group " + containerName;
string helperTableFile = containerName + Resources.ExtensionSQLFile;
string helperTableFilePath = VulcanPackage.AddFileToProject(helperTableFile);
File.Delete(helperTableFilePath);
Connection c = Connection.GetExistingConnection(VulcanPackage, patternNavigator);
foreach (XPathNavigator nav in patternNavigator.Select("rc:HelperTable", VulcanPackage.VulcanConfig.NamespaceManager))
{
string tableName = nav.SelectSingleNode("@Name", VulcanPackage.VulcanConfig.NamespaceManager).Value;
XPathNavigator tableNavigator = nav.SelectSingleNode("rc:Table", VulcanPackage.VulcanConfig.NamespaceManager);
Message.Trace(Severity.Debug, "Adding Helper table " + tableName);
TableHelper th = new TableHelper(tableName, VulcanPackage.VulcanConfig, tableNavigator);
TableEmitterEx te = new TableEmitterEx(th, VulcanPackage);
te.Emit(VulcanPackage.QualifiedProjectPath + helperTableFile, true);
}
SQLTask setupSQLTask = new SQLTask(VulcanPackage, helperTableTaskName, helperTableTaskName, ParentContainer, c);
setupSQLTask.TransmuteToFileTask(helperTableFile);
this.ExecuteDuringDesignTime(patternNavigator, setupSQLTask.SQLTaskHost);
this.FirstExecutableGeneratedByPattern = setupSQLTask.SQLTaskHost;
this.LastExecutableGeneratedByPattern = this.FirstExecutableGeneratedByPattern;
}
public override void Emit(XPathNavigator patternNavigator)
{
if (patternNavigator != null)
{
string name = patternNavigator.SelectSingleNode("@Name").Value;
string fileName =
Resources.Create +
name +
Resources.ExtensionSQLFile;
string filePath = VulcanPackage.AddFileToProject(fileName);
TableHelper th = new TableHelper(name, VulcanPackage.VulcanConfig, patternNavigator.SelectSingleNode("rc:Table", VulcanPackage.VulcanConfig.NamespaceManager));
th.TraceHelper();
th.TraceHelper();
TableEmitterEx tex = new TableEmitterEx(th, VulcanPackage);
tex.Emit(filePath, false);
Connection connection = Connection.GetExistingConnection(VulcanPackage, patternNavigator);
SQLTask sqlTask = new SQLTask(VulcanPackage,
Resources.Create + name,
Resources.Create + name,
ParentContainer,
connection
);
sqlTask.TransmuteToFileTask(fileName);
this.FirstExecutableGeneratedByPattern = sqlTask.SQLTaskHost;
this.LastExecutableGeneratedByPattern = this.FirstExecutableGeneratedByPattern;
}
}
public override void Emit(XPathNavigator patternNavigator)
{
if (patternNavigator != null)
{
string firstOrLast = patternNavigator.SelectSingleNode("@FirstOrLast").Value;
string status = patternNavigator.SelectSingleNode("@Status").Value;
string notes = patternNavigator.SelectSingleNode("@Notes").Value;
Connection tableConnection =
Connection.GetExistingConnection(VulcanPackage, LogtainerPattern.CurrentLog.TableConnectionName);
string execSqlTaskName = LogtainerPattern.CurrentLog.LogName + Resources.Seperator + firstOrLast + Guid.NewGuid();
TemplateEmitter te = new TemplateEmitter(VulcanPackage.TemplateManager["LogSelectQuery"]);
if (
LogtainerPattern.CurrentLog.SourceColumn == null ||
LogtainerPattern.CurrentLog.DestinationColumn == null ||
LogtainerPattern.CurrentLog.TableConnectionName == null ||
LogtainerPattern.CurrentLog.Table == null)
{
Message.Trace(Severity.Error,
"Could not perform LogUpdate (On Log: {0}), Parent Logtainer does not contain all of the necessary information. Needs SourceColumn, DestinationColumn, TableConnectionName, and Table attributes.", LogtainerPattern.CurrentLog.LogName);
return;
}
te.SetNamedParameter("Source", LogtainerPattern.CurrentLog.SourceColumn);
te.SetNamedParameter("Destination", LogtainerPattern.CurrentLog.DestinationColumn);
te.SetNamedParameter("Table", LogtainerPattern.CurrentLog.Table);
te.SetNamedParameter("Status", status);
te.SetNamedParameter("Notes", notes);
te.SetNamedParameter("SourceConvertStyle", "21");
te.SetNamedParameter("DestinationConvertStyle", "21");
string query;
te.Emit(out query);
SQLTask readForLogTask = new SQLTask(VulcanPackage, execSqlTaskName, execSqlTaskName, ParentContainer, tableConnection);
readForLogTask.TransmuteToExpressionTask(String.Format("\"{0}\"", query));
readForLogTask.ExecuteSQLTask.ResultSetType = Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ResultSetType.ResultSetType_SingleRow;
DTS.Variable sourceVar = LogtainerPattern.CurrentLog[firstOrLast + "SourceRecord"];
DTS.Variable destVar = LogtainerPattern.CurrentLog[firstOrLast + "DestinationRecord"];
DTS.Variable statusVar = LogtainerPattern.CurrentLog["Status"];
DTS.Variable notesVar = LogtainerPattern.CurrentLog["Notes"];
readForLogTask.BindResult("0", sourceVar.QualifiedName);
readForLogTask.BindResult("1", destVar.QualifiedName);
readForLogTask.BindResult("2", statusVar.QualifiedName);
readForLogTask.BindResult("3", notesVar.QualifiedName);
this.FirstExecutableGeneratedByPattern = readForLogTask.SQLTaskHost;
this.LastExecutableGeneratedByPattern = this.FirstExecutableGeneratedByPattern;
}
}
public AdClient()
{
logger = new Logger(logFilePath + "[MAIN]" + DateTime.Now.ToString("yyyyMMdd") + ".log");
SQLTaskParameter stp = new SQLTaskParameter(sql_conn);
AutomationTaskParameter atpSQL = new AutomationTaskParameter();
atpSQL.ClientID = clientID;
atpSQL.AcknowledgeURL = "http://localhost/testAD/testAckDie.aspx";
atpSQL.Name = "[SQL]";
atpSQL.CommandURL = url2Sql;
atpSQL.IsRepeat = true;
atpSQL.PollInterval = pollIntervalSQL;
atpSQL.Logger = logger;
atpSQL.Observer = this;
taskSQL = new SQLTask(atpSQL, stp);
}
public override void Emit(XPathNavigator patternNavigator)
{
if (patternNavigator != null)
{
string name = patternNavigator.SelectSingleNode("@Name").Value;
string body = patternNavigator.SelectSingleNode("rc:Body", VulcanPackage.VulcanConfig.NamespaceManager).Value;
string fileName =
Resources.Create +
name +
Resources.ExtensionSQLFile;
string filePath = VulcanPackage.AddFileToProject(fileName);
StoredProcEmitter spe = new StoredProcEmitter(name, body, VulcanPackage);
foreach (XPathNavigator columnNav in patternNavigator.Select("rc:Columns/rc:Column", VulcanPackage.VulcanConfig.NamespaceManager))
{
bool isOutput = columnNav.SelectSingleNode("@IsOutput").ValueAsBoolean;
spe.AddColumn(
columnNav.SelectSingleNode("@Name").Value,
columnNav.SelectSingleNode("@Default").Value,
columnNav.SelectSingleNode("@Type").Value,
isOutput);
}
spe.Emit(filePath, false);
Connection c = Connection.GetExistingConnection(VulcanPackage, patternNavigator);
SQLTask sqlTask = new SQLTask(VulcanPackage,
Resources.Create + name,
Resources.Create + name,
ParentContainer,
c
);
sqlTask.TransmuteToFileTask(fileName);
this.ExecuteDuringDesignTime(patternNavigator, sqlTask.SQLTaskHost);
this.FirstExecutableGeneratedByPattern = sqlTask.SQLTaskHost;
this.LastExecutableGeneratedByPattern = this.FirstExecutableGeneratedByPattern;
}
}
public override void Emit(XPathNavigator patternNavigator)
{
if (patternNavigator != null)
{
string taskName = patternNavigator.SelectSingleNode("@Name").Value;
string connectionName = patternNavigator.SelectSingleNode("@Connection").Value;
string varPrefix = patternNavigator.SelectSingleNode("@PreviousLogEntryVariablePrefix").Value;
string sourceColumn = patternNavigator.SelectSingleNode("@SourceColumn") == null ? null : patternNavigator.SelectSingleNode("@SourceColumn").Value;
string destinationColumn = patternNavigator.SelectSingleNode("@DestinationColumn") == null ? null : patternNavigator.SelectSingleNode("@DestinationColumn").Value;
string table = patternNavigator.SelectSingleNode("@Table") == null ? null : patternNavigator.SelectSingleNode("@Table").Value;
string tableConnectionName = patternNavigator.SelectSingleNode("@TableConnection") == null ? null : patternNavigator.SelectSingleNode("@TableConnection").Value;
if (varPrefix.ToUpperInvariant() == "varLog")
{
Message.Trace(Severity.Error, "Name: {0}: Error in PreviousLogEntryVariablePrefix: varLog is a reserved variable name.", taskName);
return;
}
Connection connection =
Connection.GetExistingConnection(VulcanPackage, connectionName);
LogtainerLog ll = new LogtainerLog(
VulcanPackage,
Resources.LogVariablePrefix + VulcanPackage.Name + taskName,
sourceColumn,
destinationColumn,
table,
tableConnectionName
);
string execSqlTaskName = Resources.Log + Resources.Seperator + VulcanPackage.Name + Resources.Seperator + taskName + Resources.Seperator;
SQLTask logStartTask = new SQLTask(VulcanPackage, execSqlTaskName + Resources.Start, execSqlTaskName + Resources.Start, ParentContainer, connection);
TemplateEmitter te = new TemplateEmitter(VulcanPackage.TemplateManager["LogStart"]);
te.SetNamedParameter("ETLName", VulcanPackage.Name);
te.SetNamedParameter("TaskName", taskName);
if (_logStack.Count > 0)
{
te.SetNamedParameter("ParentLogID", "?");
// Yes you have to hard code the data type as there is no lookup or cross-reference source, and the intger is dependent on the connection you create... bugbug!
logStartTask.BindParameter(CurrentLog.LogVariable, Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Input, 3);
}
else
{
te.SetNamedParameter("ParentLogID", "NULL");
}
logStartTask.BindParameter(ll.LogVariable, Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output, 3);
logStartTask.BindParameter(ll.LastRecordLogVariable, Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output, 3);
logStartTask.BindParameter(ll.IsAnotherInstanceCurrentlyRunningLogVariable, Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output, 3);
string sqlExpression;
te.Emit(out sqlExpression);
logStartTask.TransmuteToExpressionTask(sqlExpression);
te = new TemplateEmitter(VulcanPackage.TemplateManager["LogGetValue"]);
te.SetNamedParameter("LogID", ll.LastRecordLogVariable.QualifiedName);
this.FirstExecutableGeneratedByPattern = logStartTask.SQLTaskHost;
// Push the current log variable onto the stack!
_logStack.Push(ll);
//Add a new SQL Task to read the previous log
te = new TemplateEmitter("LogGetValue", VulcanPackage, ll.LastRecordLogVariable.QualifiedName);
string readPreviousLogValuesQuery;
te.Emit(out readPreviousLogValuesQuery);
SQLTask readPreviousLogValues = new SQLTask(VulcanPackage, Resources.Log + Resources.LoadInto + varPrefix, Resources.Log + Resources.LoadInto + varPrefix, ParentContainer, connection);
readPreviousLogValues.TransmuteToExpressionTask(readPreviousLogValuesQuery);
VulcanPackage.AddPrecedenceConstraint(this.FirstExecutableGeneratedByPattern, readPreviousLogValues.SQLTaskHost, ParentContainer);
//Bind and create variables for the SQL Task that reads the previous log. This is kinda hacky
readPreviousLogValues.BindParameter(
VulcanPackage.AddVariable(varPrefix + "StartTime", new DateTime(1980, 1, 1)),
Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output,
(int)System.Data.OleDb.OleDbType.DBTimeStamp,
255);
readPreviousLogValues.BindParameter(
VulcanPackage.AddVariable(varPrefix + "EndTime", new DateTime(1980, 1, 1)),
Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output,
(int)System.Data.OleDb.OleDbType.DBTimeStamp,
255);
readPreviousLogValues.BindParameter(
VulcanPackage.AddVariable(varPrefix + "FirstSourceRecord", String.Empty),
Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output,
(int)System.Data.OleDb.OleDbType.WChar,
255);
readPreviousLogValues.BindParameter(
VulcanPackage.AddVariable(varPrefix + "FirstDestinationRecord", String.Empty),
Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output,
(int)System.Data.OleDb.OleDbType.WChar,
255);
readPreviousLogValues.BindParameter(
VulcanPackage.AddVariable(varPrefix + "LastSourceRecord", String.Empty),
Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output,
(int)System.Data.OleDb.OleDbType.WChar,
255);
readPreviousLogValues.BindParameter(
VulcanPackage.AddVariable(varPrefix + "LastDestinationRecord", String.Empty),
Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output,
(int)System.Data.OleDb.OleDbType.WChar,
255);
readPreviousLogValues.BindParameter(
VulcanPackage.AddVariable(varPrefix + "Status", String.Empty),
Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output,
(int)System.Data.OleDb.OleDbType.WChar,
255);
readPreviousLogValues.BindParameter(
VulcanPackage.AddVariable(varPrefix + "Notes", String.Empty),
Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output,
(int)System.Data.OleDb.OleDbType.WChar,
255);
// Handle the sub-tasks just like the Container pattern
DTS.Executable previousExec = readPreviousLogValues.SQLTaskHost;
Pattern p = null;
foreach (XPathNavigator nav in patternNavigator.SelectChildren(XPathNodeType.Element))
{
p = PatternFactory.ProcessPattern(VulcanPackage, ParentContainer, nav, p);
VulcanPackage.AddPrecedenceConstraint(previousExec, p.FirstExecutableGeneratedByPattern, ParentContainer);
previousExec = p.LastExecutableGeneratedByPattern;
}
this.LastExecutableGeneratedByPattern = previousExec;
// Pop the current log variable off of the stack and end the log.
ll = _logStack.Pop();
te = new TemplateEmitter(VulcanPackage.TemplateManager["LogSetValue"]);
te.SetNamedParameter("LogID", ll.LogVariable.QualifiedName);
StringBuilder writeValuesToLogQuery = new StringBuilder();
foreach (string column in ll.LogColumnDictionary.Keys)
{
string temp;
te.SetNamedParameter("Column", column);
te.SetNamedParameter("Value", ll.LogColumnDictionary[column].QualifiedName);
te.Emit(out temp);
writeValuesToLogQuery.Append(temp);
writeValuesToLogQuery.AppendFormat(" + \n");
}
te = new TemplateEmitter(VulcanPackage.TemplateManager["LogEnd"]);
te.Emit(out sqlExpression);
writeValuesToLogQuery.Append(sqlExpression);
SQLTask logEndTask = new SQLTask(VulcanPackage, execSqlTaskName + Resources.Stop, execSqlTaskName + Resources.Stop, ParentContainer, connection);
logEndTask.TransmuteToExpressionTask(writeValuesToLogQuery.ToString());
logEndTask.BindParameter(ll.LogVariable, Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Input, 3);
if (previousExec != null)
{
VulcanPackage.AddPrecedenceConstraint(previousExec, logEndTask.SQLTaskHost, ParentContainer);
}
this.LastExecutableGeneratedByPattern = logEndTask.SQLTaskHost;
}
}
public override void Emit(XPathNavigator patternNavigator)
{
if (patternNavigator != null)
{
string createTableFile = VulcanPackage.AddFileToProject(
Resources.Create + Resources.Schema + Resources.Seperator +
VulcanPackage.Name +
Resources.ExtensionSQLFile
);
File.Delete(createTableFile);
string insertSPFile = null;
string checkAndinsertSPFile = null;
string insertAndUpdateSPFile = null;
bool hasCheckAndInsert = false;
bool hasInsertAndUpdate = false;
foreach (XPathNavigator nav in patternNavigator.Select("rc:Dimension", VulcanPackage.VulcanConfig.NamespaceManager))
{
string dimensionName = nav.SelectSingleNode("@Name", VulcanPackage.VulcanConfig.NamespaceManager).Value;
Message.Trace(Severity.Debug, "Emitting Create Statement for " + dimensionName);
TableHelper th = new TableHelper(dimensionName, VulcanPackage.VulcanConfig, nav.SelectSingleNode("rc:Table", VulcanPackage.VulcanConfig.NamespaceManager));
TableEmitterEx te = new TableEmitterEx(th, VulcanPackage);
te.Emit(createTableFile, true);
if (String.IsNullOrEmpty(insertSPFile))
{
insertSPFile = VulcanPackage.AddFileToProject(
Resources.SPPrefix + Resources.Seperator +
Resources.Insert +
VulcanPackage.Name +
Resources.ExtensionSQLFile
);
File.Delete(insertSPFile);
}
InsertSPEmitter spe = new InsertSPEmitter(dimensionName, nav.SelectSingleNode("rc:Table", VulcanPackage.VulcanConfig.NamespaceManager), th, VulcanPackage);
spe.Emit(insertSPFile, true);
if (nav.Select("rc:Table/rc:CheckAndInsertUniqueColumn", VulcanPackage.VulcanConfig.NamespaceManager).Count > 0)
{
hasCheckAndInsert = true;
if (String.IsNullOrEmpty(checkAndinsertSPFile))
{
checkAndinsertSPFile = VulcanPackage.AddFileToProject(
Resources.SPPrefix + Resources.Seperator +
Resources.CheckAndInsert +
VulcanPackage.Name +
Resources.ExtensionSQLFile
);
File.Delete(checkAndinsertSPFile);
}
CheckAndInsertSPEmitter cispe = new CheckAndInsertSPEmitter(dimensionName, nav.SelectSingleNode("rc:Table", VulcanPackage.VulcanConfig.NamespaceManager), th, VulcanPackage);
cispe.Emit(checkAndinsertSPFile, true);
}
if (nav.Select("rc:Table/rc:InsertOrUpdateUniqueColumn", VulcanPackage.VulcanConfig.NamespaceManager).Count > 0)
{
hasInsertAndUpdate = true;
if (String.IsNullOrEmpty(insertAndUpdateSPFile))
{
insertAndUpdateSPFile = VulcanPackage.AddFileToProject(
Resources.SPPrefix + Resources.Seperator +
Resources.CheckAndInsert +
Resources.Update +
VulcanPackage.Name +
Resources.ExtensionSQLFile
);
File.Delete(insertAndUpdateSPFile);
}
InsertAndUpdateSPEmitter iuspe = new InsertAndUpdateSPEmitter(dimensionName, nav.SelectSingleNode("rc:Table", VulcanPackage.VulcanConfig.NamespaceManager), th, VulcanPackage);
iuspe.Emit(insertAndUpdateSPFile, true);
}
}
Dictionary <string, object> properties = new Dictionary <string, object>();
properties["Name"] = Resources.Create + Resources.Schema + Resources.Seperator + VulcanPackage.Name;
//TODO: This is a hardcoded bug, should be removed when Dimensions are rewritten!
Connection dimensionConnection = Connection.GetExistingConnection(VulcanPackage, patternNavigator);
SQLTask createTableTask =
new SQLTask(
VulcanPackage,
properties["Name"].ToString(),
properties["Name"].ToString(),
ParentContainer,
dimensionConnection,
properties
);
createTableTask.TransmuteToFileTask(properties["Name"] + Resources.ExtensionSQLFile);
properties["Name"] = Resources.SPPrefix + Resources.Seperator +
Resources.Insert +
VulcanPackage.Name;
this.FirstExecutableGeneratedByPattern = createTableTask.SQLTaskHost;
this.LastExecutableGeneratedByPattern = this.FirstExecutableGeneratedByPattern;
SQLTask createInsertSPTask =
new SQLTask(
VulcanPackage,
properties["Name"].ToString(),
properties["Name"].ToString(),
ParentContainer,
dimensionConnection,
properties
);
createInsertSPTask.TransmuteToFileTask(properties["Name"] + Resources.ExtensionSQLFile);
properties["Name"] = Resources.SPPrefix + Resources.Seperator +
Resources.CheckAndInsert +
VulcanPackage.Name;
properties["Description"] = properties["Name"];
VulcanPackage.AddPrecedenceConstraint(LastExecutableGeneratedByPattern, createInsertSPTask.SQLTaskHost, ParentContainer);
this.LastExecutableGeneratedByPattern = createInsertSPTask.SQLTaskHost;
if (hasCheckAndInsert)
{
SQLTask createCheckInsertSPTask =
new SQLTask(
VulcanPackage,
properties["Name"].ToString(),
properties["Name"].ToString(),
ParentContainer,
dimensionConnection,
properties
);
createCheckInsertSPTask.TransmuteToFileTask(properties["Name"] + Resources.ExtensionSQLFile);
properties["Name"] = Resources.SPPrefix + Resources.Seperator +
Resources.Insert + Resources.Defaults +
VulcanPackage.Name;
properties["Description"] = properties["Name"];
VulcanPackage.AddPrecedenceConstraint(createInsertSPTask.SQLTaskHost, createCheckInsertSPTask.SQLTaskHost, ParentContainer);
this.LastExecutableGeneratedByPattern = createCheckInsertSPTask.SQLTaskHost;
} // hasCheckAndInsert
if (hasInsertAndUpdate)
{
properties["Name"] = properties["Name"] = Resources.SPPrefix + Resources.Seperator +
Resources.CheckAndInsert + Resources.Update +
VulcanPackage.Name;
properties["Description"] = properties["Name"];
SQLTask createInsertUpdateTask =
new SQLTask(
VulcanPackage,
properties["Name"].ToString(),
properties["Name"].ToString(),
ParentContainer,
dimensionConnection,
properties
);
createInsertUpdateTask.TransmuteToFileTask(properties["Name"] + Resources.ExtensionSQLFile);
VulcanPackage.AddPrecedenceConstraint(LastExecutableGeneratedByPattern, createInsertUpdateTask.SQLTaskHost, ParentContainer);
this.LastExecutableGeneratedByPattern = createInsertUpdateTask.SQLTaskHost;
} // hasInsertAndUpdate
}
}
/// <summary>
/// Runs the SQL Statements
/// </summary>
static async Task Main(string[] args)
{
try
{
DateTime minStatisticDate = GetMinStatisticDate();
string optomizationLevel = "(none)";
foreach (string dwuScale in DWUsToTestList)
{
// Get the number of DWUs SQL DW is running
string DWUs = GetDWUs();
if (DWUs != dwuScale)
{
// Is the database at the corect DWUs? If not then let's scale it
ScaleDatabase(DWUs, dwuScale);
DWUs = GetDWUs();
}
// Replicate all the tables marked for replication and wait until they are all replicated.
// If you need to alter the stored procedures to adjust which tables to test (e.g. WHERE table_name IS NOT IN ('skip-me-table')
ReplicateTables();
// Configuration Runs (these will all run in a loop)
List <ExecutionRun> executionRuns = new List <ExecutionRun>();
executionRuns.Clear();
// **** CHANGE ME ****
// Create your execution runs
#region Execution Runs
//////////////////////////////////////////////////////////////////
// Serial
//////////////////////////////////////////////////////////////////
// Run Serial V1 on small, medium and large resource classes
executionRuns.Add(new ExecutionRun()
{
ReplicatedTables = true,
ConnectionString = SQL_CONNECTION_SMALL,
DWU = DWUs,
Enabled = true,
Interations = 5,
Mode = SerialOrConcurrentEnum.Serial,
OptLevel = optomizationLevel,
ResourceClass = "smallrc",
ResultSetCaching = false,
ScriptPath = @"..\..\..\..\Sample-Serial-SQL-v1"
});
executionRuns.Add(new ExecutionRun()
{
ReplicatedTables = true,
ConnectionString = SQL_CONNECTION_MEDIUM,
DWU = DWUs,
Enabled = true,
Interations = 5,
Mode = SerialOrConcurrentEnum.Serial,
OptLevel = optomizationLevel,
ResourceClass = "mediumrc",
ResultSetCaching = false,
ScriptPath = @"..\..\..\..\Sample-Serial-SQL-v1"
});
executionRuns.Add(new ExecutionRun()
{
ReplicatedTables = true,
ConnectionString = SQL_CONNECTION_LARGE,
DWU = DWUs,
Enabled = true,
Interations = 5,
Mode = SerialOrConcurrentEnum.Serial,
OptLevel = optomizationLevel,
ResourceClass = "largerc",
ResultSetCaching = false,
ScriptPath = @"..\..\..\..\Sample-Serial-SQL-v1"
});
// Run Serial V2 on medium and large resource classes
executionRuns.Add(new ExecutionRun()
{
ReplicatedTables = true,
ConnectionString = SQL_CONNECTION_SMALL,
DWU = DWUs,
Enabled = true,
Interations = 5,
Mode = SerialOrConcurrentEnum.Serial,
OptLevel = optomizationLevel,
ResourceClass = "smallrc",
ResultSetCaching = false,
ScriptPath = @"..\..\..\..\Sample-Serial-SQL-v2"
});
executionRuns.Add(new ExecutionRun()
{
ReplicatedTables = true,
ConnectionString = SQL_CONNECTION_MEDIUM,
DWU = DWUs,
Enabled = true,
Interations = 5,
Mode = SerialOrConcurrentEnum.Serial,
OptLevel = optomizationLevel,
ResourceClass = "mediumrc",
ResultSetCaching = false,
ScriptPath = @"..\..\..\..\Sample-Serial-SQL-v2"
});
executionRuns.Add(new ExecutionRun()
{
ReplicatedTables = true,
ConnectionString = SQL_CONNECTION_LARGE,
DWU = DWUs,
Enabled = true,
Interations = 5,
Mode = SerialOrConcurrentEnum.Serial,
OptLevel = optomizationLevel,
ResourceClass = "largerc",
ResultSetCaching = false,
ScriptPath = @"..\..\..\..\Sample-Serial-SQL-v2"
});
//////////////////////////////////////////////////////////////////
// Concurrency
//////////////////////////////////////////////////////////////////
// Run Concurrency V1 on medium and large resource classes
executionRuns.Add(new ExecutionRun()
{
ReplicatedTables = true,
ConnectionString = SQL_CONNECTION_MEDIUM,
DWU = DWUs,
Enabled = true,
Interations = 2,
Mode = SerialOrConcurrentEnum.Concurrent,
OptLevel = optomizationLevel,
ResourceClass = "mediumrc",
ScriptPath = @"..\..\..\..\Sample-Concurrency-SQL-v1",
ResultSetCaching = false,
});
executionRuns.Add(new ExecutionRun()
{
ReplicatedTables = true,
ConnectionString = SQL_CONNECTION_LARGE,
DWU = DWUs,
Enabled = true,
Interations = 2,
Mode = SerialOrConcurrentEnum.Concurrent,
OptLevel = optomizationLevel,
ResourceClass = "largerc",
ResultSetCaching = false,
ScriptPath = @"..\..\..\..\Sample-Concurrency-SQL-v1"
});
// Run Concurrency V2 on medium and large resource classes
executionRuns.Add(new ExecutionRun()
{
ReplicatedTables = true,
ConnectionString = SQL_CONNECTION_MEDIUM,
DWU = DWUs,
Enabled = true,
Interations = 2,
Mode = SerialOrConcurrentEnum.Concurrent,
OptLevel = optomizationLevel,
ResourceClass = "mediumrc",
ResultSetCaching = false,
ScriptPath = @"..\..\..\..\Sample-Concurrency-SQL-v2"
});
executionRuns.Add(new ExecutionRun()
{
ReplicatedTables = true,
ConnectionString = SQL_CONNECTION_LARGE,
DWU = DWUs,
Enabled = true,
Interations = 2,
Mode = SerialOrConcurrentEnum.Concurrent,
OptLevel = optomizationLevel,
ResourceClass = "largerc",
ResultSetCaching = false,
ScriptPath = @"..\..\..\..\Sample-Concurrency-SQL-v2"
});
//////////////////////////////////////////////////////////////////
// Concurrency WITH ResultSetCaching
//////////////////////////////////////////////////////////////////
executionRuns.Add(new ExecutionRun()
{
ReplicatedTables = true,
ConnectionString = SQL_CONNECTION_SMALL,
DWU = DWUs,
Enabled = true,
Interations = 4,
Mode = SerialOrConcurrentEnum.Concurrent,
OptLevel = optomizationLevel,
ResourceClass = "smallrc",
ResultSetCaching = true,
ScriptPath = @"..\..\..\..\Sample-Concurrency-SQL-v1"
});
#endregion // Execution Runs
// Read all the SQL Statements and do any necessary string replacements on the SQL
foreach (ExecutionRun executionRun in executionRuns)
{
Console.WriteLine("Starting Execution Run: " + executionRun.ScriptPath);
if (executionRun.Enabled == false)
{
Console.WriteLine("Skipping Execution Run: " + executionRun.ScriptPath);
continue;
}
bool resultSetCachingStatus = GetResultSetCachingStatus();
if (resultSetCachingStatus != executionRun.ResultSetCaching)
{
SetResultSetCachingStatus(executionRun.ResultSetCaching);
}
// local variables
List <string> sessionIds = new List <string>();
sessionIds.Clear();
List <SQLTask> sqlScripts = new List <SQLTask>();
sqlScripts.Clear();
string rawSQL = null;
string fileName = "";
int counter = 1;
DateTime startDate = DateTime.UtcNow;
DateTime endDate = DateTime.UtcNow;
startDate = DateTime.UtcNow;
// Prepare the scripts for execution
// We want to do a search and replace on certain items since we might need to change table names or resource classes.
for (int i = 1; i <= executionRun.Interations; i++)
{
foreach (var item in System.IO.Directory.GetFiles(executionRun.ScriptPath))
{
SQLTask sqlTask = new SQLTask();
rawSQL = System.IO.File.ReadAllText(item);
fileName = item.Substring(item.LastIndexOf("\\") + 1).Replace(".sql", string.Empty);
// **** CHANGE ME (OVERRIDES PER CUSTOMER) ****
// OVERRIDES: To handle multiple CTASs (append the loop variable)
// What this will do is find "my_ctas_table" and replace it with "my_ctas_table_1" so if we have 2 loops we will get a _1 and _2 table so we do not have collections
rawSQL = rawSQL.Replace("my_ctas_table", "my_ctas_table_" + i.ToString());
sqlTask.ConnectionString = executionRun.ConnectionString;
sqlTask.ScriptName = fileName;
sqlTask.Label = fileName + " :: " + i.ToString() + " :: " + counter.ToString();
sqlTask.SQL = rawSQL;
// **** CHANGE ME (OVERRIDES PER CUSTOMER) ****
// OVERRIDES: Change certain scripts to run in different classes
if (sqlTask.ScriptName.ToUpper() == "????")
{
// Do whatever you want in here
sqlTask.ConnectionString = SQL_CONNECTION_LARGE;
}
sqlScripts.Add(sqlTask);
counter++;
} // foreach
} // i
if (executionRun.Mode == SerialOrConcurrentEnum.Concurrent)
{
// CONCURRENT
List <Task <string> > executeSQLTask = new List <Task <string> >();
executeSQLTask.Clear();
startDate = DateTime.UtcNow;
foreach (SQLTask sqlTask in sqlScripts)
{
executeSQLTask.Add(Task <string> .Run(() => ExecuteSQL(sqlTask.Label, sqlTask.SQL, sqlTask.ConnectionString)));
}
// Wait for everything to finish
await Task.WhenAll(executeSQLTask);
endDate = DateTime.UtcNow;
// Copy SessionIds
foreach (var item in executeSQLTask)
{
sessionIds.Add(item.Result);
if (item.Result != null)
{
Console.WriteLine("DONE: " + (item.Result ?? "NULL"));
}
else
{
Console.WriteLine("*********** ERROR ABORT TEST: SESSION ID IS NULL ***********");
return;
}
}
}
else
{
// SERIAL
startDate = DateTime.UtcNow;
foreach (SQLTask sqlTask in sqlScripts)
{
Task <string> serialSQL = Task <string> .Run(() => ExecuteSQL(sqlTask.Label, sqlTask.SQL, sqlTask.ConnectionString));
serialSQL.Wait();
sessionIds.Add(serialSQL.Result);
if (serialSQL.Result != null)
{
Console.WriteLine("DONE: " + (serialSQL.Result ?? "NULL"));
}
else
{
Console.WriteLine("***********ERROR ABORT TEST: SESSION ID IS NULL -- SQL: " + sqlTask.Label + " ***********");
return;
}
}
endDate = DateTime.UtcNow;
} // serialOrConcurrentMode == SerialOrConcurrentEnum.Concurrent
// Save the data to the Automated Test Tables
try
{
using (SqlConnection connection = new SqlConnection(SQL_CONNECTION_SMALL))
{
connection.Open();
using (SqlCommand command = new SqlCommand("SELECT 1 + ISNULL(MAX(AutomatedTestId), 0) FROM telemetry.AutomatedTest", connection))
{
string AutomatedTestId = command.ExecuteScalar().ToString();
command.CommandText = "INSERT telemetry.AutomatedTest(" +
"[AutomatedTestId]," +
"[ScriptMods]," +
"[Mode]," +
"[DWU]," +
"[ReplicatedTables]," +
"[Interations]," +
"[MinStatisticDate]," +
"[ResultSetCaching]," +
"[ResourceClass]," +
"[OptLevel]," +
"[StartTime]," +
"[EndTime]) VALUES (" +
AutomatedTestId.ToString() + "," +
"'" + executionRun.ScriptPath.Substring(executionRun.ScriptPath.LastIndexOf("\\") + 1) + "'," +
"'" + executionRun.Mode.ToString() + "'," +
"'" + DWUs + "', " +
(executionRun.ReplicatedTables ? "1" : "0") + "," +
executionRun.Interations.ToString() + "," +
"'" + minStatisticDate.ToString("yyyy-MM-dd HH:mm:ss.fff") + "'," +
(executionRun.ResultSetCaching ? "1" : "0") + "," +
"'" + executionRun.ResourceClass + "'," +
"'" + executionRun.OptLevel + "'," +
"'" + startDate.ToString("yyyy-MM-dd HH:mm:ss.fff") + "'," +
"'" + endDate.ToString("yyyy-MM-dd HH:mm:ss.fff") + "');";
command.ExecuteNonQuery();
foreach (var sessionId in sessionIds)
{
command.CommandText = "INSERT INTO telemetry.AutomatedTestSession (AutomatedTestId,session_id) " +
"VALUES (" + AutomatedTestId.ToString() + ",'" + sessionId + " ');";
command.ExecuteNonQuery();
Console.WriteLine("Saving Data: " + sessionId);
} // sessionId
// Capture the exec_requests since we can loose them when the server is paused
command.CommandText = "INSERT INTO [telemetry].[AutomatedTest_exec_requests] " +
"([request_id], [session_id], [status], [submit_time], [start_time], [end_compile_time], [end_time], [total_elapsed_time], " +
"[label], [error_id], [database_id], [command], [resource_class], [importance], [group_name], [classifier_name], " +
"[resource_allocation_percentage], [result_cache_hit]) " +
"SELECT [request_id], [session_id], [status], [submit_time], [start_time], [end_compile_time], [end_time], [total_elapsed_time], " +
"[label], [error_id], [database_id], [command], [resource_class], [importance], [group_name], [classifier_name], " +
"[resource_allocation_percentage], [result_cache_hit] " +
"FROM sys.dm_pdw_exec_requests " +
"WHERE session_id IN (SELECT session_id FROM [telemetry].[AutomatedTestSession]) " +
"AND session_id NOT IN (SELECT session_id FROM [telemetry].[AutomatedTest_exec_requests]);";
command.ExecuteNonQuery();
Console.WriteLine("Captured sys.dm_pdw_exec_requests");
} // SqlCommand command
}
}
catch (SqlException e)
{
Console.WriteLine("*********** ERROR: " + e.ToString());
Console.ReadKey();
}
Console.WriteLine("Completed Execution Run: " + executionRun.ScriptPath);
} // foreach Execution run
} // (string dwuScale in DWUsToTestList)
}
catch (Exception mainException)
{
Console.WriteLine("*********** ERROR: " + mainException.ToString());
Console.ReadKey();
}
Console.WriteLine("Program Complete!");
} // Main
private List <Zombie> GetZombies()
{
List <Zombie> returnList = new List <Zombie>();
XmlNodeList Zombies = this.ConfigXML.SelectNodes("//Zombies/Zombie[@Multiplier>0]");
foreach (XmlNode zombie in Zombies)
{
Zombie z = new Zombie();
z.Name = GetAttributeValue(zombie, "Name");
z.Frequency = Convert.ToInt32(GetAttributeValue(zombie, "Frequency"));
z.Multiplier = Convert.ToInt32(GetAttributeValue(zombie, "Multiplier"));
XmlElement taskVariableList = zombie["TaskVariables"];
if (taskVariableList != null)
{
foreach (XmlNode variableNode in taskVariableList.ChildNodes)
{
switch (GetAttributeValue(variableNode, "Type").ToLower())
{
case "static":
StaticTaskVariable staticVar = new StaticTaskVariable();
staticVar.Name = GetNodeInnerText(variableNode, "Name");
staticVar.Value = GetNodeInnerText(variableNode, "Value");
staticVar.Reset = Convert.ToBoolean(GetAttributeValue(variableNode, "Reset"));
z.TaskVariables.Add(staticVar.Name, staticVar);
break;
case "sql":
SQLTaskVariable sqlVar = new SQLTaskVariable();
sqlVar.Name = GetNodeInnerText(variableNode, "Name");
sqlVar.Reset = Convert.ToBoolean(GetAttributeValue(variableNode, "Reset"));
sqlVar.ConnectionString = GetAttributeValue(variableNode, "ConnectionString");
sqlVar.ConnectionString = (sqlVar.ConnectionString.ToLower() == "{parent}") ? SourceConnectionString : sqlVar.ConnectionString;
sqlVar.Statement = GetAttributeValue(variableNode, "Statement");
z.TaskVariables.Add(sqlVar.Name, sqlVar);
break;
}
}
}
XmlElement taskNodeList = zombie["Tasks"];
if (taskNodeList != null)
{
foreach (XmlNode taskNode in taskNodeList.ChildNodes)
{
switch (taskNode.Attributes["Type"].Value.ToLower())
{
case "sql":
SQLTask task = new SQLTask(this);
task.Name = taskNode.Attributes["Name"].Value;
task.Sequence = Convert.ToInt32(taskNode.Attributes["Sequence"].Value);
XmlNodeList sqlStatements = taskNode["SQLStatements"].ChildNodes;
foreach (XmlNode statement in sqlStatements)
{
SQLStatement newStatement = new SQLStatement();
newStatement.Statement = statement["Text"].InnerText;
if (statement.Attributes["OutputType"] != null)
{
newStatement.OutputType = (SqlStatementOutputType)Enum.Parse(typeof(SqlStatementOutputType), statement.Attributes["OutputType"].Value);
}
else
{
newStatement.OutputType = SqlStatementOutputType.None;
}
newStatement.ConnectionString = statement.Attributes["ConnectionString"].Value == "{PARENT}" ? SourceConnectionString : statement.Attributes["ConnectionString"].Value;
XmlElement traceSQLOutput = statement["OutputList"];
if (traceSQLOutput != null)
{
newStatement.OutputList = GetOutputValues(traceSQLOutput.ChildNodes);
}
task.QueryList.Add(newStatement);
}
XmlElement traceSQLReplacements = taskNode["Replacements"];
if (traceSQLReplacements != null)
{
task.Replacements = GetReplacementValues(traceSQLReplacements.ChildNodes);
}
z.Tasks.Add(task);
break;
case "sqltrace":
SQLTraceTask trace = new SQLTraceTask(this);
trace.Name = taskNode.Attributes["Name"].Value;
trace.TraceSource = taskNode.Attributes["FilePath"].Value;
trace.ConnectionString = taskNode.Attributes["ConnectionString"].Value;
XmlNodeList traceTaskReplacements = taskNode["Replacements"].ChildNodes;
if (traceTaskReplacements != null)
{
trace.Replacements = GetReplacementValues(traceTaskReplacements);
}
XmlNodeList traceEventList = taskNode["EventsToMonitor"].ChildNodes;
foreach (XmlNode sqlEvent in traceEventList)
{
SQLEvent newEvent = new SQLEvent();
newEvent.EventName = sqlEvent.Attributes["Text"].Value;
trace.EventList.Add(newEvent);
}
z.Tasks.Add(trace);
break;
case "webservice":
WebServiceTask restTask = new WebServiceTask();
restTask.Endpoint = GetNodeInnerText(taskNode, "URL");
restTask.ContentType = GetChildNodeAttribute(taskNode, "URL", "ContentType");
restTask.ContentLength = Convert.ToInt32(GetChildNodeAttribute(taskNode, "URL", "ContentLength"));
restTask.Method = GetChildNodeAttribute(taskNode, "URL", "Verb");
if (taskNode["Authentication"] != null)
{
switch (taskNode["Authentication"].Attributes["Type"].Value.ToLower())
{
case "basic":
restTask.UserName = GetChildNodeAttribute(taskNode["Authentication"], "Credentials", "UserName");
restTask.Password = GetChildNodeAttribute(taskNode["Authentication"], "Credentials", "Password");
break;
case "none":
break;
}
}
if (taskNode["Cookies"] != null)
{
foreach (XmlNode taskCookie in taskNode["Cookies"].ChildNodes)
{
HttpCookie cookie = new HttpCookie();
cookie.Domain = GetAttributeValue(taskCookie, "Domain");
cookie.Name = GetAttributeValue(taskCookie, "Name");
cookie.Value = GetNodeInnerText(taskCookie, "Value");
restTask.Cookies.Add(cookie);
}
}
z.Tasks.Add(restTask);
break;
}
}
}
z.Tasks.Sort((task1, task2) => task1.Sequence.CompareTo(task2.Sequence));
returnList.Add(z);
}
return(returnList);
}
public override void Emit(XPathNavigator patternNavigator)
{
if (patternNavigator != null)
{
string name = patternNavigator.SelectSingleNode("@Name").Value;
string body = patternNavigator.SelectSingleNode("rc:Body", VulcanPackage.VulcanConfig.NamespaceManager).Value.Trim();
string type = patternNavigator.SelectSingleNode("@Type").Value;
string resultSet = patternNavigator.SelectSingleNode("@ResultSet").Value;
// Message.Trace("\n\n{0}\n\n", patternNavigator.SelectSingleNode("@Name").);
Connection c = Connection.GetExistingConnection(VulcanPackage, patternNavigator);
if (c != null)
{
SQLTask sqlTask = new SQLTask(VulcanPackage,
Resources.Create + name,
Resources.Create + name,
ParentContainer,
c
);
switch (type.ToUpperInvariant())
{
case "FILE":
string fileName = Resources.Create + name + Resources.ExtensionSQLFile;
string filePath = VulcanPackage.AddFileToProject(fileName);
TemplateEmitter te = new TemplateEmitter(new Template("temp", "temp", body));
te.SetParameters("");
te.Emit(filePath, false);
sqlTask.TransmuteToFileTask(fileName);
break;
case "EXPRESSION":
sqlTask.TransmuteToExpressionTask(body);
break;
default:
break;
}
switch (resultSet.ToUpperInvariant())
{
case "NONE":
sqlTask.ExecuteSQLTask.ResultSetType = Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ResultSetType.ResultSetType_None;
break;
case "SINGLEROW":
sqlTask.ExecuteSQLTask.ResultSetType = Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ResultSetType.ResultSetType_SingleRow;
break;
case "FULL":
sqlTask.ExecuteSQLTask.ResultSetType = Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ResultSetType.ResultSetType_Rowset;
break;
case "XML":
sqlTask.ExecuteSQLTask.ResultSetType = Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ResultSetType.ResultSetType_XML;
break;
default:
sqlTask.ExecuteSQLTask.ResultSetType = Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ResultSetType.ResultSetType_None;
break;
}
foreach (XPathNavigator resultNavigator in patternNavigator.Select("rc:Result", VulcanPackage.VulcanConfig.NamespaceManager))
{
sqlTask.BindResult(resultNavigator.SelectSingleNode("@Name").Value, resultNavigator.SelectSingleNode("@VariableName").Value);
}
this.FirstExecutableGeneratedByPattern = sqlTask.SQLTaskHost;
this.LastExecutableGeneratedByPattern = this.FirstExecutableGeneratedByPattern;
this.ExecuteDuringDesignTime(patternNavigator,sqlTask.SQLTaskHost);
} // if c != null
else
{
Message.Trace(Severity.Error, "{0}: Connection {1} does not exist.", name, patternNavigator.SelectSingleNode("rc:Connection", VulcanPackage.VulcanConfig.NamespaceManager).OuterXml);
}
}
}
