/// <summary>
/// This method evaluate expressions like @([System::TaskName] + [System::TaskID]) or any other operation created using
/// ExpressionBuilder
/// </summary>
/// <param name="mappedParam">The mapped param.</param>
/// <param name="variableDispenser">The variable dispenser.</param>
/// <returns></returns>
public static object EvaluateExpression(string mappedParam, VariableDispenser variableDispenser)
{
Regex regex = new Regex(Keys.REGEX_EMAIL, RegexOptions.IgnoreCase);
if (regex.IsMatch(mappedParam))
{
return(mappedParam);
}
object variableObject;
try
{
var expressionEvaluatorClass = new ExpressionEvaluator
{
Expression = mappedParam
};
expressionEvaluatorClass.Evaluate(DtsConvert.GetExtendedInterface(variableDispenser), out variableObject, false);
}
catch (Exception) // for already initialized values
{
variableObject = mappedParam;
}
return(variableObject);
}
/// <summary>
/// This method evaluate expressions like @([System::TaskName] + [System::TaskID]) or any other operation created using
/// ExpressionBuilder
/// </summary>
/// <param name="mappedParam">The mapped param.</param>
/// <param name="variableDispenser">The variable dispenser.</param>
/// <returns></returns>
public static object EvaluateExpression(string mappedParam, VariableDispenser variableDispenser)
{
object variableObject;
var regex = new Regex(Keys.REGEX_EMAIL, RegexOptions.IgnoreCase);
if (regex.IsMatch(mappedParam))
{
return(mappedParam);
}
if (mappedParam.Contains("@"))
{
var expressionEvaluatorClass = new ExpressionEvaluatorClass
{
Expression = mappedParam
};
expressionEvaluatorClass.Evaluate(DtsConvert.GetExtendedInterface(variableDispenser),
out variableObject,
false);
}
else
{
variableObject = mappedParam;
}
return(variableObject);
}
/// <summary>
/// Method use to encode the flat file
/// </summary>
/// <param name="connections"></param>
/// <param name="variableDispenser"></param>
/// <param name="componentEvents"></param>
/// <returns></returns>
private bool EncodeFile(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents)
{
bool retval = false;
bool refire = false;
try
{
string filePath = SourceType == SourceFileType.FromFileConnector.ToString()
? connections[FileConnector].ConnectionString
: EvaluateExpression(FileSourcePathInVariable, variableDispenser).ToString();
retval = new FileEncodingTools(filePath,
EncodingType,
AutodetectSourceEncodingType,
SourceEncodingType,
ReadWriteBuffer).Encode(componentEvents);
}
catch (Exception exception)
{
componentEvents.FireError(0,
"SSISEncodeFileTask",
string.Format("EncodeFile Error :{0} {1} {2}", exception.Message, exception.Source, exception.StackTrace),
string.Empty,
0);
}
return(retval);
}
/// <summary>
/// Prepares method's parameters that will be executed
/// It will recuperate the direct values
/// It will make the interpretation of the expressions
/// It will pass NULL value for REF Or OUT params
/// </summary>
/// <param name="vars">The vars.</param>
/// <param name="variableDispenser">The variable dispenser.</param>
/// <returns></returns>
private List <object> GetValuedParamsWithoutMethodInfo(Variables vars, VariableDispenser variableDispenser)
{
var objects = new List <object>();
try
{
string[] mappedParams = MappingParams.Split(new[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
objects.AddRange(from mappedParam in mappedParams
let paramInfo = mappedParam.Split(new[] { "|" }, StringSplitOptions.RemoveEmptyEntries)[1]
let paramType = mappedParam.Split(new[] { "|" }, StringSplitOptions.RemoveEmptyEntries)[0].Split(new[] { "=" }, StringSplitOptions.RemoveEmptyEntries)[1]
let paramDirection = mappedParam.Split(new[] { "|" }, StringSplitOptions.RemoveEmptyEntries)[2]
select Convert.ChangeType(EvaluateExpression(paramInfo.Trim(), variableDispenser)
//select Convert.ChangeType(((paramInfo.Contains("@"))
// ? EvaluateExpression(paramInfo.Trim(), variableDispenser)
// : (paramDirection == ParameterDirection.IN)
// ? vars[paramInfo.Trim()].Value
// : vars["@" + paramInfo.Trim()].Value)
, Type.GetType(paramType)));
}
catch (Exception exception)
{
throw new Exception("Check StackTrace " + exception.Message + " :: " + exception.StackTrace);
}
return(objects);
}
public static string RunScript(string scriptText, VariableDispenser variableDispenser)
{
Collection <PSObject> results;
using (Runspace runspace = RunspaceFactory.CreateRunspace())
{
runspace.Open();
using (Pipeline pipeline = runspace.CreatePipeline())
{
pipeline.Commands.AddScript(EvaluateExpression(FormatPowerShellScript(scriptText), variableDispenser).ToString());
pipeline.Commands.Add("Out-String");
results = pipeline.Invoke();
}
runspace.Close();
}
var stringBuilder = new StringBuilder();
if (results != null)
{
foreach (PSObject obj in results)
{
stringBuilder.AppendLine(obj.ToString());
}
}
return(stringBuilder.ToString());
}
/// <summary>
/// Validate local parameters
/// </summary>
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
bool isBaseValid = true;
if (base.Validate(connections, variableDispenser, componentEvents, log) != DTSExecResult.Success)
{
componentEvents.FireError(0, "SSISWebServiceTask", "Base validation failed", "", 0);
isBaseValid = false;
}
if (string.IsNullOrEmpty(ServiceUrl))
{
componentEvents.FireError(0, "SSISWebServiceTask", "An URL is required.", "", 0);
isBaseValid = false;
}
if (string.IsNullOrEmpty(Service))
{
componentEvents.FireError(0, "SSISWebServiceTask", "A service name is required.", "", 0);
isBaseValid = false;
}
if (string.IsNullOrEmpty(WebMethod))
{
componentEvents.FireError(0, "SSISWebServiceTask", "A WebMethod name is required.", "", 0);
isBaseValid = false;
}
return(isBaseValid ? DTSExecResult.Success : DTSExecResult.Failure);
}
private static object EvaluateRecordsetVariable(string mappedParam, VariableDispenser variableDispenser)
{
try
{
var mappedParams = mappedParam.Split(new[] { "@" }, StringSplitOptions.RemoveEmptyEntries);
Variables variables = null;
foreach (string t in mappedParams)
{
try
{
string param = t.Split(new[] { "::" }, StringSplitOptions.RemoveEmptyEntries)[1].Replace("[", string.Empty).Replace("]", string.Empty).Replace("@", string.Empty);
variableDispenser.LockOneForRead(param, ref variables);
object variableObject = variables[0].Value;
return(variableObject);
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
return(null);
}
/// <summary>
/// Executes the reader.
/// </summary>
/// <param name="sql">The SQL.</param>
/// <param name="variableDispenser">The variable dispenser.</param>
/// <param name="connections">The connections.</param>
/// <param name="commandTimeOut">The command time out.</param>
/// <returns></returns>
public List <string> ExecuteReader(string sql, VariableDispenser variableDispenser, Connections connections, int commandTimeOut)
{
var retVal = new List <string>();
using (var sqlConnection = new SqlConnection(connections[SQLServerInstance].ConnectionString))
{
sqlConnection.Open();
using (var sqlCommand = new SqlCommand(sql, sqlConnection)
{
CommandTimeout = commandTimeOut
})
{
using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
{
if (sqlDataReader != null)
{
while (sqlDataReader.Read())
{
if (!sqlDataReader.IsDBNull(0))
{
retVal.Add(sqlDataReader.GetString(0));
}
}
}
}
}
sqlConnection.Close();
}
return(retVal);
}
/// <summary>
/// Validate local parameters
/// </summary>
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser,
IDTSComponentEvents componentEvents, IDTSLogging log)
{
bool isBaseValid = true;
if (base.Validate(connections, variableDispenser, componentEvents, log) != DTSExecResult.Success)
{
componentEvents.FireError(0, "SSISDownloadFileTask", "Base validation failed", "", 0);
isBaseValid = false;
}
if (string.IsNullOrEmpty(HttpConnector))
{
componentEvents.FireError(0, "SSISDownloadFileTask", "A connector is required.", "", 0);
isBaseValid = false;
}
if (string.IsNullOrEmpty(HttpSourceFile))
{
componentEvents.FireError(0, "SSISDownloadFileTask", "A source file (direct file URL) is required.", "", 0);
isBaseValid = false;
}
if (string.IsNullOrEmpty(LocalDestinationFile))
{
componentEvents.FireError(0, "SSISDownloadFileTask", "A destination file (local path) is required.", "", 0);
isBaseValid = false;
}
return(isBaseValid ? DTSExecResult.Success : DTSExecResult.Failure);
}
/// <summary>
/// Validate local parameters
/// </summary>
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser,
IDTSComponentEvents componentEvents, IDTSLogging log)
{
bool isBaseValid = true;
if (base.Validate(connections, variableDispenser, componentEvents, log) != DTSExecResult.Success)
{
componentEvents.FireError(0, "SSISReportGeneratorTask", "Base validation failed", "", 0);
isBaseValid = false;
}
if (string.IsNullOrEmpty(ReportServer))
{
componentEvents.FireError(0, "SSISReportGeneratorTask", "Please specify a Report Server URL.", "", 0);
isBaseValid = false;
}
if (string.IsNullOrEmpty(ReportPath))
{
componentEvents.FireError(0, "SSISReportGeneratorTask", "Please specify a path to your report.", "", 0);
isBaseValid = false;
}
if (string.IsNullOrEmpty(ReportName))
{
componentEvents.FireError(0, "SSISReportGeneratorTask", "Please specify the report name.", "", 0);
isBaseValid = false;
}
if (string.IsNullOrEmpty(OutPutType))
{
componentEvents.FireError(0, "SSISReportGeneratorTask", "Please specify the output file type.", "", 0);
isBaseValid = false;
}
if (string.IsNullOrEmpty(FileSourceType))
{
componentEvents.FireError(0, "SSISReportGeneratorTask", "You didn't specify the output type version : by File Connector or by Variable/Expression.", "", 0);
isBaseValid = false;
}
if (string.IsNullOrEmpty(DestinationFile))
{
componentEvents.FireError(0, "SSISReportGeneratorTask", "Please specify a destination file path of the generated report.", "", 0);
isBaseValid = false;
}
if (SendFileByEmail == Keys.TRUE && (string.IsNullOrEmpty(SmtpFrom) || string.IsNullOrEmpty(SmtpRecipients)))
{
componentEvents.FireError(0, "SSISReportGeneratorTask", "Please specify the minimal elements to send the email: the Sender and the Recipient(s) addresses.", "", 0);
isBaseValid = false;
}
//bool refire = false;
//GenerateReport(connections, variableDispenser, componentEvents, refire);
return(isBaseValid ? DTSExecResult.Success : DTSExecResult.Failure);
}
/// <summary>
/// Validate properties
/// </summary>
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser,
IDTSComponentEvents componentEvents, IDTSLogging log)
{
bool isBaseValid = true;
if (base.Validate(connections, variableDispenser, componentEvents, log) != DTSExecResult.Success)
{
componentEvents.FireError(0, "SSISEncodeFileTask", "Base validation failed", "", 0);
isBaseValid = false;
}
if (EncodingType < 1)
{
componentEvents.FireError(0, "SSISEncodeFileTask", "An encode type is required.", "", 0);
isBaseValid = false;
}
if (string.IsNullOrEmpty(SourceType))
{
componentEvents.FireError(0, "SSISEncodeFileTask", "A source type is required.", "", 0);
isBaseValid = false;
}
if (!AutodetectSourceEncodingType && SourceEncodingType < 1)
{
componentEvents.FireError(0, "SSISEncodeFileTask",
"if AutodetectSourceEncodingType is true, please specify an initial encoding.",
"", 0);
isBaseValid = false;
}
if (SourceFileType.FromFileConnector.ToString() == SourceType)
{
if (string.IsNullOrEmpty(FileConnector))
{
componentEvents.FireError(0, "SSISEncodeFileTask", "A FILE connector is required.", "", 0);
isBaseValid = false;
}
}
else
{
if (string.IsNullOrEmpty(FileSourcePathInVariable))
{
componentEvents.FireError(0, "SSISEncodeFileTask", "A file path is required.", "", 0);
isBaseValid = false;
}
}
if (ReadWriteBuffer < 1)
{
componentEvents.FireError(0, "SSISEncodeFileTask", "The ReadWriteBuffer property cannot be less than 1.", "", 0);
isBaseValid = false;
}
//var trans = new object();
//ProxyExecute(connections, variableDispenser, componentEvents, log, trans);
return(isBaseValid ? DTSExecResult.Success : DTSExecResult.Failure);
}
/// <summary>
/// This method is a run-time method executed dtsexec.exe
/// </summary>
/// <param name="connections"></param>
/// <param name="variableDispenser"></param>
/// <param name="componentEvents"></param>
/// <param name="log"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
bool refire = false;
GenerateReport(connections, variableDispenser, componentEvents, refire);
return(base.Execute(connections, variableDispenser, componentEvents, log, transaction));
}
/// <summary>
/// Gets the variable dispenser wrapper. This method itself is just an abstraction for the condition compilation code.
/// </summary>
/// <param name="variableDispenser">The variable dispenser.</param>
/// <returns>A version typed variable dispamser wrapper.</returns>
private static IDTSVariableDispenserXX GetVariableDispenserWrapper(VariableDispenser variableDispenser)
{
#if YUKON
IDTSVariableDispenserXX variableDispenserWrapper = DtsConvert.ToVariableDispenser90(variableDispenser);
#else // KATMAI, DENALI, SQL2014, 2016
IDTSVariableDispenserXX variableDispenserWrapper = DtsConvert.GetExtendedInterface(variableDispenser);
#endif
return(variableDispenserWrapper);
}
/// <summary>
/// This method is a run-time method executed dtsexec.exe
/// </summary>
/// <param name="connections"></param>
/// <param name="variableDispenser"></param>
/// <param name="componentEvents"></param>
/// <param name="log"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
bool refire = false;
GetNeededVariables(variableDispenser, HttpSourceFile);
GetNeededVariables(variableDispenser, LocalDestinationFile);
componentEvents.FireInformation(0,
"SSISDownloadFileTask",
string.Format("Source File: \"{0}\", Destination File \"{1}\"",
EvaluateExpression(HttpSourceFile, variableDispenser),
EvaluateExpression(LocalDestinationFile, variableDispenser)),
string.Empty,
0,
ref refire);
try
{
componentEvents.FireInformation(0,
"SSISDownloadFileTask",
"Prepare variables",
string.Empty,
0,
ref refire);
componentEvents.FireInformation(0,
"SSISDownloadFileTask",
DownloadFile(connections, variableDispenser, componentEvents)
? "The file has been downloaded successfully."
: "The file has NOT been downloaded.",
string.Empty,
0,
ref refire);
}
catch (Exception ex)
{
componentEvents.FireError(0,
"SSISDownloadFileTask",
string.Format("Problem: {0}",
ex.Message),
"",
0);
}
finally
{
if (_vars.Locked)
{
_vars.Unlock();
}
}
return(base.Execute(connections, variableDispenser, componentEvents, log, transaction));
}
/// <summary>
/// Validate local parameters
/// </summary>
/// <param name="connections">A collection of connections used by the task.</param>
/// <param name="variableDispenser">A <see cref="T:Microsoft.SqlServer.Dts.Runtime.VariableDispenser"/> object for locking variables.</param>
/// <param name="componentEvents">An object that implements the <see cref="T:Microsoft.SqlServer.Dts.Runtime.IDTSComponentEvents"/> interface.</param>
/// <param name="log">An object that implements the <see cref="T:Microsoft.SqlServer.Dts.Runtime.IDTSLogging"/> interface.</param>
/// <returns>
/// A value from the <see cref="T:Microsoft.SqlServer.Dts.Runtime.DTSExecResult"/> enumeration.
/// </returns>
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
bool isBaseValid = true;
if (base.Validate(connections, variableDispenser, componentEvents, log) != DTSExecResult.Success)
{
componentEvents.FireError(0, "SSISExecuteAssemblyTask", "Base validation failed", "", 0);
isBaseValid = false;
}
if (!File.Exists(connections[AssemblyConnector].ConnectionString.Trim()))
{
componentEvents.FireError(0, "SSISExecuteAssemblyTask", "Assembly doesn't exists at the specified path", "", 0);
isBaseValid = false;
}
if (string.IsNullOrEmpty(AssemblyConnector))
{
componentEvents.FireError(0, "SSISExecuteAssemblyTask", "A connector is required.", "", 0);
isBaseValid = false;
}
if (string.IsNullOrEmpty(AssemblyNamespace))
{
componentEvents.FireError(0, "SSISExecuteAssemblyTask", "A namespace is required.", "", 0);
isBaseValid = false;
}
if (string.IsNullOrEmpty(AssemblyClass))
{
componentEvents.FireError(0, "SSISExecuteAssemblyTask", "A class is required.", "", 0);
isBaseValid = false;
}
if (string.IsNullOrEmpty(AssemblyMethod))
{
componentEvents.FireError(0, "SSISExecuteAssemblyTask", "A method to execute is required.", "", 0);
isBaseValid = false;
}
if (ConfigurationType != SSISExecuteAssemblyTask100.ConfigurationType.NO_CONFIGURATION)
{
if (string.IsNullOrEmpty(ConfigurationType))
{
componentEvents.FireError(0, "SSISExecuteAssemblyTask", "A method to execute is required.", "", 0);
isBaseValid = false;
}
}
return(isBaseValid ? DTSExecResult.Success : DTSExecResult.Failure);
}
private DTSExecResult ProxyExecute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
bool refire = false;
componentEvents.FireInformation(0,
"SSISEncodeFileTask",
"Prepare variables",
string.Empty,
0,
ref refire);
if (!string.IsNullOrEmpty(FileSourcePathInVariable))
{
GetNeededVariables(variableDispenser, FileSourcePathInVariable);
}
try
{
componentEvents.FireInformation(0,
"SSISEncodeFileTask",
EncodeFile(connections, variableDispenser, componentEvents)
? "The file has been encoded successfully."
: "The file has NOT been encoded.",
string.Empty,
0,
ref refire);
}
catch (Exception ex)
{
componentEvents.FireError(0,
"SSISEncodeFileTask",
string.Format("Problem: {0}", ex.Message),
string.Empty,
0);
return(DTSExecResult.Failure);
}
finally
{
if (_vars != null)
{
if (_vars.Locked)
{
_vars.Unlock();
}
}
}
return(base.Execute(connections, variableDispenser, componentEvents, log, transaction));
}
private void Executing(VariableDispenser variableDispenser, IDTSComponentEvents componentEvents)
{
bool refire = false;
try
{
PowerShellCoreScripting.GetNeededVariables(variableDispenser, ref _vars, componentEvents, this);
componentEvents.FireInformation(0, "SSISPowerShell", String.Format("Launching PowerShell Script: {0} ", EvaluateExpression(PowerShellScript, variableDispenser)), string.Empty, 0, ref refire);
var futureDevis = System.Threading.Tasks.Task.Factory.StartNew(() => PowerShellCoreScripting.RunScript(PowerShellScript, variableDispenser));
System.Threading.Tasks.Task.WaitAll(futureDevis);
string retVal = futureDevis.Result.Trim();
componentEvents.FireInformation(0, "SSISPowerShell", String.Format("Execution of PowerShell Script succeeded"), string.Empty, 0, ref refire);
componentEvents.FireInformation(0, "SSISPowerShell", string.Format("The result: {0}", retVal), string.Empty, 0, ref refire);
componentEvents.FireInformation(0, "SSISPowerShell", string.Format("Attach result to the output variable: {0}", OutputVariableName), string.Empty, 0, ref refire);
string output = PowerShellCoreScripting.GetVariableFromNamespaceContext(OutputVariableName).Trim();
try
{
_vars[output].Value = retVal;
}
catch (Exception exception)
{
componentEvents.FireError(0, "SSISPowerShell", string.Format("Error : {0}", exception.Message), "", 0);
}
}
catch (Exception ex)
{
componentEvents.FireError(0, "SSISPowerShell", string.Format("Error : {0}", ex.Message), "", 0);
}
finally
{
if (_vars != null)
{
if (_vars.Locked != null)
{
if (_vars.Locked)
{
_vars.Unlock();
}
}
}
}
}
/// <summary>
/// This method evaluate expressions like @([System::TaskName] + [System::TaskID]) or any other operation created using
/// ExpressionBuilder
/// </summary>
/// <param name="mappedParam"></param>
/// <param name="variableDispenser"></param>
/// <returns></returns>
private static object EvaluateExpression(string mappedParam, VariableDispenser variableDispenser)
{
object variableObject = null;
try
{
var expressionEvaluatorClass = new ExpressionEvaluatorClass
{
Expression = mappedParam
};
expressionEvaluatorClass.Evaluate(DtsConvert.GetExtendedInterface(variableDispenser), out variableObject, false);
}
catch
{
variableObject = mappedParam;
}
return(variableObject);
}
private void GetNeededVariables(VariableDispenser variableDispenser, string variableExpression)
{
try
{
var mappedParams = variableExpression.Split(new[] { "@" }, StringSplitOptions.RemoveEmptyEntries);
for (int index = 0; index < mappedParams.Length - 1; index++)
{
var param = mappedParams[index].Split(new[] { "::" }, StringSplitOptions.RemoveEmptyEntries)[1];
variableDispenser.LockForRead(param.Substring(0, param.IndexOf(']')));
}
}
catch
{
//We will continue...
}
variableDispenser.GetVariables(ref _vars);
}
/// <summary>
/// This method evaluate expressions like @([System::TaskName] + [System::TaskID]) or any other operation created using
/// ExpressionBuilder
/// </summary>
/// <param name="mappedParam"></param>
/// <param name="variableDispenser"></param>
/// <returns></returns>
private static object EvaluateExpression(string mappedParam, VariableDispenser variableDispenser)
{
object variableObject;
try
{
var expressionEvaluatorClass = new ExpressionEvaluator
{
Expression = mappedParam
};
expressionEvaluatorClass.Evaluate(DtsConvert.GetExtendedInterface(variableDispenser), out variableObject, false);
}
catch (Exception) // for already initialized values
{
variableObject = mappedParam;
}
return(variableObject);
}
/// <summary>
/// Fill the ConfigurationFile of AppDomain property if a ConnectionFile Property is specified
/// </summary>
/// <param name="variableDispenser">The variable dispenser.</param>
/// <param name="connections">The connections.</param>
/// <param name="appDomainSetup">The app domain setup.</param>
private void GetConfigurationFile(VariableDispenser variableDispenser, Connections connections, AppDomainSetup appDomainSetup)
{
if (ConfigurationType != SSISExecuteAssemblyTask100.ConfigurationType.NO_CONFIGURATION)
{
string configurationFile = string.Empty;
if (ConfigurationType == SSISExecuteAssemblyTask100.ConfigurationType.FILE_CONNECTOR)
{
configurationFile = connections[ConfigurationFile].ConnectionString;
}
if (ConfigurationType == SSISExecuteAssemblyTask100.ConfigurationType.TASK_VARIABLE)
{
configurationFile = (ConfigurationFile.Contains('@'))
? EvaluateExpression(ConfigurationFile, variableDispenser).ToString()
: _vars[ConfigurationFile].Value.ToString();
}
appDomainSetup.ConfigurationFile = configurationFile;
}
}
private static void LockForWrite(VariableDispenser variableDispenser, ref Variables variables, List <string> lockForReadWrite, IEnumerable <string> mappedParams, IDTSComponentEvents componentEvents)
{
bool refire = false;
foreach (string t in mappedParams)
{
try
{
string param = t.Split(new[] { "::" }, StringSplitOptions.RemoveEmptyEntries)[1].Replace("[", string.Empty).Replace("]", string.Empty).Replace("@", string.Empty);
if (!IsVariableInLockForReadOrWrite(lockForReadWrite, param))
{
componentEvents.FireInformation(0, "SSISPowerShell", String.Format("LockForWrite: {0} ", param), string.Empty, 0, ref refire);
variableDispenser.LockOneForWrite(param, ref variables);
}
}
catch (Exception)
{
componentEvents.FireInformation(0, "SSISPowerShell", String.Format("Unable to LockForWrite: {0} ", t), string.Empty, 0, ref refire);
}
}
}
/// <summary>
/// Executes the non SQL.
/// </summary>
/// <param name="sql">The SQL.</param>
/// <param name="variableDispenser">The variable dispenser.</param>
/// <param name="connections">The connections.</param>
/// <param name="commandTimeOut">The command time out.</param>
/// <returns></returns>
public int ExecuteNonSQL(string sql, VariableDispenser variableDispenser, Connections connections, int commandTimeOut)
{
int retVal;
using (var sqlConnection = new SqlConnection(connections[SQLServerInstance].ConnectionString))
{
sqlConnection.Open();
using (var sqlCommand = new SqlCommand(sql, sqlConnection)
{
CommandTimeout = commandTimeOut
})
{
retVal = sqlCommand.ExecuteNonQuery();
}
sqlConnection.Close();
}
return(retVal);
}
/// <summary>
/// Validate local parameters
/// </summary>
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
bool isBaseValid = true;
if (base.Validate(connections, variableDispenser, componentEvents, log) != DTSExecResult.Success)
{
componentEvents.FireError(0, "SSISBulkExportTask", "Base validation failed", "", 0);
isBaseValid = false;
}
if (string.IsNullOrEmpty(SQLServerInstance))
{
componentEvents.FireError(0, "SSISBulkExportTask", "A SQL Server Connection is mandatory", "", 0);
isBaseValid = false;
}
if (string.IsNullOrEmpty(DestinationPath))
{
componentEvents.FireError(0, "SSISBulkExportTask", "Destination Path is mandatory ", "", 0);
isBaseValid = false;
}
if (string.IsNullOrEmpty(SQLStatment) &&
string.IsNullOrEmpty(View) &&
string.IsNullOrEmpty(Tables) &&
string.IsNullOrEmpty(StoredProcedure))
{
componentEvents.FireError(0, "SSISBulkExportTask", "You must specify an SQL Select Statelment / a View name / a Stored Procedure name / a Table Name", "", 0);
isBaseValid = false;
}
if (SendFileByEmail == Keys.TRUE && (string.IsNullOrEmpty(SmtpFrom) || string.IsNullOrEmpty(SmtpRecipients)))
{
componentEvents.FireError(0, "SSISReportGeneratorTask", "Please specify the minimal elements to send the email: the Sender and the Recipient(s) addresses.", "", 0);
isBaseValid = false;
}
return(isBaseValid ? DTSExecResult.Success : DTSExecResult.Failure);
}
private bool DownloadFile(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents)
{
bool retVal = false;
try
{
object connInfoObject = connections[HttpConnector].AcquireConnection(null);
if (connInfoObject == null)
{
throw new Exception(string.Format("Cannot Acquire HTTP Connection To the File {0}", HttpSourceFile));
}
var httpClientConnection = new HttpClientConnection(connInfoObject)
{
ServerURL = (string)EvaluateExpression(HttpSourceFile, variableDispenser)
};
httpClientConnection.DownloadFile((string)EvaluateExpression(LocalDestinationFile, variableDispenser), true);
if (File.Exists((string)EvaluateExpression(LocalDestinationFile, variableDispenser)))
{
retVal = true;
}
}
catch (Exception exception)
{
retVal = false;
componentEvents.FireError(0,
"SSISDownloadFileTask",
string.Format("Problem: {0}",
exception.Message),
"",
0);
}
return(retVal);
}
public static void GetNeededVariables(VariableDispenser variableDispenser, ref Variables variables, IDTSComponentEvents componentEvents, SSISPowerShellTask ssisPowerShellTask)
{
bool refire = false;
var lockForReadWrite = new List <string>();
{
var regex = new Regex(@"\@(.*?)\]");
componentEvents.FireInformation(0, "SSISPowerShell", String.Format("Search variable to LockForRead "), string.Empty, 0, ref refire);
foreach (var match in regex.Matches(ssisPowerShellTask.PowerShellScript))
{
componentEvents.FireInformation(0, "SSISPowerShell", String.Format("Match : {0} ", match), string.Empty, 0, ref refire);
var mappedParams = match.ToString().Split(new[] { "@" }, StringSplitOptions.RemoveEmptyEntries);
componentEvents.FireInformation(0, "SSISPowerShell", String.Format("mappedParams : {0} ", mappedParams), string.Empty, 0, ref refire);
LockForRead(variableDispenser, ref variables, lockForReadWrite, mappedParams, componentEvents);
}
}
{
var mappedParams = ssisPowerShellTask.OutputVariableName.Split(new[] { "@" }, StringSplitOptions.RemoveEmptyEntries);
LockForWrite(variableDispenser, ref variables, lockForReadWrite, mappedParams, componentEvents);
}
}
/// <summary>
/// Prepares method's parameters that will be executed
/// It will recuperate the direct values
/// It will make the interpretation of the expressions
/// It will pass NULL value for REF Or OUT params
/// </summary>
/// <param name="vars">The vars.</param>
/// <param name="variableDispenser">The variable dispenser.</param>
/// <param name="methodInfo">The method info.</param>
/// <returns></returns>
private object[] GetValuedParams(Variables vars, VariableDispenser variableDispenser, MethodInfo methodInfo)
{
object[] objects;
try
{
ParameterInfo[] parameterInfos = methodInfo.GetParameters();
objects = new object[parameterInfos.Length];
int paramIndex = 0;
foreach (ParameterInfo parameterInfo in parameterInfos)
{
var mappedParams = MappingParams.Split(new[] { ";" }, StringSplitOptions.RemoveEmptyEntries).ToArray();
foreach (string param in mappedParams.Where(param => param.Length > 0).Where(param => param.Split('|')[0].Split('=')[0].Trim() == parameterInfo.Name))
{
objects[paramIndex] = EvaluateExpression(param.Split('|')[1], variableDispenser);
//param.Split('|')[1].Contains("@")
// ? EvaluateExpression(param.Split('|')[1], variableDispenser)
// : (!(parameterInfo.ParameterType.IsByRef || parameterInfo.IsOut))
// ? vars[param.Split('|')[1].Trim()].Value
// : null;
paramIndex++;
break;
}
}
}
catch (Exception exception)
{
throw new Exception(exception.StackTrace);
}
return(objects);
}
/// <summary>
/// Validate local parameters
/// </summary>
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
bool isBaseValid = true;
if (base.Validate(connections, variableDispenser, componentEvents, log) != DTSExecResult.Success)
{
componentEvents.FireError(0, "SSISPowerShellTask", "Base validation failed", "", 0);
isBaseValid = false;
}
if (string.IsNullOrEmpty(PowerShellScript))
{
componentEvents.FireError(0, "SSISPowerShellTask", "Input PowerShell script is missing.", "", 0);
isBaseValid = false;
}
/*else if (string.IsNullOrEmpty(OutputVariableName))
* {
* componentEvents.FireError(0, "SSISPowerShellTask", "Output variable specified.", "", 0);
* isBaseValid = false;
* }*/
return(isBaseValid ? DTSExecResult.Success : DTSExecResult.Failure);
}
/// <summary>
/// Gets the needed variables.
/// </summary>
/// <param name="variableDispenser">The variable dispenser.</param>
/// <param name="componentEvents">The component events.</param>
private void GetNeededVariables(VariableDispenser variableDispenser, IDTSComponentEvents componentEvents)
{
bool refire = false;
List <string> lockForRead = new List <string>();
try
{
var param = ServiceUrl;
componentEvents.FireInformation(0, "SSISWebServiceTask", "ServiceUrl = " + ServiceUrl, string.Empty, 0, ref refire);
if (param.Contains("@"))
{
var regexStr = param.Split('@');
foreach (var nexSplitedVal in regexStr.Where(val => val.Trim().Length != 0).Select(strVal => strVal.Split(new[] { "::" }, StringSplitOptions.RemoveEmptyEntries)))
{
try
{
componentEvents.FireInformation(0, "SSISWebServiceTask", nexSplitedVal[1].Remove(nexSplitedVal[1].IndexOf(']')), string.Empty, 0, ref refire);
if (!IsVariableInLockForReadOrWrite(lockForRead, nexSplitedVal[1].Remove(nexSplitedVal[1].IndexOf(']'))))
{
variableDispenser.LockForRead(nexSplitedVal[1].Remove(nexSplitedVal[1].IndexOf(']')));
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
}
}
catch
{
//We will continue...
}
try
{
var param = Service;
componentEvents.FireInformation(0, "SSISWebServiceTask", "Service = " + Service, string.Empty, 0, ref refire);
if (param.Contains("@"))
{
var regexStr = param.Split('@');
foreach (var nexSplitedVal in regexStr.Where(val => val.Trim().Length != 0).Select(strVal => strVal.Split(new[] { "::" }, StringSplitOptions.RemoveEmptyEntries)))
{
try
{
componentEvents.FireInformation(0, "SSISWebServiceTask", nexSplitedVal[1].Remove(nexSplitedVal[1].IndexOf(']')), string.Empty, 0, ref refire);
if (!IsVariableInLockForReadOrWrite(lockForRead, nexSplitedVal[1].Remove(nexSplitedVal[1].IndexOf(']'))))
{
variableDispenser.LockForRead(nexSplitedVal[1].Remove(nexSplitedVal[1].IndexOf(']')));
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
try
{
var param = WebMethod;
componentEvents.FireInformation(0, "SSISWebServiceTask", "WebMethod = " + WebMethod, string.Empty, 0, ref refire);
if (param.Contains("@"))
{
var regexStr = param.Split('@');
foreach (var nexSplitedVal in regexStr.Where(val => val.Trim().Length != 0).Select(strVal => strVal.Split(new[] { "::" }, StringSplitOptions.RemoveEmptyEntries)))
{
try
{
componentEvents.FireInformation(0, "SSISWebServiceTask", nexSplitedVal[1].Remove(nexSplitedVal[1].IndexOf(']')), string.Empty, 0, ref refire);
if (!IsVariableInLockForReadOrWrite(lockForRead, nexSplitedVal[1].Remove(nexSplitedVal[1].IndexOf(']'))))
{
variableDispenser.LockForRead(nexSplitedVal[1].Remove(nexSplitedVal[1].IndexOf(']')));
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
try
{
if (!string.IsNullOrEmpty(ReturnedValue))
{
var param = ReturnedValue;
componentEvents.FireInformation(0, "SSISWebServiceTask", "ReturnedValue = " + ReturnedValue,
string.Empty, 0, ref refire);
if (param.Contains("@"))
{
var regexStr = param.Split('@');
foreach (var nexSplitedVal in regexStr.Where(val => val.Trim().Length != 0).Select(strVal => strVal.Split(new[] { "::" }, StringSplitOptions.RemoveEmptyEntries)))
{
try
{
componentEvents.FireInformation(0, "SSISWebServiceTask",
nexSplitedVal[1].Remove(nexSplitedVal[1].IndexOf(']')),
string.Empty, 0, ref refire);
if (!IsVariableInLockForReadOrWrite(lockForRead, nexSplitedVal[1].Remove(nexSplitedVal[1].IndexOf(']'))))
{
variableDispenser.LockForWrite(nexSplitedVal[1].Remove(nexSplitedVal[1].IndexOf(']')));
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
}
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
try
{
componentEvents.FireInformation(0, "SSISWebServiceTask", "MappingParams ", string.Empty, 0, ref refire);
//Get variables for MappingParams
foreach (var mappingParams in (MappingParams)MappingParams)
{
try
{
if (mappingParams.Value.Contains("@"))
{
var regexStr = mappingParams.Value.Split('@');
foreach (var nexSplitedVal in
regexStr.Where(val => val.Trim().Length != 0).Select(strVal => strVal.Split(new[] { "::" }, StringSplitOptions.RemoveEmptyEntries)))
{
try
{
componentEvents.FireInformation(0, "SSISWebServiceTask", nexSplitedVal[1].Remove(nexSplitedVal[1].IndexOf(']')), string.Empty, 0, ref refire);
if (!IsVariableInLockForReadOrWrite(lockForRead, nexSplitedVal[1].Remove(nexSplitedVal[1].IndexOf(']'))))
{
variableDispenser.LockForRead(nexSplitedVal[1].Remove(nexSplitedVal[1].IndexOf(']')));
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
}
catch (Exception ex)
{
componentEvents.FireError(0, "SSISReportGeneratorTask", string.Format("Problem MappingParams: {0} {1}", ex.Message, ex.StackTrace), "", 0);
}
variableDispenser.GetVariables(ref _vars);
}
/// <summary>
/// This method is a run-time method executed dtsexec.exe
/// </summary>
/// <param name="connections"></param>
/// <param name="variableDispenser"></param>
/// <param name="componentEvents"></param>
/// <param name="log"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
Executing(variableDispenser, componentEvents);
return(base.Execute(connections, variableDispenser, componentEvents, log, transaction));
}
