public static void AddSqlTask(Executable exe, ExecuteSQLTaskWrapper sqlTask)
{
TaskHost th = exe as TaskHost;
//Configure the task
th.Name = sqlTask.Name;
th.Properties["BypassPrepare"].SetValue(th, sqlTask.BypassPrepare);
th.Properties["Connection"].SetValue(th, sqlTask.Connection);
th.Properties["SqlStatementSourceType"].SetValue(th, sqlTask.SqlStatementSourceTypeField);
th.Properties["SqlStatementSource"].SetValue(th, sqlTask.SqlStatementSource);
th.Properties["ResultSetType"].SetValue(th, sqlTask.ResultSetTypeField);
th.Properties["SqlStatementSource"].SetExpression(th, sqlTask.SetExpression);
th = null;
}
public Executable AddExecutableSQLTask(String SQLStatement, string conKey, string taskName, string variableName = "", IDTSSequence container = null)
{
if (container == null)
{
container = _Package;
}
ConnectionManager conMgrSource = _ConnectionDic[conKey];
ExecuteSQLTaskWrapper sqlTask = new ExecuteSQLTaskWrapper();
//Configure the task
sqlTask.Name = taskName;
sqlTask.BypassPrepare = true;
sqlTask.Connection = conMgrSource.Name.ToString();
sqlTask.SqlStatementSourceTypeField = SqlStatementSourceType.DirectInput;
if (SQLStatement.Contains(@"@[CHEF::"))
{
sqlTask.SqlStatementSourceTypeField = SqlStatementSourceType.Variable;
Variable taskFailSuccess = SetVariableValue("varSQLStatement" + iVarCount.ToString(), "", "String");
taskFailSuccess.EvaluateAsExpression = true;
taskFailSuccess.Expression = "\"" + SQLStatement.Replace(@"\", @"\\").Replace("\\\\\"", "\\\"") + "\"";
sqlTask.SqlStatementSource = taskFailSuccess.QualifiedName;
iVarCount++;
}
else
{
sqlTask.SqlStatementSource = SQLStatement;
}
sqlTask.ResultSetTypeField = ResultSetType.ResultSetType_None;
Executable exe = container.Executables.Add(SSISMoniker.SQL_TASK);
ExecuteSQLTaskWrapper.AddSqlTask(exe, sqlTask);
if (variableName != string.Empty)
{
TaskHost sqlTaskHostPost = exe as TaskHost;
var sqlTaskPre = sqlTaskHostPost.InnerObject as IDTSExecuteSQL;
IDTSParameterBinding bindingPostSuccessFailure = sqlTaskPre.ParameterBindings.Add();
bindingPostSuccessFailure.ParameterDirection = ParameterDirections.Input;
bindingPostSuccessFailure.DtsVariableName = variableName;
bindingPostSuccessFailure.ParameterName = 0;
bindingPostSuccessFailure.DataType = 3;
}
//#####Error Task#####
AddSQLErrorTask(exe, sqlTask.Name);
return(exe);
}
