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 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 VulcanConfig XmlTemplateReplacement(VulcanConfig vulcanConfig) { List <XPathNavigator> nodeList = new List <XPathNavigator>(); VulcanConfig vc = vulcanConfig; foreach (XPathNavigator node in vc.Navigator.SelectDescendants(XPathNodeType.Element, true)) { if (node.XmlType != null && node.XmlType.Name != null) { if (node.XmlType.Name.ToUpperInvariant().Contains("TEMPLATEXML")) { nodeList.Add(node); } } } foreach (XPathNavigator node in nodeList) { XPathNavigator nameNode = node.SelectSingleNode("@Name"); string templateName = nameNode.Value; nameNode.DeleteSelf(); Message.Trace(Severity.Debug, "Replacing Template {0}", node.OuterXml); if (this.templateDictionary.ContainsKey(templateName)) { Template t = this[templateName]; TemplateEmitter te = new TemplateEmitter(t); int parameterCount = 0; if (node.MoveToFirstAttribute()) { do { parameterCount++; Message.Trace(Severity.Debug, "Mapping Parameter {0}={1}", node.Name, node.Value); te.SetNamedParameter(node.Name, node.Value); } while (node.MoveToNextAttribute()); } if (parameterCount == t.MapDictionary.Keys.Count) { string newXml; te.Emit(out newXml); if (parameterCount > 0) { node.MoveToParent(); } Message.Trace(Severity.Debug, "Old Node: {0}", node.OuterXml); node.OuterXml = newXml; Message.Trace(Severity.Debug, "New Node: {0}", node.OuterXml); } else { Message.Trace(Severity.Error, "Template parameters do not match up. Contains {0} but the template requires {1}", parameterCount, t.MapDictionary.Keys.Count); } } else { Message.Trace(Severity.Error, "Invalid template {0}", templateName); } } string savePath = vc.Save(); Message.Trace(Severity.Notification, "Saved new VulcanConfig to {0}", savePath); vc = new VulcanConfig(savePath); return(vc); }