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