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