public IDTSParameterBinding Modify_ExecSQL_AddParameterBinding(
Executable ex,
string BingingVariableName,
OleDBDataTypes BingingDataType
)
{
TaskHost SQLth = (TaskHost)ex;
ExecuteSQLTask task = SQLth.InnerObject as ExecuteSQLTask;
int i = 0;
foreach (IDTSParameterBinding z in task.ParameterBindings)
{
i++;
}
task.ParameterBindings.Add();
IDTSParameterBinding pb = task.ParameterBindings.GetBinding(i);
pb.DtsVariableName = BingingVariableName;
pb.ParameterDirection = ParameterDirections.Input;
pb.DataType = (int)BingingDataType;
pb.ParameterName = i;
pb.ParameterSize = -1;
return(pb);
}
/// <summary>
/// Extension method to easily add parameters bindings to ExecSQLTask
/// </summary>
/// <param name="binds"></param>
/// <param name="parameterName"></param>
/// <param name="dtsVariableName"></param>
/// <param name="dataType"></param>
public static void Add(this IDTSParameterBindings binds, string parameterName, string dtsVariableName, OleDBDataTypes dataType)
{
IDTSParameterBinding x = binds.Add();
x.ParameterName = parameterName;
x.DtsVariableName = dtsVariableName;
x.DataType = (int)dataType;
}
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);
}
/// <summary>
/// ctor accepts an ISExecuteSqlTask and various Paramater Binding Properties
/// If a ParameterBinding with the passed name exists, then that is used; otherwise a new Parameter Binding is created
/// </summary>
/// <param name="sqlTask"></param>
/// <param name="dataType"></param>
/// <param name="dtsVariableName"></param>
/// <param name="paramDirection"></param>
/// <param name="paramName"></param>
/// <param name="paramSize"></param>
public ISParameterBinding(ISExecuteSqlTask sqlTask, int dataType, string dtsVariableName, ParameterDirections paramDirection, object paramName, int paramSize)
{
bool paramExists = false;
IEnumerator parameters = sqlTask.ParameterBindings_m.GetEnumerator();
while (parameters.MoveNext())
{
IDTSParameterBinding param = (parameters.Current as IDTSParameterBinding);
if (param.ParameterName.ToString() == paramName.ToString())
{
paramExists = true;
ParameterBinding = param;
}
}
if (paramExists == false)
{
ParameterBinding = sqlTask.ParameterBindings_m.Add();
ParameterName = paramName;
}
DataType = dataType;
DtsVariableName = dtsVariableName;
ParameterDirection = paramDirection;
ParameterSize = paramSize;
}
static void Main(string[] args)
{
Console.WriteLine("Building Package...");
// Create a new SSIS Package
var package = new Package();
TaskHost taskHost;
// Add a Connection Manager to the Package, of type, OLEDB
var connMgrOleDb = package.Connections.Add("OLEDB");
var connectionString = new StringBuilder();
connectionString.Append("Provider=SQLOLEDB.1;");
connectionString.Append("Integrated Security=SSPI;Initial Catalog=");
connectionString.Append("DB");
connectionString.Append(";Data Source=");
connectionString.Append(".");
connectionString.Append(";");
connMgrOleDb.ConnectionString = connectionString.ToString();
connMgrOleDb.Name = "My OLE DB Connection";
connMgrOleDb.Description = "OLE DB connection";
int i = 0;
package.Variables.Add("ChangeDate", false, "User", "2019-21-01");
using (SqlConnection con = new SqlConnection("Data Source=.; Initial Catalog=DB;Integrated Security=SSPI;"))
{
con.Open();
using (SqlCommand command = new SqlCommand("select * from ##list", con))
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var a = package.Executables.Add("STOCK:SQLTask");
taskHost = a as TaskHost;
i++;
// Set required properties
taskHost.Properties["Connection"].SetValue(taskHost, connMgrOleDb.ID);
taskHost.Properties["SqlStatementSource"].SetValue(taskHost, reader.GetString(1));
// Add variable to hold parameter value
Console.WriteLine(taskHost.InnerObject.GetType().ToString());
ExecuteSQLTask task = taskHost.InnerObject as ExecuteSQLTask;
task.ParameterBindings.Add();
IDTSParameterBinding parameterBinding = task.ParameterBindings.GetBinding(0);
parameterBinding.DtsVariableName = "User::ChangeDate";
parameterBinding.ParameterDirection = ParameterDirections.Input;
parameterBinding.DataType = 7;
parameterBinding.ParameterName = "0";
taskHost.Name = reader.GetString(2);
}
}
using (SqlCommand command = new SqlCommand("select * from ##dep2", con))
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
PrecedenceConstraint pcFileTasks = package.PrecedenceConstraints.Add(package.Executables[reader.GetString(1)], package.Executables[reader.GetString(0)]);
}
}
}
var app = new Application();
Console.WriteLine("Saving Package...");
app.SaveToXml(@"C:\Users\cognos\Documents\SSIS Exports\test.dtsx", package, null);
}
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
}
}
}
