public SsisSqlTask(SqlTask obj, SSISEmitterContext context) : base(obj, context)
{
_logicalExecuteSQL = obj;
_sqlTask = (DTS.TaskHost)Context.SSISSequence.AppendExecutable("STOCK:SQLTask");
if (!string.IsNullOrEmpty(_logicalExecuteSQL.Name))
{
_name = _logicalExecuteSQL.Name;
}
_sqlTask.Name = Name + Guid.NewGuid().ToString();
_sqlTask.Description = Description;
SetProperty("Connection", _logicalExecuteSQL.Connection);
SetProperty("IsolationLevel", _logicalExecuteSQL.IsolationLevel);
switch (_logicalExecuteSQL.Type.ToUpper(System.Globalization.CultureInfo.InvariantCulture))
{
case "EXPRESSION": SSISTask.SqlStatementSourceType = DTSTasks.ExecuteSQLTask.SqlStatementSourceType.DirectInput; break;
default: //case "FILE":
SSISTask.SqlStatementSourceType = DTSTasks.ExecuteSQLTask.SqlStatementSourceType.FileConnection; break;
}
}
public static PhysicalTask.SqlTask Lower(this AstTask.AstStoredProcNode astNode)
{
if (astNode.AsClassOnly)
{
return null;
}
try
{
PhysicalTSQL.StoredProc physicalStoredProc = new Ssis2008Emitter.IR.TSQL.StoredProc();
physicalStoredProc.Body = astNode.Body;
physicalStoredProc.ExecuteDuringDesignTime = astNode.ExecuteDuringDesignTime;
physicalStoredProc.Name = astNode.Name;
physicalStoredProc.Columns.Parent = physicalStoredProc;
foreach (AstTask.AstStoredProcColumnNode column in astNode.Columns)
{
PhysicalTSQL.StoredProcColumn physicalColumn = new Ssis2008Emitter.IR.TSQL.StoredProcColumn();
physicalColumn.Default = column.Default;
physicalColumn.IsOutput = column.IsOutput;
physicalColumn.Name = column.Name;
physicalColumn.Parent = physicalStoredProc;
physicalColumn.Type = Ssis2008Emitter.Emitters.TSQL.PhysicalTypeTranslator.Translate(column.Type, column.Length, column.Precision, column.Scale, column.CustomType);
physicalStoredProc.Columns.ColumnList.Add(physicalColumn);
}
TSQLEmitter.StoredProcPlatformEmitter storedProcEmitter = new TSQLEmitter.StoredProcPlatformEmitter();
string storedProcBody = storedProcEmitter.Emit(physicalStoredProc);
PhysicalTask.SqlTask sqlTask = new Ssis2008Emitter.IR.Task.SqlTask();
sqlTask.Body = storedProcBody;
AddConnection(astNode.Connection);
sqlTask.Connection = astNode.Connection.Name;
sqlTask.ExecuteDuringDesignTime = astNode.ExecuteDuringDesignTime;
sqlTask.Name = astNode.Name;
sqlTask.ResultSet = "None";
sqlTask.Type = "File";
return sqlTask;
}
catch (Exception e)
{
throw new SSISEmitterException(astNode, e);
}
}
public static PhysicalTask.SqlTask Lower(this AstTask.AstMergeTaskNode astNode)
{
if (astNode.AsClassOnly)
{
return null;
}
try
{
PhysicalTask.SqlTask mergeSqlTask = new PhysicalTask.SqlTask();
Ssis2008Emitter.Emitters.TSQL.PlatformEmitter.MergeEmitter me = new Ssis2008Emitter.Emitters.TSQL.PlatformEmitter.MergeEmitter();
mergeSqlTask.Name = astNode.Name;
mergeSqlTask.Type = "File";
mergeSqlTask.ResultSet = "NONE";
mergeSqlTask.ExecuteDuringDesignTime = false;
mergeSqlTask.Connection = astNode.Connection.Name;
AddConnection(astNode.Connection);
mergeSqlTask.Body = me.Emit(astNode);
return mergeSqlTask;
}
catch (Exception e)
{
throw new SSISEmitterException(astNode, e);
}
}
public static Package Lower(this PhysicalTSQL.Table table)
{
Package tablePackage = new Package();
_CurrentPackage = tablePackage;
AddConnection(table.ConnectionConfiguration);
tablePackage.ConstraintMode = "Linear";
tablePackage.DefaultPlatform = "SSIS2008";
tablePackage.Log = true;
tablePackage.LogConnectionName = table.ConnectionConfiguration == null ? null : table.ConnectionConfiguration.Name;
tablePackage.Name = String.Format(System.Globalization.CultureInfo.InvariantCulture, "{0}", table.Name);
PhysicalTask.SqlTask createTableTask = new Ssis2008Emitter.IR.Task.SqlTask();
createTableTask.Name = "Create Table";
createTableTask.ResultSet = "None";
createTableTask.Connection = table.ConnectionConfiguration == null ? null : table.ConnectionConfiguration.Name;
createTableTask.ExecuteDuringDesignTime = false;
createTableTask.Parent = tablePackage;
createTableTask.Type = "File";
createTableTask.Body = new TSQLEmitter.TablePlatformEmitter().Emit(table);
tablePackage.Tasks.Add(createTableTask);
return tablePackage;
}
public static PhysicalTask.SqlTask CreateLogEndTask(PhysicalTask.SequenceTask logContainer, PhysicalTask.SequenceTask container, string path)
{
StringBuilder sLogEndTask = new StringBuilder();
sLogEndTask.Append(new TSQLEmitter.LogPlatformEmitter().LogPrepareToSetValue(logContainer));
sLogEndTask.Append(" + \n");
string logTaskName = Resources.Log + Resources.Seperator + _CurrentPackage.Name + Resources.Seperator + logContainer.Name + Resources.Seperator;
PhysicalTask.SqlTask logEndTask = new PhysicalTask.SqlTask();//logEndTask.logContainer, context, logTaskName + Resources.Stop);
logEndTask.Type = "Expression";
logEndTask.Connection = logContainer.LogConnectionName;
logEndTask.ResultSet = "NONE";
Variable varLogID = AddVariable(logContainer, Resources.LogID, Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Input, logEndTask, "INT32");
foreach (Variable variable in container.VariableList)
{
sLogEndTask.Append(new TSQLEmitter.LogPlatformEmitter().LogSetValue(logContainer, varLogID.Name, SetVariablePath(path), variable.Name, variable.Name));
sLogEndTask.Append(" + \n");
AddVariable(variable, Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Input, logEndTask);
}
sLogEndTask.Append(new TSQLEmitter.LogPlatformEmitter().LogEnd(logContainer, varLogID.Name));
AddVariable(logContainer, Resources.LogID, Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Input, logEndTask, "INT32");
logEndTask.Body = sLogEndTask.ToString();
return logEndTask;
}
private static PhysicalTask.SqlTask CreateLogReadPreviousValuesTask(PhysicalTask.SequenceTask logContainer, PhysicalTask.SequenceTask container, string path)
{
string logTaskName = Resources.Log + Resources.Seperator + Resources.LoadInto + Resources.Seperator + logContainer.Name;
PhysicalTask.SqlTask logReadValuesTask = new PhysicalTask.SqlTask();
logReadValuesTask.Type = "Expression";
logReadValuesTask.Name = String.Format("LogReadValues-{0}", logContainer.Name);
logReadValuesTask.Connection = logContainer.LogConnectionName;
logReadValuesTask.ResultSet = "NONE";
AddVariable(logContainer, Resources.LastSuccessfulRunLogID, Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Input, logReadValuesTask, "INT32");
AddVariable(logContainer, Resources.StartTime, Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output, logReadValuesTask, "INT32");
AddVariable(logContainer, Resources.EndTime, Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output, logReadValuesTask, "INT32");
AddVariable(logContainer, Resources.Status, Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output, logReadValuesTask, "STRING");
AddVariable(logContainer, Resources.Notes, Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output, logReadValuesTask, "STRING");
StringBuilder sGetValues = new StringBuilder();
sGetValues.Append(new TSQLEmitter.LogPlatformEmitter().LogGetPredefinedValues(logContainer));
foreach (Variable v in container.VariableList)
{
sGetValues.Append(" + \n");
sGetValues.Append(new TSQLEmitter.LogPlatformEmitter().LogGetValue(logContainer, Resources.LastSuccessfulRunLogID, SetVariablePath(path), v.Name));
AddVariable(v, Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output, logReadValuesTask);
}
logReadValuesTask.Body = sGetValues.ToString();
return logReadValuesTask;
}
public static PhysicalTask.SqlTask CreateLogStartTask(PhysicalTask.SequenceTask LogContainer, string path)
{
string logTaskName = Resources.Log + Resources.Seperator + _CurrentPackage.Name + Resources.Seperator + LogContainer.Name + Resources.Seperator;
PhysicalTask.SqlTask logStartTask = new PhysicalTask.SqlTask();
Variable varLogID = AddVariable(LogContainer, Resources.LogID, Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output, logStartTask, "INT32");
AddVariable(LogContainer, Resources.LastSuccessfulRunLogID, Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output, logStartTask, "INT32");
AddVariable(LogContainer, Resources.IsAnotherInstanceCurrentlyRunningLogID, Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output, logStartTask, "INT32");
logStartTask.Type = "Expression";
logStartTask.Name = logTaskName + Resources.Start;
logStartTask.ResultSet = "NONE";
logStartTask.Connection = LogContainer.LogConnectionName;
logStartTask.ExecuteDuringDesignTime = false;
logStartTask.Parent = LogContainer;
logStartTask.Body = new TSQLEmitter.LogPlatformEmitter().LogStart(LogContainer, _CurrentPackage.Name, LogContainer.Name, GetVariablePathXML(path));
return logStartTask;
}
public static PhysicalTask.SqlTask Lower(this AstTask.AstExecuteSQLTaskNode astNode)
{
if (astNode.AsClassOnly)
{
return null;
}
try
{
PhysicalTask.SqlTask sqlTask = new Ssis2008Emitter.IR.Task.SqlTask();
sqlTask.Body = astNode.Body;
AddConnection(astNode.Connection);
sqlTask.Connection = astNode.Connection.Name;
sqlTask.ExecuteDuringDesignTime = astNode.ExecuteDuringDesignTime;
sqlTask.Name = astNode.Name;
sqlTask.ResultSet = astNode.ResultSet.ToString();
sqlTask.Type = astNode.Type.ToString();
sqlTask.IsolationLevel = astNode.IsolationLevel;
foreach (AstTask.AstParameterMappingTypeNode mapping in astNode.Results)
{
PhysicalTask.ExecuteSQLResult result = new Ssis2008Emitter.IR.Task.ExecuteSQLResult();
result.Name = mapping.ParameterName;
result.VariableName = mapping.Variable.Name;
result.Parent = sqlTask;
sqlTask.ResultList.Add(result);
}
return sqlTask;
}
catch (Exception e)
{
throw new SSISEmitterException(astNode, e);
}
}
