private Executable AddDataFlowTask(IDTSSequence container, string Name, CHEFMetaDataProcessStepDataFlowSet dataFlowSet, CHEFMetaDataProcessStepDataFlowSetDataFlow dataFlow, string ErrorDetail)
{
IDTSComponentMetaData100 sourceTask = null;
IDTSComponentMetaData100 targetTask = null;
IDTSComponentMetaData100 rowCountTask = null;
IDTSComponentMetaData100 badRowCountTask = null;
IDTSComponentMetaData100 badRowTask = null;
bool unicode = false;
string variableName = "RC" + iCount.ToString();
SetVariableValue(variableName, 0, "Int32");
string variableBadRowCount = "BadRC" + iCount.ToString();
Variable varBadRC = SetVariableValue(variableBadRowCount, 0, "Int32");
SourceType sourceType = (SourceType)Enum.Parse(typeof(SourceType), dataFlowSet.SourceType);
TargetType targetType = (TargetType)Enum.Parse(typeof(TargetType), dataFlowSet.TargetType);
iCount++;
DFTTask dft_task = new DFTTask();
Executable dftTask = dft_task.AddDFTTask(container, Name, iCount);
ConnectionManager conMgrSource = null;
string conStr = string.Empty;
if (sourceType == SourceType.TableStorage || sourceType == SourceType.SPList)
{
conStr = GetConnStr(dataFlowSet.SourceConnection);
}
else
{
conMgrSource = GetConnection(dataFlowSet.SourceConnection);
}
ConnectionManager conMgrDestination = null;
string conStrTarget = string.Empty;
if (targetType == TargetType.TableStorage || targetType == TargetType.SPList)
{
conStrTarget = GetConnStr(dataFlowSet.TargetConnection);
}
else
{
conMgrDestination = GetConnection(dataFlowSet.TargetConnection);
}
ConnectionManager conMgrBadRows = null;
string sourceName = dataFlow.SourceName;
string targetName = dataFlow.TargetName;
string taskName = dataFlow.Name;
if (taskName == null || taskName == string.Empty)
{
taskName = Name;
}
if (sourceType == SourceType.FlatFile)
{
if (dataFlow.SourceName.Contains("@[CHEF::"))
{
Variable varTaskFlowNameRuntime = SetVariableValue("varTaskFlowNameRuntime" + iVarCount.ToString(), "", "String");
varTaskFlowNameRuntime.EvaluateAsExpression = true;
varTaskFlowNameRuntime.Expression = "\"" + dataFlow.SourceName + "\"";
iVarCount++;
sourceName = varTaskFlowNameRuntime.Value.ToString();
}
string flatFileConnectionKey = taskName.Replace('.', '_');
string badRowsConnectionKey = flatFileConnectionKey + "_BadRows";
AddConnection(new Connection()
{
Key = flatFileConnectionKey, ConnectionString = conMgrSource.ConnectionString + @"\" + sourceName, ConnectionType = ConnectionTypes.FlatFileConnection
});
conMgrSource = GetConnection(flatFileConnectionKey);
AddConnection(new Connection()
{
Key = badRowsConnectionKey, ConnectionString = this.BadRowsFolderLocation + @"\" + badRowsConnectionKey + ".txt", ConnectionType = ConnectionTypes.FlatFileConnection
});
conMgrBadRows = GetConnection(badRowsConnectionKey);
conMgrBadRows.Properties["ColumnNamesInFirstDataRow"].SetValue(conMgrBadRows, false);
//Map target columns
if (dataFlowSet.UniCode == null || dataFlowSet.UniCode == string.Empty)
{
unicode = false;
}
else
{
unicode = Convert.ToBoolean(dataFlowSet.UniCode);
}
// conMgrBadRows.Properties["Unicode"].SetValue(conMgrBadRows, unicode);
if (Convert.ToBoolean(dataFlowSet.PickColumnsFromTarget))
{
string strConn = GetConnStr(dataFlowSet.TargetConnection);
dft_task.AddVirtualColumnsFromTarget(conMgrSource, sourceTask, dataFlow.TargetName, dataFlowSet.ColumnDelimeter, Convert.ToBoolean(dataFlowSet.IsColumnNamesInFirstDataRow), Convert.ToBoolean(dataFlowSet.AllowFlatFileTruncate), unicode, strConn);
}
else
{
throw new Exception("Engine don't support Pick Columns From Target as 'False'");
}
}
TaskHost taskHost = dftTask as TaskHost;
MainPipe dataFlowTask = taskHost.InnerObject as MainPipe;
if (sourceType == SourceType.SELECTSQL)
{
if (sourceName.Contains("@[CHEF::"))
{
string str = sourceName;
Variable taskFailSuccess = SetVariableValue("varTaskFlowSQLStatement" + iVarCount.ToString(), "", "String");
taskFailSuccess.EvaluateAsExpression = true;
iVarCount++;
taskFailSuccess.Expression = "\"" + str + "\"";
dft_task.AddSourceTask(dataFlowTask, Name, taskFailSuccess.QualifiedName, sourceType, conMgrSource, out sourceTask, true, conStr);
}
else
{
dft_task.AddSourceTask(dataFlowTask, Name, dataFlow.SourceName, sourceType, conMgrSource, out sourceTask, false, conStr);
}
}
else
{
dft_task.AddSourceTask(dataFlowTask, Name, dataFlow.SourceName, sourceType, conMgrSource, out sourceTask, false, conStr);
}
dft_task.AddRowCount(dataFlowTask, Name, out rowCountTask, variableName);
dataFlowTask.ConnectSourceTarget(sourceTask, rowCountTask);
CManagedComponentWrapper InstanceDestination = dft_task.AddTargetTask(dataFlowTask, Name, targetName, targetType, conMgrDestination, out targetTask, conStrTarget);
dataFlowTask.ConnectSourceTarget(rowCountTask, targetTask);
// dataFlowTask.ConnectSourceTarget(sourceTask, targetTask);
if (targetType == TargetType.FlatFile)
{
//Add column based on source
dft_task.GenerateColumnForVirtualInput(conMgrDestination, targetTask, dataFlowSet.ColumnDelimeter, unicode);
//Refresh to generate meta data
InstanceDestination.ConnectAndReinitializeMetaData(sourceName);
//Map target columns
dft_task.MapColumns(targetTask, InstanceDestination, DTSUsageType.UT_READONLY, ErrorDetail);
}
else if (targetType == TargetType.Table && sourceType == SourceType.FlatFile)
{
//Map target columns
dft_task.MapColumns(targetTask, InstanceDestination, DTSUsageType.UT_READONLY, ErrorDetail);
dft_task.AddRowCount(dataFlowTask, Name + "BadRowCount", out badRowCountTask, variableBadRowCount);
dataFlowTask.ConnectBadRowsSourceTarget(sourceTask, badRowCountTask);
BadRowsRedirect(sourceTask, badRowCountTask);
CManagedComponentWrapper InstanceBadRowDestination = dft_task.AddTargetTask(dataFlowTask, Name + "BadRows", sourceName + "BadRows", TargetType.FlatFile, conMgrBadRows, out badRowTask, string.Empty);
BadRowsRedirect(badRowCountTask, badRowTask);
dataFlowTask.ConnectSourceTarget(badRowCountTask, badRowTask);
//Add column based on source
dft_task.GenerateColumnForVirtualInput(conMgrBadRows, badRowTask, "\\t", false);
//Refresh to generate meta data
InstanceBadRowDestination.ConnectAndReinitializeMetaData(sourceName);
//Map target columns
dft_task.MapColumns(badRowTask, InstanceBadRowDestination, DTSUsageType.UT_READONLY, ErrorDetail);
}
else if (targetType != TargetType.TableStorage)
{
if (targetType == TargetType.Table && sourceType == SourceType.SPList && Convert.ToBoolean(dataFlowSet.PickColumnsFromTarget))
{
dft_task.MapColumnsSPList(targetTask, InstanceDestination, DTSUsageType.UT_READONLY, ErrorDetail);
}
else
{
dft_task.MapColumns(targetTask, InstanceDestination, DTSUsageType.UT_READONLY, ErrorDetail);
}
}
//Function to add Event handler SQLTask
AddSQLErrorTask(dftTask, Name);
return(dftTask);
}
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
}
}
}
