/// <summary>
/// Extension method to easily add result bindings to ExecSQLTask
/// </summary>
/// <param name="binds"></param>
/// <param name="resultName"></param>
/// <param name="dtsVariableName"></param>
public static void Add(this IDTSResultBindings binds, string resultName, string dtsVariableName)
{
IDTSResultBinding x = binds.Add();
x.ResultName = resultName;
x.DtsVariableName = dtsVariableName;
}
/// <summary>
/// ctor accepts an ISExecuteSqlTask, result name and variable name
/// If a ResultBinding with the same name exists, then that is reused otherwise a new Result Binding is created
/// </summary>
/// <param name="sqlTask"></param>
/// <param name="dtsVariableName"></param>
/// <param name="resultName"></param>
public ISResultSetBinding(ISExecuteSqlTask sqlTask, string dtsVariableName, object resultName)
{
bool resultSetExists = false;
IEnumerator resultSets = sqlTask.ResultSetBindings_m.GetEnumerator();
while (resultSets.MoveNext())
{
IDTSResultBinding resultSet = (resultSets.Current as IDTSResultBinding);
if (resultSet.ResultName.ToString() == ResultName.ToString())
{
resultSetExists = true;
ResultSetBinding = resultSet;
}
}
if (resultSetExists == false)
{
ResultSetBinding = sqlTask.ResultSetBindings_m.Add();
ResultName = resultName;
}
DtsVariableName = dtsVariableName;
}
public IDTSResultBinding Modify_ExecSQL_AddResultSetBinding(Executable ex,
string ResultName,
string VariableName
)
{
TaskHost SQLth = (TaskHost)ex;
ExecuteSQLTask task = SQLth.InnerObject as ExecuteSQLTask;
int i = 0;
foreach (IDTSResultBinding z in task.ResultSetBindings)
{
i++;
}
// Add result set binding, map the id column to variable
task.ResultSetBindings.Add();
IDTSResultBinding rb = task.ResultSetBindings.GetBinding(i);
rb.ResultName = ResultName;
rb.DtsVariableName = VariableName;
return(rb);
}
public void SetVariableRuntime(SetVariablesSetVariable[] sqlTaskSetVariables, string stepName, string setName)
{
int iVarCount = 0;
foreach (var sqlSetVariable in sqlTaskSetVariables)
{
Executable sqlSetVariableExe = AddExecutableSQLTask(sqlSetVariable.SQLStatement, sqlSetVariable.TargetConnection, "Set Variable-" + stepName + "_" + setName + iVarCount.ToString(), string.Empty);
TaskHost srcth = sqlSetVariableExe as TaskHost;
// srcth.Properties["BypassPrepare"].SetValue(srcth, true);
// srcth.Properties["Connection"].SetValue(srcth, sqlSetVariable.TargetConnection);
// srcth.Properties["SqlStatementSourceType"].SetValue(srcth, SqlStatementSourceType.DirectInput);
//srcth.Properties["SqlStatementSource"].SetValue(srcth, sqlSetVariable.SQLStatement);
srcth.Properties["ResultSetType"].SetValue(srcth, ResultSetType.ResultSetType_SingleRow);
var sqlTaskSRC = srcth.InnerObject as IDTSExecuteSQL;
foreach (var resultSet in sqlSetVariable.ResultSet)
{
IDTSResultBinding resultbinding = sqlTaskSRC.ResultSetBindings.Add();
resultbinding.ResultName = resultSet.Order;
resultbinding.DtsVariableName = GetVariableValue(resultSet.VariableName).QualifiedName;
}
AddPrecedenceConstraints(sqlSetVariableExe);
iVarCount++;
}
}
public void AddDataFlowSet(CHEFMetaDataProcessStepDataFlowSet dataFlowSet, string stepName)
{
string ErrorDetail = string.Empty;
string Name = string.Empty;
if (stepName == string.Empty)
{
throw new Exception("Step Name is missing: Please modify the metadata and try again");
}
if (dataFlowSet != null)
{
if (dataFlowSet.Name == string.Empty || dataFlowSet.Name == null)
{
throw new Exception("Data Flow Set Name is missing under Step " + stepName + " : Please modify the metadata and try again");
}
else if (dataFlowSet.TargetConnection != string.Empty)
{
//Handling Paraller Task
bool isParallel = true;
if (dataFlowSet.RunParallel.ToUpper() != "TRUE")
{
isParallel = false;
}
if (dataFlowSet.DataFlow != null)
{
//Setting up Variables Values @Runtime
if (dataFlowSet.SetVariables != null)
{
SetVariableRuntime(dataFlowSet.SetVariables, stepName, dataFlowSet.Name);
}
if (dataFlowSet.SendMailTask != null)
{
foreach (var sendMail in dataFlowSet.SendMailTask)
{
AddSendMailTask(sendMail.Name, sendMail.SMTPServer, sendMail.From, sendMail.To, sendMail.CC, sendMail.BCC, sendMail.Subject, sendMail.MessageSource, sendMail.Attachments, sendMail.Priority, sendMail.MessageSourceType);
}
}
IDTSSequence container = null;
if (isParallel)
{
_sequence = (Sequence)_Package.Executables.Add("STOCK:SEQUENCE");
_sequence.Name = dataFlowSet.Name;
if (_preTaskHost != null)
{
_Package.PrecedenceConstraints.Add(_preTaskHost, _sequence);
}
container = _sequence;
}
else
{
container = _Package;
}
foreach (var dataFlow in dataFlowSet.DataFlow)
{
if (dataFlow.Name == string.Empty)
{
throw new Exception("Data Flow Task name is missing under DataFlow Set " + dataFlowSet.Name + " : Please modify the metadata and try again");
}
else
{
if (isParallel)
{
_preTaskHost = null;
}
Name = stepName + "_" + dataFlowSet.Name + "_" + dataFlow.Name;
ErrorDetail = "Please varify the metadata for DataFlow :" + dataFlow.Name + "' under Step:'" + stepName + "' and DataFlowSet:'" + dataFlowSet.Name + "'";
SourceType sourceType = (SourceType)Enum.Parse(typeof(SourceType), dataFlowSet.SourceType);
TargetType targetType = (TargetType)Enum.Parse(typeof(TargetType), dataFlowSet.TargetType);
Executable sqlTaskPostExe = null;
Executable sqlTaskPreExe = null;
sqlTaskPreExe = AddExecutableSQLTask(PreSQL.Replace("<StepName>", Name) + ",?", LogConnectionKey, "Pre-" + Name, string.Empty, container);
//Source Counting binding
string sourcevariableName = "SRC" + iCount.ToString();
Variable src = SetVariableValue(sourcevariableName, 0, "Int32");
string taskFailSuccessVariable = "TaskFailSuccess" + iCount.ToString();
Variable varTaskFailSuccess = SetVariableValue(taskFailSuccessVariable, 2, "Int32");
TaskHost sqlTaskHostPre = sqlTaskPreExe as TaskHost;
var sqlTaskPre = sqlTaskHostPre.InnerObject as IDTSExecuteSQL;
IDTSParameterBinding bindingPre = sqlTaskPre.ParameterBindings.Add();
bindingPre.ParameterDirection = ParameterDirections.Input;
bindingPre.DtsVariableName = src.QualifiedName;
bindingPre.ParameterName = 0;
bindingPre.DataType = 3;
if (SourceType.SPList != sourceType && SourceType.FlatFile != sourceType && SourceType.TableStorage != sourceType && TargetType.TableStorage != targetType && GetConnStr(dataFlowSet.SourceConnection).Contains("database.windows.net") == false)
{
string sqlSRCStatement = "declare @rowCount INT = 0 select @rowCount = rows from sys.partitions with (nolock) where object_id = object_id('" + dataFlow.TargetName + "') select @rowCount";
Executable sqlExecSRC = AddExecutableSQLTask(sqlSRCStatement, dataFlowSet.SourceConnection, "SRC " + Name, string.Empty, container);
TaskHost sqlTaskHostSRC = sqlExecSRC as TaskHost;
var sqlTaskSRC = sqlTaskHostSRC.InnerObject as IDTSExecuteSQL;
TaskHost srcth = sqlExecSRC as TaskHost;
srcth.Properties["ResultSetType"].SetValue(srcth, ResultSetType.ResultSetType_SingleRow);
IDTSResultBinding resultbinding = sqlTaskSRC.ResultSetBindings.Add();
resultbinding.ResultName = 0;
resultbinding.DtsVariableName = src.QualifiedName;
AddPrecedenceConstraints(sqlExecSRC, container);
}
else if (SourceType.FlatFile == sourceType)
{
string strSQLPreQuery = "DECLARE @TaskHasBadRows INT DECLARE @RC INT SET @TaskHasBadRows=? SET @RC=? IF @TaskHasBadRows >=4 BEGIN RAISERROR ('File has bad records. Modifiy the bad rows and re-run the process', 16, 1) END ELSE BEGIN Exec CHEF.InsertLog '" + Name + "', @TaskHasBadRows, @RC END";
varTaskFailSuccess.Expression = "@[CHEF::BadRC" + (iCount).ToString() + "] >0 ?4:2";
varTaskFailSuccess.EvaluateAsExpression = true;
Variable packageFailSuccess = GetVariableValue("CHEF::PackageFailSuccess");
if (packageFailSuccess.Expression == string.Empty || packageFailSuccess.Expression == null)
{
packageFailSuccess.Expression = "@[CHEF::TaskFailSuccess" + (iCount).ToString() + "] ==4 ?4:2";
packageFailSuccess.EvaluateAsExpression = true;
}
else
{
packageFailSuccess.Expression = "@[CHEF::TaskFailSuccess" + (iCount).ToString() + "] ==4 || " + packageFailSuccess.Expression;
}
}
AddPrecedenceConstraints(sqlTaskPreExe, container);
//Truncate or Delete Cases
if (TargetType.TableStorage != targetType && TargetType.SPList != targetType)
{
AddTruncateOrDeleteSQLTask(container, Name, dataFlow.Name, dataFlow.TargetName, dataFlowSet.TargetConnection, dataFlowSet.TruncateOrDeleteBeforeInsert, dataFlowSet.DeleteFilterClause);
}
Executable dftTaskExe = AddDataFlowTask(container, Name, dataFlowSet, dataFlow, ErrorDetail);
AddPrecedenceConstraints(dftTaskExe, container);
// Post Log
sqlTaskPostExe = AddExecutableSQLTask(PostPassSuccessSQL.Replace("<StepName>", Name), LogConnectionKey, "Post-" + Name, string.Empty, container);
TaskHost sqlTaskHostPost = sqlTaskPostExe as TaskHost;
var sqlTaskPost = sqlTaskHostPost.InnerObject as IDTSExecuteSQL;
IDTSParameterBinding bindingPostBadRC = sqlTaskPost.ParameterBindings.Add();
bindingPostBadRC.ParameterDirection = ParameterDirections.Input;
bindingPostBadRC.DtsVariableName = varTaskFailSuccess.QualifiedName;
bindingPostBadRC.ParameterName = 0;
bindingPostBadRC.DataType = 3;
IDTSParameterBinding bindingPost = sqlTaskPost.ParameterBindings.Add();
bindingPost.ParameterDirection = ParameterDirections.Input;
bindingPost.DtsVariableName = "CHEF::RC" + (iCount - 1);
bindingPost.ParameterName = 1;
bindingPost.DataType = 3;
AddPrecedenceConstraints(sqlTaskPostExe, container);
}
}
}
if (isParallel)
{
_preTaskHost = _sequence;
}
}
else
{
// loging if target connection is not provided
}
}
}