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