/// <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);
}
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
Events = componentEvents;
Conns = connections;
DTSExecResult execResult = base.Validate(connections, variableDispenser, componentEvents, log);
if (execResult == DTSExecResult.Success)
{
if (!DoNothingAndContinue)
{
if (HasSQLCheckStatement && !HasSQLConnection)
{
FireError("An Sql check statement is defined, aber no connection exists. Please select a valid ADO.NET or OLEDB connection or leave the sql check statement blank.");
}
if (HasSQLConnection && HasSQLCheckStatement && !HasMaximumSQLWaitTime)
{
FireWarning("A Sql check statement and connection exists, but no maximum wait time was entered. The default of 24h is used.");
}
if (HasSQLConnection && HasSQLCheckStatement && !HasSQLRepetitionFrequencyInMinutes)
{
FireWarning("A Sql check statement and connection exists, but no Sql repetition frequency was entered. The SQL statement will be fired on the database every second. This can cause performance issues.");
}
}
}
return(execResult);
}
private void _Check_GZ(string pathFileTAR, IDTSComponentEvents componentEvents)
{
bool b = false;
if (_testarchive)
{
using (ICSharpCode.SharpZipLib.GZip.GZipInputStream gzip = new ICSharpCode.SharpZipLib.GZip.GZipInputStream(System.IO.File.OpenRead(pathFileTAR)))
{
try
{
componentEvents.FireInformation(1, "UnZip SSIS", "Start verify file GZ (" + _folderSource + _fileZip + ")", null, 0, ref b);
gzip.CopyTo(System.IO.MemoryStream.Null);
gzip.Flush();
componentEvents.FireInformation(1, "UnZip SSIS", "File ZIP verified GZ (" + _folderSource + _fileZip + ")", null, 0, ref b);
}
catch (Exception ex)
{
throw new Exception("Verify file: " + _fileZip + " failed. (" + ex.Message + ")");
}
gzip.Close();
}
}
}
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
System.Threading.Thread.Sleep(this._interval * 60000);
return DTSExecResult.Success;
}
/// <summary>
/// Resolves the variable placeholders in message text and returnes the resolved message
/// Variable placeholders will be lookedup and the value will be inserted in the placeholder
/// </summary>
/// <param name="variableDispenser">the ssis variable dispenser to access runtime variables</param>
/// <param name="componentEvents"></param>
/// <param name="messageText">the message text possibly containing variable placeholder</param>
/// <returns>returns the resolved message where all variable placeholders are replaced by their correspoding values</returns>
public string GetMessageText(VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, string messageText)
{
try
{
if (messageText != "")
{
while (messageText.Contains("@("))
{
int start = messageText.IndexOf("@(", 0);
int end = messageText.IndexOf(")", start);
string varName = messageText.Substring(start + 2, end - start - 2);
messageText = messageText.Replace("@(" + varName + ")", GetValueFromVariable(variableDispenser, varName));
}
}
}
catch (Exception ex)
{
componentEvents.FireError(0, "", "Fehler beim Anfordern eines Variablen-Wertes: " + ex.Message, "", 0);
throw ex;
}
return(messageText);
}
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction) {
try {
var connectionManager = connections[ConnectionName];
var connection = connectionManager.AcquireConnection(transaction) as SqlConnection;
var dropKeysScript = Resources.DropCreateFKScriptCommon.Replace(SELECT_STATEMENT_PLACEHOLDER, Resources.CreateFKScript);
var command = new SqlCommand(dropKeysScript, connection);
var reader = command.ExecuteReader();
var scriptBuilder = new StringBuilder();
while (reader.Read())
{
var dropScript = reader.GetString(reader.GetOrdinal("CreateScript"));
//var sourceTableName = reader.GetString(reader.GetOrdinal("SourceTableName"));
//var targetTableName = reader.GetString(reader.GetOrdinal("TargetTableName"));
scriptBuilder.AppendLine(dropScript);
}
if (File.Exists(TargetPath))
{
File.Delete(TargetPath);
}
File.WriteAllText(TargetPath, scriptBuilder.ToString());
return DTSExecResult.Success;
} catch (Exception ex) {
log.Write(string.Format("{0}.Execute", GetType().FullName), ex.ToString());
return DTSExecResult.Failure;
}
}
/// <summary>
/// Validates the specified connections.
/// </summary>
/// <param name="connections">The connections.</param>
/// <param name="variableDispenser">The variable dispenser.</param>
/// <param name="componentEvents">The component events.</param>
/// <param name="log">The log.</param>
/// <returns></returns>
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
try
{
string _sourceFile = Common.GetVariableValue<string>(this.sourceFile, variableDispenser);
string _targetFile = Common.GetVariableValue<string>(this.targetFile, variableDispenser);
string errorMsg = String.Empty;
if (String.IsNullOrEmpty(_sourceFile))
errorMsg += " Source,";
if (String.IsNullOrEmpty(_targetFile))
errorMsg += " Target,";
if (errorMsg.Trim().Length > 0)
{
componentEvents.FireError(0, "", "Missing:" + errorMsg.Remove(errorMsg.Length - 1) + ".", "", 0);
return DTSExecResult.Failure;
}
return base.Validate(connections, variableDispenser, componentEvents, log);
}
catch (Exception ex)
{
componentEvents.FireError(0, "Validate: ", ex.Message + Environment.NewLine + ex.StackTrace, "", 0);
return DTSExecResult.Failure;
}
}
/// <summary>
/// Overrides Execute logic
/// Core Logic: It Fires a Custom Event, with the given ComponentType and Message
/// <see cref="Microsoft.SqlServer.Dts.Runtime.Task.Execute"/>
/// </summary>
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
bool refFire = false;
componentEvents.FireInformation(ComponentType, "", GetMessageText(variableDispenser, componentEvents, MessageText), "", 0, ref refFire);
return base.Execute(connections, variableDispenser, componentEvents, log, transaction);
}
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
Events = componentEvents;
Conns = connections;
Transaction = transaction;
DTSExecResult execResult = DTSExecResult.Success;
if (!DoNothingAndContinue)
{
try
{
SleepUntilWaitTime();
SleepSomeMinutes();
WaitForSQL();
}
catch
{
execResult = DTSExecResult.Failure;
}
}
else
{
FireInformation("DoNothingAndContinueExecution is set to true - task will do nothing and continue.");
}
return(execResult);
}
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
return (
connections.Contains(ConnectionName)
&& !string.IsNullOrWhiteSpace(TargetPath)
) ? 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);
}
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
DTSExecResult execResult = DTSExecResult.Success;
System.Windows.Forms.MessageBox.Show(this.displayText);
return execResult;
}
/// <summary>Logs an informational message.</summary>
/// <param name="source">The source.</param>
/// <param name="action">The action to produce the message.</param>
public static void LogInformation(this IDTSComponentEvents source, Action <LogInfoBuilder> action)
{
var builder = CreateLogInfoBuilder(LogType.Information);
action(builder);
LogCore(source, builder.GetInfo());
}
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
ConnectionManager cSSH = getConnectionManager(connections, _SSHconnMgrName);
List<KeyValuePair<string, string>> connParams = (List<KeyValuePair<string, string>>)cSSH.AcquireConnection(transaction);
string host = connParams.Find(t => t.Key == "Host").Value;
string username = connParams.Find(t => t.Key == "Username").Value;
string password = connParams.Find(t => t.Key == "Password").Value;
int port = Convert.ToInt32(connParams.Find(t => t.Key == "Port").Value);
if (_operation == SSHFTPOperation.SendFiles)
{
ConnectionManager cSend = getConnectionManager(connections, _sendFilesSourceConnectionManagerName);
string sourceFile = cSend.AcquireConnection(transaction).ToString();
SshTransferProtocolBase sshCp;
sshCp = new Sftp(host, username);
sshCp.Password = password;
string localFile = sourceFile;
string remoteFile = getRemotePathAndFile(_sendFilesDestinationDirectory, Path.GetFileName(sourceFile));
try
{
sshCp.Connect();
sshCp.Put(localFile, remoteFile);
}
catch
{
throw;
}
finally
{
sshCp.Close();
}
}
if (_operation == SSHFTPOperation.ReceiveFiles)
{
ConnectionManager cReceive = getConnectionManager(connections, _receiveFilesDestinationConnectionManagerName);
string destinationDirectory = cReceive.AcquireConnection(transaction).ToString();
SshTransferProtocolBase sshCp;
sshCp = new Sftp(host, username);
sshCp.Password = password;
try
{
sshCp.Connect();
sshCp.Get(_receiveFilesSourceFile, destinationDirectory);
}
catch
{
throw;
}
finally
{
sshCp.Close();
}
}
return DTSExecResult.Success;
}
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
if (this._interval <= 0)
{
componentEvents.FireError(0, "", "Pause length must be greater than 0.", "", 0);
return DTSExecResult.Failure;
}
return DTSExecResult.Success;
}
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log) {
return (
!string.IsNullOrWhiteSpace(Host)
&& Port > 0
&& !string.IsNullOrWhiteSpace(Username)
&& !string.IsNullOrWhiteSpace(Password)
&& !string.IsNullOrWhiteSpace(SourcePath)
&& !string.IsNullOrWhiteSpace(TargetPath)
) ? DTSExecResult.Success : DTSExecResult.Failure;
}
private DTSExecResult ValidateProperties(ref IDTSComponentEvents componentEvents)
{
DTSExecResult result = DTSExecResult.Success;
if (String.IsNullOrEmpty(this.FtpProtocolName))
{
componentEvents.FireError(0, TASK_NAME, FtpProtocolName_MISSING_MESAGE, String.Empty, 0);
result = DTSExecResult.Failure;
}
if (String.IsNullOrEmpty(this.FtpHostName))
{
componentEvents.FireError(0, TASK_NAME, FtpHostName_MISSING_MESAGE, String.Empty, 0);
result = DTSExecResult.Failure;
}
if (String.IsNullOrEmpty(this.FtpUserName))
{
componentEvents.FireError(0, TASK_NAME, FtpUserName_MISSING_MESAGE, String.Empty, 0);
result = DTSExecResult.Failure;
}
if (String.IsNullOrEmpty(this.FtpPassword))
{
componentEvents.FireError(0, TASK_NAME, FtpPassword_MISSING_MESAGE, String.Empty, 0);
result = DTSExecResult.Failure;
}
if (String.IsNullOrEmpty(this.FtpSshHostKeyFingerprint))
{
componentEvents.FireError(0, TASK_NAME, FtpSshHostKeyFingerprint_MISSING_MESAGE, String.Empty, 0);
result = DTSExecResult.Failure;
}
if (String.IsNullOrEmpty(this.FtpOperationName))
{
componentEvents.FireError(0, TASK_NAME, FtpOperationName_MISSING_MESAGE, String.Empty, 0);
result = DTSExecResult.Failure;
}
if (String.IsNullOrEmpty(this.FtpLocalPath))
{
componentEvents.FireError(0, TASK_NAME, FtpLocalPath_MISSING_MESAGE, String.Empty, 0);
result = DTSExecResult.Failure;
}
if (String.IsNullOrEmpty(this.FtpRemotePath))
{
componentEvents.FireError(0, TASK_NAME, FtpRemotePath_MISSING_MESAGE, String.Empty, 0);
result = DTSExecResult.Failure;
}
return(result);
}
/// <summary>
/// Initializes a new instance of the <see cref="SFTPConnection"/> class.
/// </summary>
/// <param name="hostName">Name of the host.</param>
/// <param name="userName">Name of the user.</param>
/// <param name="passWord">The pass word.</param>
/// <param name="portNumber">The port number.</param>
/// <param name="reTries">The re tries.</param>
/// <param name="componentEvents">The component events.</param>
/// <param name="logLevel">The log level.</param>
public SFTPConnection(string hostName, string userName, string passWord, int portNumber, bool stopOnFailure, IDTSComponentEvents componentEvents, LogLevel logLevel)
{
this.hostName = hostName;
this.userName = userName;
this.passWord = passWord;
this.portNumber = portNumber;
//this.reTries = reTries;
this.componentEvents = componentEvents;
this.logLevel = logLevel;
this.stopOnFailure = stopOnFailure;
}
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser,
IDTSComponentEvents componentEvents, IDTSLogging log)
{
if (WebHookUrl == null)
{
componentEvents.FireError(0, "Slact Taks", "Error sending message to Slack. Webhook is not set.", "", 0);
return(DTSExecResult.Failure);
}
return(DTSExecResult.Success);
}
/// <summary>
/// Initializes a new instance of the <see cref="SFTPConnection"/> class.
/// </summary>
/// <param name="hostName">Name of the host.</param>
/// <param name="userName">Name of the user.</param>
/// <param name="passWord">The pass word.</param>
/// <param name="portNumber">The port number.</param>
/// <param name="reTries">The re tries.</param>
/// <param name="componentEvents">The component events.</param>
/// <param name="logLevel">The log level.</param>
public SFTPConnection(string hostName, string userName, string passWord, int portNumber, bool stopOnFailure, IDTSComponentEvents componentEvents, LogLevel logLevel)
{
this.hostName = hostName;
this.userName = userName;
this.passWord = passWord;
this.portNumber = portNumber;
//this.reTries = reTries;
this.componentEvents = componentEvents;
this.logLevel = logLevel;
this.stopOnFailure = stopOnFailure;
}
private void _DeCompressGZ(string pathFileTAR, IDTSComponentEvents componentEvents)
{
_Check_GZ(pathFileTAR, componentEvents);
using (ICSharpCode.SharpZipLib.GZip.GZipInputStream gzip = new ICSharpCode.SharpZipLib.GZip.GZipInputStream(System.IO.File.OpenRead(pathFileTAR)))
{
_DeCompressTAR(pathFileTAR, componentEvents, gzip);
//gzip.Flush();
//gzip.Close();
}
}
private void _CompressGZ(string pathFileTAR, IDTSComponentEvents componentEvents)
{
using (ICSharpCode.SharpZipLib.GZip.GZipOutputStream gzip = new ICSharpCode.SharpZipLib.GZip.GZipOutputStream(System.IO.File.Create(pathFileTAR)))
{
gzip.SetLevel(9);
_CompressTAR(pathFileTAR, componentEvents, gzip);
//gzip.Flush();
//gzip.Close();
}
_Check_GZ(pathFileTAR, componentEvents);
}
/// <summary>Logs an error.</summary>
/// <param name="source">The source.</param>
/// <param name="message">The message.</param>
/// <param name="arguments">Optional message arguments.</param>
public static void LogError(this IDTSComponentEvents source, string message, params object[] arguments)
{
if (arguments != null && arguments.Any())
{
message = String.Format(message, arguments);
}
LogCore(source, new LogInfo()
{
Type = LogType.Error, Message = message
});
}
/// <summary>
/// Initializes a new instance of the <see cref="ZipManager"/> class.
/// </summary>
/// <param name="source">The source.</param>
/// <param name="target">The target.</param>
/// <param name="zipLevel">The zip level.</param>
/// <param name="zipPassword">The zip password.</param>
/// <param name="recursive">if set to <c>true</c> [recursive].</param>
/// <param name="removeSouce">if set to <c>true</c> [remove souce].</param>
/// <param name="overwriteTarget">if set to <c>true</c> [overwrite target].</param>
/// <param name="loglevel">The loglevel.</param>
/// <param name="componentEvents">The component events.</param>
public TarManager(string source, string target, TarCompressionLevel tarLevel, string password, bool recursive, bool removeSouce, bool overwriteTarget, LogLevel loglevel, IDTSComponentEvents componentEvents)
{
this.source = source;
this.target = target;
this.tarLevel = tarLevel;
this.password = password;
this.recursive = recursive;
this.removeSource = removeSource;
this.fileFilter = String.IsNullOrEmpty(fileFilter) ? "true" : fileFilter;
this.overwriteTarget = overwriteTarget;
this.logLevel = logLevel;
this.componentEvents = componentEvents;
}
/// <summary>
/// Initializes a new instance of the <see cref="ZipManager"/> class.
/// </summary>
/// <param name="source">The source.</param>
/// <param name="target">The target.</param>
/// <param name="zipLevel">The zip level.</param>
/// <param name="zipPassword">The zip password.</param>
/// <param name="recursive">if set to <c>true</c> [recursive].</param>
/// <param name="removeSouce">if set to <c>true</c> [remove souce].</param>
/// <param name="overwriteTarget">if set to <c>true</c> [overwrite target].</param>
/// <param name="loglevel">The loglevel.</param>
/// <param name="componentEvents">The component events.</param>
public TarManager(string source, string target, TarCompressionLevel tarLevel, string password, bool recursive, bool removeSouce, bool overwriteTarget, LogLevel loglevel, IDTSComponentEvents componentEvents)
{
this.source = source;
this.target = target;
this.tarLevel = tarLevel;
this.password = password;
this.recursive = recursive;
this.removeSource = removeSource;
this.fileFilter = String.IsNullOrEmpty(fileFilter) ? "true" : fileFilter;
this.overwriteTarget = overwriteTarget;
this.logLevel = logLevel;
this.componentEvents = componentEvents;
}
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
DTSExecResult execResult = base.Validate(connections, variableDispenser, componentEvents, log);
if (execResult == DTSExecResult.Success)
{
// Validate task properties
if (string.IsNullOrEmpty(displayText))
{
componentEvents.FireWarning(1, this.GetType().Name, "Value required for DisplayText", string.Empty, 0);
}
}
return execResult;
}
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();
}
}
}
}
}
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
ConnectionManager conn = getCurrentConnectionManager(connections);
List<KeyValuePair<string, string>> connParams = (List<KeyValuePair<string, string>>)conn.AcquireConnection(transaction);
string host = connParams.Find(t => t.Key == "Host").Value;
string username = connParams.Find(t => t.Key == "Username").Value;
string password = connParams.Find(t => t.Key == "Password").Value;
int port = Convert.ToInt32(connParams.Find(t => t.Key == "Port").Value);
SshExec exec = new SshExec(host, username);
exec.Password = password;
try
{
string stdOut = string.Empty;
string stdErr = string.Empty;
exec.Connect();
StringReader sr = new StringReader(_commandText);
while (true)
{
string s = sr.ReadLine();
if (s != null && stdErr.Trim().Length == 0)
{
int res = exec.RunCommand(s, ref stdOut, ref stdErr);
}
else
{
if (stdErr.Trim().Length > 0)
{
fireError(componentEvents, stdErr);
return DTSExecResult.Failure;
}
break;
}
}
}
catch (Exception ex)
{
fireError(componentEvents, ex.Message);
return DTSExecResult.Failure;
}
finally
{
exec.Close();
}
return DTSExecResult.Success;
}
private static void LogCore(IDTSComponentEvents source, LogInfo info)
{
switch (info.Type)
{
case LogType.Error: source.FireError(info.Code, info.Component, info.MessageAndException(), null, 0); break;
case LogType.Warning: source.FireWarning(info.Code, info.Component, info.MessageAndException(), null, 0); break;
case LogType.Information:
{
bool fireAgain = false;
source.FireInformation(info.Code, info.Component, info.MessageAndException(), null, 0, ref fireAgain); break;
};
}
;
}
/// <summary>
/// Set filename with prefix to local variable
/// </summary>
/// <param name="variableDispenser"></param>
/// <param name="componentEvents"></param>
private void SetPrefixFilename(VariableDispenser variableDispenser, IDTSComponentEvents componentEvents)
{
try
{
if (PrefixFilename != null && PrefixFilename != "Choose a Variable")
{
Variables vars = null;
variableDispenser.LockOneForWrite(PrefixFilename, ref vars);
vars[PrefixFilename].Value = TypeConverter(_localname, vars[0].DataType);
vars.Unlock();
variableDispenser.Reset();
componentEvents.FireInformation(0, "ReportGenerator", "Variable " + PrefixFilename + " set to " + _localname, string.Empty, 0, ref _refire);
}
}
catch (Exception exc)
{
componentEvents.FireError(0, "ReportGenerator", "Task Errored: " + exc, "", -1);
}
}
public override DTSExecResult Execute(Connections connections,
VariableDispenser variableDispenser,
IDTSComponentEvents componentEvents,
IDTSLogging log,
object transaction)
{
//return base.Execute(connections, variableDispenser, componentEvents, log, transaction);
Server catalogServer = new Server(ServerName);
IntegrationServices integrationServices = new IntegrationServices(catalogServer);
Catalog catalog = integrationServices.Catalogs[PackageCatalog];
CatalogFolder catalogFolder = catalog.Folders[PackageFolder];
ProjectInfo catalogProject = catalogFolder.Projects[PackageProject];
Microsoft.SqlServer.Management.IntegrationServices.PackageInfo catalogPackage = catalogProject.Packages[PackageName + ".dtsx"];
catalogPackage.Execute(false, null);
return(DTSExecResult.Success);
}
private bool GetSignalledState(VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, out bool failure)
{
bool isSignalled = false;
failure = false;
if (string.IsNullOrEmpty(SignalVariable))
{
componentEvents.FireError(0, Resources.WaitForSignalTaskName, Resources.ErrorSignalVariableHasToBeSpecified, string.Empty, 0);
failure = true;
}
if (!variableDispenser.Contains(SignalVariable))
{
componentEvents.FireError(0, Resources.WaitForSignalTaskName, string.Format(Resources.ErrorSignalVariableDoesNotExists, SignalVariable), string.Empty, 0);
failure = true;
}
else
{
Variables vars = null;
variableDispenser.LockOneForRead(SignalVariable, ref vars);
if (vars != null && vars.Contains(SignalVariable))
{
Variable v = vars[SignalVariable];
if (v.DataType != TypeCode.Boolean)
{
componentEvents.FireError(0, Resources.WaitForSignalTaskName, string.Format(Resources.ErrorSignalVariableNotBoolean, SignalVariable), string.Empty, 0);
failure = true;
}
isSignalled = (bool)v.Value;
vars.Unlock();
}
else
{
componentEvents.FireError(0, Resources.WaitForSignalTaskName, string.Format(Resources.ErrorLockSignalVariable, SignalVariable), string.Empty, 0);
failure = true;
}
}
return(isSignalled);
}
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser,
IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
try
{
var client = new SlackClient();
var message = new SlackMessage();
message.Text = this.Text;
message.Username = this.User;
client.SendMessage(message, this.WebHookUrl);
}
catch (Exception e)
{
componentEvents.FireError(0, "Slact Taks", "Error sending message to Slack. Webhook = " + WebHookUrl, "", 0);
}
//Always retun sucess because we do not want to fail package if we cannot send message to Slack
//TODO: Should this be a setting?
return(DTSExecResult.Success);
}
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
Debugger.Launch();
var filesToTransfer = Directory.GetFiles(SourceDirectory, FileTypeFilter);
WebHDFSClient client;
if (filesToTransfer.Length == 0)
{
return DTSExecResult.Success;
}
try
{
ConnectionManager cm = connections[this.ConnectionManagerName];
client = cm.AcquireConnection(transaction) as WebHDFSClient;
}
catch (Exception)
{
componentEvents.FireError(0, "HDFSTask", "Unable to create HDFS Connection Manager", "", 0);
return DTSExecResult.Failure;
}
foreach (var file in filesToTransfer)
{
string fileName = Path.GetFileName(file);
string remoteFileName = RemoteDirectory + "/" + fileName;
try
{
client.CreateFile(file, remoteFileName).Wait();
}
catch (Exception)
{
componentEvents.FireError(0, "HDFSTask", "Unable to transfer file...", "", 0);
return DTSExecResult.Failure;
}
}
return DTSExecResult.Success;
}
private void _Check_ZIP(string pathFileZip, IDTSComponentEvents componentEvents)
{
bool b = false;
if (_testarchive)
{
using (ICSharpCode.SharpZipLib.Zip.ZipInputStream fz = new ICSharpCode.SharpZipLib.Zip.ZipInputStream(System.IO.File.OpenRead(pathFileZip)))
{
if (!string.IsNullOrEmpty(_password))
{
fz.Password = _password;
}
try
{
ICSharpCode.SharpZipLib.Zip.ZipEntry ze = fz.GetNextEntry();
componentEvents.FireInformation(1, "UnZip SSIS", "Start verify file ZIP (" + _folderSource + _fileZip + ")", null, 0, ref b);
while (ze != null)
{
componentEvents.FireInformation(1, "UnZip SSIS", "Verifying Entry: " + ze.Name, null, 0, ref b);
fz.CopyTo(System.IO.MemoryStream.Null);
fz.Flush();
fz.CloseEntry();
ze = fz.GetNextEntry();
}
componentEvents.FireInformation(1, "UnZip SSIS", "File ZIP verified ZIP (" + _folderSource + _fileZip + ")", null, 0, ref b);
}
catch (Exception ex)
{
throw new Exception("Verify file: " + _fileZip + " failed. (" + ex.Message + ")");
}
finally
{
fz.Close();
}
}
}
}
/// <summary>
/// Overrides default validate logic, method will be called each time ssis tries to validate the package
/// <see cref="Microsoft.SqlServer.Dts.Runtime.Task.Validate"/>
/// </summary>
/// <param name="connections"></param>
/// <param name="variableDispenser"></param>
/// <param name="componentEvents"></param>
/// <param name="log"></param>
/// <returns></returns>
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
if (string.IsNullOrEmpty(MessageText))
{
componentEvents.FireWarning(0, "LOG", "The message is empty!", "", 0);
}
else
{
try
{
GetMessageText(variableDispenser, componentEvents, MessageText);
}
catch (Exception ex)
{
componentEvents.FireError(1, "LOG", "the chosen variable cannot be read: " + ex.Message, "", 0);
return DTSExecResult.Failure;
}
}
return base.Validate(connections, variableDispenser, componentEvents, log);
}
/// <summary>
/// Validates the specified connections.
/// </summary>
/// <param name="connections">The connections.</param>
/// <param name="variableDispenser">The variable dispenser.</param>
/// <param name="componentEvents">The component events.</param>
/// <param name="log">The log.</param>
/// <returns></returns>
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
try
{
string sourceFile = Common.GetVariableValue<string>(this.sourceFile, variableDispenser);
string targetFile = Common.GetVariableValue<string>(this.targetFile, variableDispenser);
string publicKey = Common.GetVariableValue<string>(this.publicKey, variableDispenser);
string privateKey = Common.GetVariableValue<string>(this.privateKey, variableDispenser);
string passPhrase = Common.GetVariableValue<string>(this.passPhrase, variableDispenser);
string errorMsg = String.Empty;
if (String.IsNullOrEmpty(sourceFile))
errorMsg += " Source File,";
if (String.IsNullOrEmpty(targetFile) || targetFile.Trim().Length == 0)
errorMsg += " Target File,";
if (String.IsNullOrEmpty(publicKey) && this.fileAction != PGPFileAction.Decrypt)
errorMsg += " Public Key,";
if (String.IsNullOrEmpty(privateKey) && this.fileAction != PGPFileAction.Encrypt)
errorMsg += " Private Key for Decryption and Encryption with signature,";
if (String.IsNullOrEmpty(passPhrase) && this.fileAction != PGPFileAction.Encrypt)
errorMsg += " Pass Phrase for Decryption and Encryption with signature,\n";
if (errorMsg.Trim().Length > 0)
{
componentEvents.FireError(0, "", "Missing:" + errorMsg.Remove(errorMsg.Length - 1) + ".", "", 0);
return DTSExecResult.Failure;
}
return base.Validate(connections, variableDispenser, componentEvents, log);
}
catch (Exception ex)
{
componentEvents.FireError(0, "Validate: ", ex.Message + Environment.NewLine + ex.StackTrace, "", 0);
return DTSExecResult.Failure;
}
}
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
WinSCPWrapper p;
SQLServerWrapper s;
IEnumerable<RemoteFileInfo> remoteFiles;
// connect to servers
try
{
p = new WinSCPWrapper(connections[this.WinSCPConnectionManagerName], transaction);
}
catch (System.Exception e)
{
componentEvents.FireError(0, "WinSCPTask.Execute - WinSCPConnection", e.Message, "", 0);
return DTSExecResult.Failure;
}
// compare remote files with local metadata; if using OLEDB - it can be tricky
try
{
s = new SQLServerWrapper(connections[this.SQLServerConnectionManagerName], transaction);
}
catch (System.Exception e)
{
componentEvents.FireError(0, "WinSCPTask.Execute - SqlServerConnection", e.Message, "", 0);
return DTSExecResult.Failure;
}
// get remote files
remoteFiles = p.SearchDirectory(DirectoryPath, null, 0);
s.SaveFilesToDatabase(remoteFiles);
// close connections
p.CloseSession();
return DTSExecResult.Success;
}
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
//SSH Connection Manager
if (StringUtil.NullOrEmpty(_SSHconnMgrName))
{
fireError(componentEvents, "No SSH Connection Manager selected.");
return DTSExecResult.Failure;
}
ConnectionManager cSSH = getConnectionManager(connections, _SSHconnMgrName);
if (cSSH == null)
{
fireError(componentEvents, "No SSH Connection Manager selected.");
return DTSExecResult.Failure;
}
//Send Files
if (_operation == SSHFTPOperation.SendFiles)
{
if (StringUtil.NullOrEmpty(_sendFilesSourceConnectionManagerName))
{
fireError(componentEvents, "No Send Files Source Connection Manager selected.");
return DTSExecResult.Failure;
}
ConnectionManager cSend = getConnectionManager(connections, _sendFilesSourceConnectionManagerName);
if (cSend == null)
{
fireError(componentEvents, "No Send Files Source Connection Manager selected.");
return DTSExecResult.Failure;
}
if (StringUtil.NullOrEmpty(_sendFilesDestinationDirectory))
{
fireError(componentEvents, "No Send Files Destination Directory Specified.");
return DTSExecResult.Failure;
}
}
//Receive Files
if (_operation == SSHFTPOperation.ReceiveFiles)
{
if (StringUtil.NullOrEmpty(_receiveFilesDestinationConnectionManagerName))
{
fireError(componentEvents, "No Receive Files Destination Connection Manager selected.");
return DTSExecResult.Failure;
}
ConnectionManager cReceive = getConnectionManager(connections, _receiveFilesDestinationConnectionManagerName);
if (cReceive == null)
{
fireError(componentEvents, "No Receive Files Destination Connection Manager selected.");
return DTSExecResult.Failure;
}
if (StringUtil.NullOrEmpty(_receiveFilesSourceFile))
{
fireError(componentEvents, "No Receive Files Source File specified.");
return DTSExecResult.Failure;
}
}
return DTSExecResult.Success;
}
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);
}
/// <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));
}
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents,
IDTSLogging log)
{
//return base.Validate(connections, variableDispenser, componentEvents, log);
return(DTSExecResult.Success);
}
/// <summary>
/// Executes the specified action based on settings.
/// </summary>
/// <param name="connections">The connections.</param>
/// <param name="variableDispenser">The variable dispenser.</param>
/// <param name="componentEvents">The component events.</param>
/// <param name="log">The log.</param>
/// <param name="transaction">The transaction.</param>
/// <returns></returns>
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
try
{
string _sourceFile = Common.GetVariableValue<string>(this.sourceFile, variableDispenser);
string _targetFile = Common.GetVariableValue<string>(this.targetFile, variableDispenser);
string _password = Common.GetVariableValue<string>(this.zipPassword, variableDispenser);
string _fileFilter = Common.GetVariableValue<string>(this.fileFilter, variableDispenser);
if (this.compressionType == CompressionType.Zip)
{
ZipManager zipManager = new ZipManager(_sourceFile, _targetFile,this.zipCompressionLevel, _password, this.recursive, _fileFilter, this.removeSource, this.overwriteTarget, this.logLevel, componentEvents);
if (this.fileAction == ZipFileAction.Compress)
zipManager.Zip();
else if (this.fileAction == ZipFileAction.Decompress)
zipManager.UnZip();
}
else if (this.compressionType == CompressionType.Tar)
{
TarManager tarManager = new TarManager(_sourceFile, _targetFile, this.tarCompressionLevel, _password, this.recursive, this.removeSource, this.overwriteTarget, this.logLevel, componentEvents);
if (this.fileAction == ZipFileAction.Compress)
tarManager.Compress();
else if (this.fileAction == ZipFileAction.Decompress)
tarManager.Decompress();
}
}
catch (Exception ex)
{
componentEvents.FireError(0, "Execute: ", ex.Message + Environment.NewLine + ex.StackTrace, "", 0);
return DTSExecResult.Failure;
}
return DTSExecResult.Success;
}
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
const string METHOD_NAME = "CustomLoggingTask-Validate";
try
{
if (string.IsNullOrEmpty(_eventType))
{
componentEvents.FireError(0, METHOD_NAME, "The event property must be specified", "", -1);
return(DTSExecResult.Failure);
}
if (string.IsNullOrEmpty(_connectionName))
{
componentEvents.FireError(0, METHOD_NAME, "No connection has been specified", "", -1);
return(DTSExecResult.Failure);
}
//SqlConnection connection = connections[_connectionName].AcquireConnection(null) as SqlConnection;
_connection = connections[_connectionName].AcquireConnection(null) as DbConnection;
if (_connection == null)
{
ConnectionManager cm = connections[_connectionName];
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject as Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100;
_connection = cmParams.GetConnectionForSchema() as OleDbConnection;
if (_connection == null)
{
componentEvents.FireError(0, METHOD_NAME, "The connection is not a valid ADO.NET or OLEDB connection", "", -1);
return(DTSExecResult.Failure);
}
}
if (!variableDispenser.Contains("System::SourceID"))
{
componentEvents.FireError(0, METHOD_NAME, "No System::SourceID variable available. This task can only be used in an Event Handler", "", -1);
return(DTSExecResult.Failure);
}
return(DTSExecResult.Success);
}
catch (Exception exc)
{
componentEvents.FireError(0, METHOD_NAME, "Validation Failed: " + exc.ToString(), "", -1);
return(DTSExecResult.Failure);
}
}
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
if (StringUtil.NullOrEmpty(_connMgrName))
{
fireError(componentEvents, "No Connection Manager selected.");
return DTSExecResult.Failure;
}
ConnectionManager c = getCurrentConnectionManager(connections);
if (c == null)
{
fireError(componentEvents, "No Connection Manager selected.");
return DTSExecResult.Failure;
}
if (c.Properties["Host"].GetValue(c) == null)
{
fireError(componentEvents, "Host not configured.");
return DTSExecResult.Failure;
}
if (StringUtil.NullOrEmpty(c.Properties["Host"].GetValue(c).ToString()))
{
fireError(componentEvents, "Host not configured.");
return DTSExecResult.Failure;
}
if (c.Properties["Username"].GetValue(c) == null)
{
fireError(componentEvents, "Username not configured.");
return DTSExecResult.Failure;
}
if (StringUtil.NullOrEmpty(c.Properties["Username"].GetValue(c).ToString()))
{
fireError(componentEvents, "Username not configured.");
return DTSExecResult.Failure;
}
if (c.Properties["Password"].GetValue(c) == null)
{
fireError(componentEvents, "Password not configured.");
return DTSExecResult.Failure;
}
if (StringUtil.NullOrEmpty(c.Properties["Password"].GetValue(c).ToString()))
{
fireError(componentEvents, "Password not configured.");
return DTSExecResult.Failure;
}
return DTSExecResult.Success;
}
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
_componentEvents = componentEvents;
_variableDispenser = variableDispenser;
_connections = connections;
_azureBlobConnectionInfo = (AzureBlobConnectionInfo)_connections[_connection].AcquireConnection(null);
Uri uri = (Uri)null;
if (this.InputSource != SourceType.BlobPath)
{
//If a blob name is specified we create one
if (string.IsNullOrEmpty(_blobName))
{
_blobName = string.Format(AZURE_ML_BLOB_NAME_FORMAT,
DateTime.Now.ToString("yyyyMMdd"),
Guid.NewGuid());
}
uri = UploadFile();
if (uri == null)
return DTSExecResult.Failure;
}
else
{
uri = new Uri(_source);
}
var task = ProcessBatch(uri);
task.Wait();
if (task.Result != BatchScoreStatusCode.Finished)
{
return DTSExecResult.Failure;
}
return DTSExecResult.Success;
}
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
try
{
//Azure Storage Connection
if (_connection == null || string.IsNullOrEmpty(_connection.Trim()))
{
componentEvents.FireError(-1, "ExecuteAzureMLBatch", "Connection manager required.", null, 0);
return DTSExecResult.Failure;
}
if (validateConnection(connections, _connection) == (DtsObject)null)
{
componentEvents.FireError(-1, "ExecuteAzureMLBatch", "Invalid connection manager name.", null, 0);
return DTSExecResult.Failure;
}
//Source
if (_source == null || string.IsNullOrEmpty(_source.Trim()))
{
componentEvents.FireError(-1, "ExecuteAzureMLBatch", "Source required", null, 0);
return DTSExecResult.Failure;
}
if (_sourceType == SourceType.FileConnection)
{
var sourceConn = validateConnection(connections, _source);
if (sourceConn == null)
{
componentEvents.FireError(-1, "ExecuteAzureMLBatch", "Invalid source connection manager name.", null, 0);
return DTSExecResult.Failure;
}
}
else if (_sourceType == SourceType.Variable)
{
if (!isValidVariable(variableDispenser, _source, TypeCode.String))
{
componentEvents.FireError(-1, "ExecuteAzureMLBatch", "Source variable must be of string data type", null, 0);
return DTSExecResult.Failure;
}
}
//Destination
if (_outputDestination != DestinationType.None)
{
if (_destination == null || string.IsNullOrEmpty(_destination.Trim()))
{
componentEvents.FireError(-1, "ExecuteAzureMLBatch", "Destination required", null, 0);
return DTSExecResult.Failure;
}
if (_outputDestination == DestinationType.FileConnection)
{
var destConn = validateConnection(connections, _destination);
if (destConn == null)
{
componentEvents.FireError(-1, "ExecuteAzureMLBatch", "Invalid destination connection manager name.", null, 0);
return DTSExecResult.Failure;
}
}
else if (_outputDestination == DestinationType.Variable)
{
if (!isValidVariable(variableDispenser, _destination, TypeCode.String))
{
componentEvents.FireError(-1, "ExecuteAzureMLBatch", "Destination variable must be of string data type", null, 0);
return DTSExecResult.Failure;
}
}
}
//Azure ML Config
if (_azureMLBaseURL == null || string.IsNullOrEmpty(_azureMLBaseURL.Trim()))
{
componentEvents.FireError(-1, "ExecuteAzureMLBatch", "Azure ML base url required.", null, 0);
return DTSExecResult.Failure;
}
if (_azureMLAPIKey == null || string.IsNullOrEmpty(_azureMLAPIKey.Trim()))
{
componentEvents.FireError(-1, "ExecuteAzureMLBatch", "Azure ML api key required.", null, 0);
return DTSExecResult.Failure;
}
if (_mlBatchTimeout == 0)
{
_mlBatchTimeout = 300;
}
return DTSExecResult.Success;
}
catch (Exception)
{
return DTSExecResult.Failure;
}
}
/// <summary>
/// Executes the specified actions based on settings.
/// </summary>
/// <param name="connections">The connections.</param>
/// <param name="variableDispenser">The variable dispenser.</param>
/// <param name="componentEvents">The component events.</param>
/// <param name="log">The log.</param>
/// <param name="transaction">The transaction.</param>
/// <returns></returns>
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
bool fireAgain = true;
try
{
string sourceFile = Common.GetVariableValue<string>(this.sourceFile, variableDispenser);
string targetFile = Common.GetVariableValue<string>(this.targetFile, variableDispenser);
string publicKey = Common.GetVariableValue<string>(this.publicKey, variableDispenser);
string privateKey = Common.GetVariableValue<string>(this.privateKey, variableDispenser);
string passPhrase = Common.GetVariableValue<string>(this.passPhrase, variableDispenser);
if (this.fileAction == PGPFileAction.Decrypt)
{
componentEvents.FireInformation(1, "", String.Format("Decrypting file [{0}] -> [{1}]", sourceFile, targetFile), "", 0, ref fireAgain);
PGPManager.Decrypt(sourceFile, privateKey, passPhrase, targetFile, this.overwriteTarget,this.removeSource);
componentEvents.FireInformation(1, "", String.Format("Successfully Decrypted file [{0}] -> [{1}]. ", sourceFile, targetFile), "", 0, ref fireAgain);
}
else if (this.fileAction == PGPFileAction.Encrypt)
{
componentEvents.FireInformation(1, "", String.Format("Encrypting file [{0}] -> [{1}]", sourceFile, targetFile), "", 0, ref fireAgain);
PGPManager.Encrypt(sourceFile, publicKey, targetFile, this.overwriteTarget, this.removeSource, this.isArmored);
componentEvents.FireInformation(1, "", String.Format("Sucessfully Encrypted file [{0}] -> [{1}]", sourceFile, targetFile), "", 0, ref fireAgain);
}
else if (this.fileAction == PGPFileAction.EncryptAndSign)
{
componentEvents.FireInformation(1, "", String.Format("Encrypting and Signing file [{0}] -> [{1}]", sourceFile, targetFile), "", 0, ref fireAgain);
PGPManager.EncryptAndSign(sourceFile, publicKey, privateKey, passPhrase, targetFile, this.overwriteTarget, this.removeSource, this.isArmored);
componentEvents.FireInformation(1, "", String.Format("Sucessfully Encrypted and Signed file [{0}] -> [{1}]", sourceFile, targetFile), "", 0, ref fireAgain);
}
}
catch (Exception ex)
{
componentEvents.FireError(0, "Execute: ", ex.Message + Environment.NewLine + ex.StackTrace, "", 0);
return DTSExecResult.Failure;
}
return DTSExecResult.Success;
}
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
try
{
ConnectionManager cmW = connections[this.WinSCPConnectionManagerName];
//return DTSExecResult.Success;
}
catch (System.Exception e)
{
componentEvents.FireError(0, "WinSCPTask", "Invalid WinSCP connection manager. " + e.Message, "", 0);
return DTSExecResult.Failure;
}
try
{
ConnectionManager cmS = connections[this.SQLServerConnectionManagerName];
}
catch (System.Exception e)
{
componentEvents.FireError(0, "WinSCPTask", "Invalid SQL Server connection manager. " + e.Message, "", 0);
return DTSExecResult.Failure;
}
return DTSExecResult.Success;
}
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
DTSExecResult execResult = base.Validate(connections, variableDispenser, componentEvents, log);
if (execResult == DTSExecResult.Success)
{
// Validate task properties
if (string.IsNullOrEmpty(displayText))
{
componentEvents.FireWarning(1, this.GetType().Name, "Value required for DisplayText", string.Empty, 0);
}
}
return(execResult);
}
/// <summary>
/// Validates the specified connections.
/// </summary>
/// <param name="connections">The connections.</param>
/// <param name="variableDispenser">The variable dispenser.</param>
/// <param name="componentEvents">The component events.</param>
/// <param name="log">The log.</param>
/// <returns></returns>
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
try
{
string _localFile = Common.GetVariableValue<string>(this.localFile, variableDispenser);
string _remoteFile = Common.GetVariableValue<string>(this.remoteFile, variableDispenser);
string _hostName = Common.GetVariableValue<string>(this.hostName, variableDispenser);
string _portNumber = Common.GetVariableValue<string>(this.portNumber, variableDispenser);
string _userName = Common.GetVariableValue<string>(this.userName, variableDispenser);
string _passWord = Common.GetVariableValue<string>(this.passWord, variableDispenser);
string _fileListVar = Common.GetVariableValue<string>(this.remoteFileListVariable, variableDispenser);
TypeCode _fileListVarType = Common.GetVariableType(this.remoteFileListVariable, variableDispenser);
string _fileInfoVar = Common.GetVariableValue<string>(this.fileInfo, variableDispenser);
TypeCode _fileInfoType = Common.GetVariableType(this.fileInfo, variableDispenser);
string errorMsg = String.Empty;
if (String.IsNullOrEmpty(_hostName))
errorMsg += " Host Name,";
if (String.IsNullOrEmpty(_portNumber))
errorMsg += " Port Number,";
else
{
int p;
if (Int32.TryParse(_portNumber, out p) == false)
errorMsg += " Port Number(must be a valid integer),";
}
if (String.IsNullOrEmpty(_userName))
errorMsg += " User Name,";
if (String.IsNullOrEmpty(_passWord))
errorMsg += " Password,";
if (String.IsNullOrEmpty(_localFile) && (this.fileAction == SFTPFileAction.Send || this.fileAction == SFTPFileAction.Receive))
errorMsg += " Local File,";
if (String.IsNullOrEmpty(_remoteFile) && (this.fileAction == SFTPFileAction.Send || this.fileAction == SFTPFileAction.Receive || this.fileAction == SFTPFileAction.List))
errorMsg += " Remote File,";
if (this.fileAction == SFTPFileAction.List && String.IsNullOrEmpty(_fileListVar))
errorMsg += " Result Variable,";
else if (this.fileAction == SFTPFileAction.List && _fileListVarType != TypeCode.Object)
errorMsg += " Result Variable(must be of type Object),";
if (this.fileAction == SFTPFileAction.SendMultiple || this.fileAction == SFTPFileAction.ReceiveMultiple)
{
if (String.IsNullOrEmpty(_fileInfoVar))
errorMsg += " Data Set Variable,";
else if (_fileInfoType != TypeCode.Object)
errorMsg += " Data Set Variable(must be of type Object),";
}
if (errorMsg.Trim().Length > 0)
{
componentEvents.FireError(0, "", "Missing:" + errorMsg.Remove(errorMsg.Length - 1) + ".", "", 0);
return DTSExecResult.Failure;
}
return base.Validate(connections, variableDispenser, componentEvents, log);
}
catch (Exception ex)
{
componentEvents.FireError(0, "Validate: ", ex.Message + Environment.NewLine + ex.StackTrace, "", 0);
return DTSExecResult.Failure;
}
}
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
DTSExecResult execResult = DTSExecResult.Success;
System.Windows.Forms.MessageBox.Show(this.displayText);
return(execResult);
}
/// <summary>
/// Executes the action based on seleted options.
/// </summary>
/// <param name="connections">The connections.</param>
/// <param name="variableDispenser">The variable dispenser.</param>
/// <param name="componentEvents">The component events.</param>
/// <param name="log">The log.</param>
/// <param name="transaction">The transaction.</param>
/// <returns></returns>
/// <exception cref="System.Exception">Data Set Variable Must Contain a Valid List<ISFTPFileInfo> Object.</exception>
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
try
{
bool fireAgain = true;
string _localFile = Common.GetVariableValue<string>(this.localFile, variableDispenser);
string _remoteFile = Common.GetVariableValue<string>(this.remoteFile, variableDispenser);
string _hostName = Common.GetVariableValue<string>(this.hostName, variableDispenser);
int _portNumber = Common.GetVariableValue<int>(this.portNumber, variableDispenser);
string _userName = Common.GetVariableValue<string>(this.userName, variableDispenser);
string _passWord = Common.GetVariableValue<string>(this.passWord, variableDispenser);
string _fileFilter = Common.GetVariableValue<string>(this.fileFilter, variableDispenser);
List<ISFTPFileInfo> sftpFileInfo = new List<ISFTPFileInfo>();
SFTPConnection sftp = new SFTPConnection(_hostName, _userName, _passWord, _portNumber, this.stopOnFailure, componentEvents, this.logLevel);
if (this.fileAction == SFTPFileAction.ReceiveMultiple || this.fileAction == SFTPFileAction.SendMultiple)
{
if (Common.GetVariableValue<object>(fileInfo, variableDispenser).GetType() != typeof(List<ISFTPFileInfo>))
throw new Exception("Data Set Variable Must Contain a Valid List<ISFTPFileInfo> Object.");
}
if (this.fileAction == SFTPFileAction.Send)
{
List<string> fileList = Common.GetFileList(_localFile, _fileFilter, this.isRecursive);
foreach (string fileName in fileList)
sftpFileInfo.Add(new SFTPFileInfo(fileName, _remoteFile, this.overwriteDest, this.removeSource));
if (sftpFileInfo.Count > 0)
sftp.UploadFiles(sftpFileInfo);
else
componentEvents.FireInformation(1, "", "No files selected for Upload.", "", 1, ref fireAgain);
}
else if (this.fileAction == SFTPFileAction.SendMultiple)
{
sftpFileInfo = (List<ISFTPFileInfo>)Common.GetVariableValue<object>(fileInfo, variableDispenser);
if (sftpFileInfo.Count > 0)
sftp.UploadFiles(sftpFileInfo);
else
componentEvents.FireInformation(1, "", "No files selected for Upload.", "", 1, ref fireAgain);
}
else if (this.fileAction == SFTPFileAction.Receive)
{
List<IRemoteFileInfo> remoteFileList = sftp.ListFiles(_remoteFile);
remoteFileList = Common.GetRemoteFileList(remoteFileList, _fileFilter);
foreach (IRemoteFileInfo remoteFile in remoteFileList)
sftpFileInfo.Add(new SFTPFileInfo(_localFile, remoteFile.FullName, this.overwriteDest, this.removeSource));
if (sftpFileInfo.Count > 0)
sftp.DownloadFiles(sftpFileInfo);
else
componentEvents.FireInformation(1, "", "No files selected for Download.", "", 1, ref fireAgain);
}
else if (this.fileAction == SFTPFileAction.ReceiveMultiple)
{
sftpFileInfo = (List<ISFTPFileInfo>)Common.GetVariableValue<object>(fileInfo, variableDispenser);
if (sftpFileInfo.Count > 0)
sftp.DownloadFiles(sftpFileInfo);
else
componentEvents.FireInformation(1, "", "No files selected for Download.", "", 1, ref fireAgain);
}
else if (this.fileAction == SFTPFileAction.List)
{
List<IRemoteFileInfo> remoteFileList = sftp.ListFiles(_remoteFile);
Common.SetVariableValue(this.remoteFileListVariable, remoteFileList, variableDispenser);
}
}
catch (Exception ex)
{
componentEvents.FireError(0, "Execute: ", ex.Message +Environment.NewLine + ex.StackTrace, "", 0);
return DTSExecResult.Failure;
}
return DTSExecResult.Success;
}
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
try
{
string commandText = null;
ReadVariables(variableDispenser);
//DbConnection connection = connections[_connectionName].AcquireConnection(transaction) as DbConnection;
//SqlConnection connection = (SqlConnection)connections[_connectionName].AcquireConnection(transaction);
DbCommand command = null;
//using (SqlCommand command = new SqlCommand())
if (_connection is SqlConnection)
{
commandText = @"INSERT INTO SSISLog (EventType, PackageName, TaskName, EventCode, EventDescription, PackageDuration, Host, ExecutionID, EventHandlerDateTime,UID)
VALUES (@EventType, @PackageName, @TaskName, @EventCode, @EventDescription, @PackageDuration, @Host, @Executionid, @handlerdatetime,@uid)";
command = new SqlCommand();
command.Parameters.Add(new SqlParameter("@EventType", _eventType));
command.Parameters.Add(new SqlParameter("@PackageName", _packageName));
command.Parameters.Add(new SqlParameter("@TaskName", _taskName));
command.Parameters.Add(new SqlParameter("@EventCode", _errorCode ?? string.Empty));
command.Parameters.Add(new SqlParameter("@EventDescription", _errorDescription ?? string.Empty));
command.Parameters.Add(new SqlParameter("@PackageDuration", _packageDuration));
command.Parameters.Add(new SqlParameter("@Host", _machineName));
command.Parameters.Add(new SqlParameter("@ExecutionID", _executionid));
command.Parameters.Add(new SqlParameter("@handlerdatetime", _handlerdatetime));
command.Parameters.Add(new SqlParameter("@uid", _uid));
}
else if (_connection is OleDbConnection)
{
commandText = @"INSERT INTO SSISLog (EventType,PackageName,TaskName,EventCode,EventDescription,PackageDuration,Host,ExecutionID,EventHandlerDateTime,UID)
VALUES (?,?,?,?,?,?,?,?,?,?)";
command = new OleDbCommand();
command.Parameters.Add(new OleDbParameter("@EventType", _eventType));
command.Parameters.Add(new OleDbParameter("@PackageName", _packageName));
command.Parameters.Add(new OleDbParameter("@TaskName", _taskName));
command.Parameters.Add(new OleDbParameter("@EventCode", _errorCode ?? string.Empty));
command.Parameters.Add(new OleDbParameter("@EventDescription", _errorDescription ?? string.Empty));
command.Parameters.Add(new OleDbParameter("@PackageDuration", _packageDuration));
command.Parameters.Add(new OleDbParameter("@Host", _machineName));
command.Parameters.Add(new OleDbParameter("@ExecutionID", _executionid));
command.Parameters.Add(new OleDbParameter("@handlerdatetime", _handlerdatetime));
command.Parameters.Add(new OleDbParameter("@uid", _uid));
}
command.CommandText = commandText;
command.CommandType = CommandType.Text;
command.Connection = _connection;
command.ExecuteNonQuery();
_connection.Close();
return(DTSExecResult.Success);
}
catch (Exception exc)
{
componentEvents.FireError(0, "CustomLoggingTask-Execute", "Task Errored: " + exc.ToString(), "", -1);
return(DTSExecResult.Failure);
}
}
private void fireError(IDTSComponentEvents componentEvents, string description)
{
componentEvents.FireError(0, "SSHExecuteCommandTask", description, "", 0);
}
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
bool pass = true;
if (string.IsNullOrWhiteSpace(SourceDirectory))
{
componentEvents.FireError(0, "HDFSTask", "Source Directoy value not specfied", "", 0);
pass = false;
}
if (string.IsNullOrWhiteSpace(RemoteDirectory))
{
componentEvents.FireError(0, "HDFSTask", "Remote Directoy value not specfied", "", 0);
pass = false;
}
if (string.IsNullOrWhiteSpace(FileTypeFilter))
{
componentEvents.FireError(0, "HDFSTask", "No filetype filter specified.", "", 0);
pass = false;
}
try
{
var cm = connections[ConnectionManagerName];
if ((cm.InnerObject as SSISHDFS.HDFSConnectionManager.HDFSConnectionManager) == null)
{
componentEvents.FireError(0, "HDFSTask", "No HDFS Connection Manager specfied", "", 0);
pass = false;
}
}
catch (Exception)
{
componentEvents.FireError(0, "HDFSTask", "No HDFS Connection Manager specfied", "", 0);
pass = false;
}
if (pass)
{
return DTSExecResult.Success;
}
else
{
return DTSExecResult.Failure;
}
}
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log) {
return (
connections.Contains(ConnectionName)
) ? DTSExecResult.Success : DTSExecResult.Failure;
}
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents,
IDTSLogging log, object transaction)
{
try
{
using (var sftp = new Rebex.Net.Sftp())
{
// connect to a server
sftp.Connect(HostName, ServerPort);
// authenticate
sftp.Login(LoginId, Password);
//sftp.Encoding = Encoding.ASCII;//.BigEndianUnicode;//.ASCII;//.Unicode;
sftp.TransferType = TransferType;
if (FtpType == FtpType.Download) //read from MFT
{
var filesToRead = sftp.GetList(InBoundFolderPath)
.Where(fl => fl.Name.StartsWith(SourceFileNamePattern, StringComparison.OrdinalIgnoreCase))
.Select(s => new
{
FileName = s.Name,
FullFilePath = InBoundFolderPath + "/" + s.Name
}).ToArray();
if (filesToRead.Any())
{
var _downloadedFileNames = new List <string>();
//download the file into local folder
foreach (var file in filesToRead)
{
sftp.GetFiles(file.FullFilePath, LocalPath,
SftpBatchTransferOptions.Default, SftpActionOnExistingFiles.OverwriteAll);
_downloadedFileNames.Add(file.FileName);
if (ReWriteFileToASCII)
{
string from = Path.Combine(LocalPath, file.FileName);
string to = Path.Combine(LocalPath, file.FileName + "_temp");
using (StreamReader reader = new StreamReader(from, System.Text.Encoding.UTF8, true, 10))
using (StreamWriter writer = new StreamWriter(to, false, System.Text.Encoding.ASCII, 10))
{
while (reader.Peek() >= 0)
{
writer.WriteLine(reader.ReadLine());
}
}
//delete old file and rename *_temp file to old file
File.Delete(from);
File.Move(Path.Combine(LocalPath, file.FileName + "_temp"), Path.Combine(LocalPath, file.FileName));
}
//delete the file, after download
if (DeleteSourceFileOnSuccess)
{
sftp.DeleteFile(file.FullFilePath);
}
}
DownloadedFileNames = string.Join(",", _downloadedFileNames);
}
else
{
throw new Exception(string.Format("No file with name pattern:'{0}' found inside folder :'{1}' to download.", SourceFileNamePattern, InBoundFolderPath));
}
}
else //outbound -> write to MFT
{
//get file from application path
var filesToWrite = Directory.GetFiles(LocalPath, SourceFileNamePattern + "*");
if (filesToWrite.Any())
{
//upload file
foreach (var file in filesToWrite)
{
sftp.PutFiles(file, OutBoundFolderPath, SftpBatchTransferOptions.Default);
//delete the file, after upload
if (DeleteSourceFileOnSuccess)
{
File.Delete(file);
}
}
}
else
{
throw new Exception(string.Format("No file with name pattern:'{0}' found inside folder :'{1}' to upload.", SourceFileNamePattern, LocalPath));
}
}
sftp.Disconnect();
}
return(DTSExecResult.Success);
}
catch (System.Exception e)
{
componentEvents.FireError(0, null, string.Format("Error using Rebex Sftp: {0}", e), null, 0);
return(DTSExecResult.Failure);
}
}
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction) {
try {
using (var sftp = new SftpClient(Host, Port, Username, Password))
{
sftp.Connect();
using (var file = File.OpenWrite(TargetPath))
{
sftp.DownloadFile(SourcePath, file);
}
return DTSExecResult.Success;
}
} catch (Exception ex) {
log.Write(string.Format("{0}.Execute", GetType().FullName), ex.ToString());
return DTSExecResult.Failure;
}
}
