private void CheckExecuteSQLTask(TaskHost taskHost, TreeNode parent)
{
ExecuteSQLTask task = taskHost.InnerObject as ExecuteSQLTask;
TreeNode parameterBindings = AddFolder("ParameterBindings", parent);
foreach (IDTSParameterBinding binding in task.ParameterBindings)
{
string match;
string value = binding.DtsVariableName;
if (!string.IsNullOrEmpty(value) && PropertyMatchEval(value, out match))
{
VariableFoundEventArgs info = new VariableFoundEventArgs();
info.Match = match;
OnRaiseVariableFound(info);
AddNode(parameterBindings, binding.ParameterName.ToString(), GetImageIndex(IconKeyProperty), binding, true);
}
}
TreeNode resultSetBindings = AddFolder("ResultSetBindings", parent);
foreach (IDTSResultBinding binding in task.ResultSetBindings)
{
string match;
string value = binding.DtsVariableName;
if (!string.IsNullOrEmpty(value) && PropertyMatchEval(value, out match))
{
VariableFoundEventArgs info = new VariableFoundEventArgs();
info.Match = match;
OnRaiseVariableFound(info);
AddNode(resultSetBindings, binding.ResultName.ToString(), GetImageIndex(IconKeyProperty), binding, true);
}
}
}
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);
}
public Executable AddTask_ExecSQL(string taskName,
string connManName,
string statement,
Sequence sqp = null,
SqlStatementSourceType sourceType = SqlStatementSourceType.DirectInput,
ResultSetType resultSetType = ResultSetType.ResultSetType_None)
{
// DirectInput 1
// FileConnection 2
// Variable 3
Executable ex = null;
if (sqp == null)
{
ex = p.Executables.Add("STOCK:SQLTask");
}
else
{
ex = sqp.Executables.Add("STOCK:SQLTask");
}
TaskHost SQLth = (TaskHost)ex;
ExecuteSQLTask est = SQLth.InnerObject as ExecuteSQLTask;
DtsProperty dt_name = SQLth.Properties["Name"];
DtsProperty dt_conn = SQLth.Properties["Connection"];
DtsProperty dt_sttp = SQLth.Properties["SqlStatementSourceType"];
DtsProperty dt_stat = SQLth.Properties["SqlStatementSource"];
dt_name.SetValue(SQLth, taskName);
dt_conn.SetValue(SQLth, connManName);
dt_sttp.SetValue(SQLth, sourceType);
est.ResultSetType = resultSetType;
switch (sourceType)
{
case SqlStatementSourceType.DirectInput:
dt_stat.SetValue(SQLth, statement);
break;
case SqlStatementSourceType.Variable:
dt_stat.SetValue(SQLth, statement);
break;
}
return(ex);
}
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);
}
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);
}
