public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
DTSExecResult Result = base.Validate(connections, variableDispenser, componentEvents, log);
if (Result == DTSExecResult.Success)
{
if (string.IsNullOrEmpty(Server))
{
componentEvents.FireError(0, "Reporting Service Execution Task", "Missing configuration value - Report Server", "", 0);
return(DTSExecResult.Failure);
}
if (string.IsNullOrEmpty(SelectedReport))
{
componentEvents.FireError(0, "Reporting Service Execution Task", "Missing configuration value - Selected Report", "", 0);
return(DTSExecResult.Failure);
}
if (string.IsNullOrEmpty(FileFormat))
{
componentEvents.FireError(0, "Reporting Service Execution Task", "Missing configuration value - Output File Format", "", 0);
return(DTSExecResult.Failure);
}
if (string.IsNullOrEmpty(FileName))
{
componentEvents.FireError(0, "Reporting Service Execution Task", "Missing configuration value - Output File Name", "", 0);
return(DTSExecResult.Failure);
}
}
return(DTSExecResult.Success);
}
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;
}
}
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
System.Threading.Thread.Sleep(this._interval * 60000);
return DTSExecResult.Success;
}
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);
}
/// <summary>
/// Resolves system variables, and assigns their values to local variables for later use.
/// </summary>
/// <param name="variableDispenser"></param>
private void ResolveSystemVariables(ref VariableDispenser variableDispenser)
{
Variables variables = null;
try
{
variableDispenser.LockForRead(TASK_NAME_QUALIFIED_VARIABLE_NAME);
variableDispenser.LockForRead(PACKAGE_NAME_QUALIFIED_VARIABLE_NAME);
variableDispenser.GetVariables(ref variables);
if (variables.Contains(TASK_NAME_VARIABLE_NAME))
{
_taskName = variables[TASK_NAME_QUALIFIED_VARIABLE_NAME].Value.ToString();
}
if (variables.Contains(PACKAGE_NAME_VARIABLE_NAME))
{
_defaultApplicationName = variables[PACKAGE_NAME_QUALIFIED_VARIABLE_NAME].Value.ToString();
}
}
catch (Exception exc)
{
throw new Exception(COULD_NOT_RESOLVE_SYSTEM_VARIABLE_MESSAGE, exc.InnerException);
}
finally
{
if (variables.Locked)
{
variables.Unlock();
}
}
}
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
return (
connections.Contains(ConnectionName)
&& !string.IsNullOrWhiteSpace(TargetPath)
) ? DTSExecResult.Success : DTSExecResult.Failure;
}
private void ResolveComponentCustomProperties()
{
_dataProfileFileName = ComponentMetaData.CustomPropertyCollection[DATA_PROFILE_FILE_NAME_PROPERTY_NAME].Value.ToString();
if (VariableDispenser.Contains(_dataProfileFileName))
{
IDTSVariables100 variables = null;
VariableDispenser.LockOneForRead(_dataProfileFileName, ref variables);
_dataProfileFileName = (String)variables[0].Value;
}
_dataProfileColumnName = ComponentMetaData.CustomPropertyCollection[DATA_PROFILE_COLUMN_NAME_PROPERTY_NAME].Value.ToString();
if (VariableDispenser.Contains(_dataProfileColumnName))
{
IDTSVariables100 variables = null;
VariableDispenser.LockOneForRead(_dataProfileColumnName, ref variables);
_dataProfileColumnName = (String)variables[0].Value;
}
_regexPatterns.Clear();
_regexPatterns = this.LoadRegularExpressions(_dataProfileFileName, _dataProfileColumnName);
_emailAddressInputColumnName = ComponentMetaData.InputCollection[INPUT_NAME].CustomPropertyCollection[INPUT_COLUMN_NAME].Value.ToString();
// v2
_regexOptionsNumber = (Int64)(ComponentMetaData.CustomPropertyCollection[REGEX_OPTIONS_PROPERTY_NAME].Value);
}
/// <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;
}
}
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
return((
connections.Contains(ConnectionName) &&
!string.IsNullOrWhiteSpace(TargetPath)
) ? DTSExecResult.Success : DTSExecResult.Failure);
}
/// <summary>
/// This method is called at runtime, and does all the "work".
/// </summary>
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
_localname = Localname();
if (ReportType == "Dataset Report")
{
Variables vars = null;
var oleda = new System.Data.OleDb.OleDbDataAdapter();
var dt = new DataTable(Datasetname);
variableDispenser.LockForRead(Recordset);
variableDispenser.GetVariables(ref vars);
componentEvents.FireInformation(0, "ReportGenerator", "Filename: " + _localname, string.Empty, 0, ref _refire);
try
{
//var rv = new ReportViewer {ProcessingMode = ProcessingMode.Local};
var val = vars[Recordset].Value;
oleda.Fill(dt, val);
componentEvents.FireInformation(0, "ReportGenerator", "Starts report generation with " + dt.Rows.Count + " rows", string.Empty, 0, ref _refire);
Run(Reportname, Datasetname, dt, _localname);
componentEvents.FireInformation(0, "ReportGenerator", "The report was created.", string.Empty, 0, ref _refire);
//Warning: debugmode is a user defined option.
if (DebugMode)
{
componentEvents.FireInformation(0, "ReportGenerator", "Satrts local report generation", string.Empty, 0, ref _refire);
var localReport = new ReportViewerUi
{
Datasetname = Datasetname,
Data = dt,
Reportname = Reportname
};
localReport.ShowDialog();
}
}
catch (Exception ex)
{
componentEvents.FireError(0, "ReportGeneratorTask", ex.ToString(), "", 0);
}
finally
{
if (vars.Locked)
{
vars.Unlock();
}
}
}
else
{
GenerateServerReport(variableDispenser, componentEvents);
}
// set prefixfilename to variable
if (!string.IsNullOrEmpty(PrefixFilename) && PrefixFilename != "Choose a Variable")
{
SetPrefixFilename(variableDispenser, componentEvents);
}
return(base.Execute(connections, variableDispenser, componentEvents, log, transaction));
}
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
if (sleepInterval > 0)
{
bool failure;
while (!GetSignalledState(variableDispenser, componentEvents, log, out failure) && !failure)
{
System.Threading.Thread.Sleep(sleepInterval);
}
if (failure)
{
return(DTSExecResult.Failure);
}
else
{
return(DTSExecResult.Success);
}
}
else if (sleepInterval <= 0)
{
componentEvents.FireError(0, Resources.SleepTaskName, Resources.ErrorSleepInterval, string.Empty, 0);
return(DTSExecResult.Failure);
}
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);
}
/// <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);
}
/// <summary>Reads a variable's value.</summary>
/// <typeparam name="T">The type of the value.</typeparam>
/// <param name="source">The source.</param>
/// <param name="name">The variable name.</param>
/// <param name="defaultValue">The default value to return if the variable does not exist.</param>
/// <returns>The value of the variable.</returns>
/// <remarks>
/// The variable is locked for reading while the value is being read.
/// </remarks>
public static T TryGetValue <T> (this VariableDispenser source, string name, T defaultValue)
{
if (!source.Contains(name))
{
return(defaultValue);
}
Variables variables = null;
try
{
source.LockForRead(name);
source.GetVariables(ref variables);
T value = defaultValue;
if (variables.TryGetVar <T>(name, out value))
{
return(value);
}
return(defaultValue);
} finally
{
if (variables != null)
{
variables.Unlock();
}
};
}
/// <summary>Executes the task.</summary>
/// <param name="connections">The connections.</param>
/// <param name="variableDispenser">The variables.</param>
/// <param name="componentEvents">The event subsystem.</param>
/// <param name="log">The logging subsystem.</param>
/// <param name="transaction">The active transaction.</param>
/// <returns>The results of the execution.</returns>
/// <remarks>
/// Derived types should not override this method. Use <see cref="ExecuteCore"/> instead.
/// </remarks>
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
try
{
var context = new TaskExecuteContext()
{
Connections = connections,
Log = log,
Transaction = transaction,
Variables = variableDispenser,
Events = componentEvents
};
return(ExecuteCore(context));
} catch (Exception e)
{
if (componentEvents != null)
{
componentEvents.LogError(b => b.Message("Unhandled exception during execution.")
.Exception(e));
}
return(DTSExecResult.Failure);
};
}
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
if (_typeOperation == Type_Operation.Please_Select)
{
componentEvents.FireError(1, "UnZip SSIS", "Please select type operation (De/Compress).", null, 0);
return(DTSExecResult.Failure);
}
if (_typeOperation == Type_Operation.Compress)
{
if ((string.IsNullOrEmpty(_fileZip)) || (string.IsNullOrEmpty(_folderSource)))
{
componentEvents.FireError(1, "UnZip SSIS", "Please select Source Folder and Zip File.", null, 0);
return(DTSExecResult.Failure);
}
}
else
{
if ((string.IsNullOrEmpty(_fileZip)) || (string.IsNullOrEmpty(_folderSource)) || (string.IsNullOrEmpty(_folderDest)))
{
componentEvents.FireError(1, "UnZip SSIS", "Please select Source Folder, Zip File and Destination Folder.", null, 0);
return(DTSExecResult.Failure);
}
}
return(DTSExecResult.Success);
}
/// <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 Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
bool failure = false;
if (checkInterval <= 0)
{
componentEvents.FireError(0, Resources.WatForFileTaskName, Resources.ErrorCheckInterval, string.Empty, 0);
failure = true;
}
if (files.Count == 0)
{
componentEvents.FireError(0, Resources.WaitForFileTaskName, Resources.ErrorMissingFileToCheck, string.Empty, 0);
failure = true;
}
else
{
failure = !CheckFilesToCheck();
if (failure)
{
componentEvents.FireError(0, Resources.WaitForFileTaskName, Resources.ErrorNotValidFileToCheck, string.Empty, 0);
}
}
if (failure)
{
return(DTSExecResult.Failure);
}
else
{
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 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);
}
}
private void EditExpressionButtonClick(int selectedRow, int selectedCol)
{
try
{
Package package = GetCurrentPackage();
if (package == null)
{
return;
}
if (selectedRow < 0)
{
return;
}
Variable variable = GetVariableForRow(selectedRow);
if (variable == null)
{
return;
}
DtsContainer sourceContainer = FindObjectForVariablePackagePath(package, variable.GetPackagePath());
Variables variables = sourceContainer.Variables;
VariableDispenser variableDispenser = sourceContainer.VariableDispenser;
Konesans.Dts.ExpressionEditor.ExpressionEditorPublic editor = new Konesans.Dts.ExpressionEditor.ExpressionEditorPublic(variables, variableDispenser, variable);
if (editor.ShowDialog() == DialogResult.OK)
{
string expression = editor.Expression;
if (string.IsNullOrEmpty(expression) || string.IsNullOrEmpty(expression.Trim()))
{
expression = null;
variable.EvaluateAsExpression = false;
}
else
{
variable.EvaluateAsExpression = true;
}
variable.Expression = expression;
changesvc.OnComponentChanging(sourceContainer, null);
changesvc.OnComponentChanged(sourceContainer, null, null, null); //marks the package designer as dirty
SSISHelpers.MarkPackageDirty(package);
TypeDescriptor.Refresh(variable);
System.Windows.Forms.Application.DoEvents();
// Refresh the grid
variablesToolWindowControl.GetType().InvokeMember("FillGrid", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.InvokeMethod | System.Reflection.BindingFlags.FlattenHierarchy | System.Reflection.BindingFlags.Instance, null, variablesToolWindowControl, new object[] { });
SetButtonEnabled();
RefreshHighlights();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\r\n\r\n" + ex.StackTrace, DefaultMessageBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
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);
}
/// <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)
{
DTSExecResult execResult = DTSExecResult.Success;
System.Windows.Forms.MessageBox.Show(this.displayText);
return execResult;
}
/// <summary>
/// Initializes a new instance of the <see cref="ExpressionEditorPublic"/> class for a property
/// </summary>
/// <param name="variables">The variables collection for the container that hosts the property.</param>
/// <param name="variableDispenser">The variable dispenser for the container that hosts the property.</param>
/// <param name="propertyType">The type of the property which hosts the expression.</param>
/// <param name="propertyName">The name of the property which hosts the expression.</param>
/// <param name="expression">The current expression if any.</param>
public ExpressionEditorPublic(Variables variables, VariableDispenser variableDispenser, Type propertyType, string propertyName, string expression) : this(variables, variableDispenser)
{
this.expressionEditorView.ResultType = Type.GetTypeCode(propertyType);
this.expressionEditorView.Expression = expression;
this.TitleLabel.Text = "Expression for property:";
this.TitleValueLabel.Text = string.Format(CultureInfo.CurrentCulture, "{0} ({1})", propertyName, propertyType.Name);
}
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);
}
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 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 (sleepInterval <= 0)
{
componentEvents.FireError(0, Resources.WaitForSignalTaskName, Resources.ErrorSleepInterval, string.Empty, 0);
return(DTSExecResult.Failure);
}
if (string.IsNullOrEmpty(SignalVariable))
{
componentEvents.FireError(0, Resources.WaitForSignalTaskName, Resources.ErrorSignalVariableHasToBeSpecified, string.Empty, 0);
return(DTSExecResult.Failure);
}
if (!variableDispenser.Contains(SignalVariable))
{
componentEvents.FireError(0, Resources.WaitForSignalTaskName, string.Format(Resources.ErrorSignalVariableDoesNotExists, SignalVariable), string.Empty, 0);
return(DTSExecResult.Failure);
}
else
{
Variables vars = null;
try
{
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);
return(DTSExecResult.Failure);
}
else if (v.Namespace != "User")
{
componentEvents.FireError(0, Resources.WaitForSignalTaskName, Resources.ErrorSignalWariableNotFromUser, string.Empty, 0);
return(DTSExecResult.Failure);
}
}
else
{
componentEvents.FireError(0, Resources.WaitForSignalTaskName, string.Format(Resources.ErrorLockSignalVariable, SignalVariable), string.Empty, 0);
return(DTSExecResult.Failure);
}
}
finally
{
if (vars != null && vars.Locked)
{
vars.Unlock();
}
}
}
return(base.Validate(connections, variableDispenser, componentEvents, log));
}
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
Boolean fireAgain = false;
try
{
// Validate mandatory String properties.
DTSExecResult propertyValidationResult = this.ValidateProperties(ref componentEvents);
if (propertyValidationResult != DTSExecResult.Success)
{
return(propertyValidationResult);
}
// The package developer should know that files will be removed from the source.
if (this.FtpRemove)
{
componentEvents.FireInformation(0, TASK_NAME, REMOVE_ENABLED_MESSAGE, String.Empty, 0, ref fireAgain);
}
// Verify the connection.
using (Session winScpSession = this.EstablishSession())
{
componentEvents.FireInformation(0, TASK_NAME, SESSION_OPEN_MESSAGE, String.Empty, 0, ref fireAgain);
// Verify the remote resources.
OperationMode operation = (OperationMode)Enum.Parse(typeof(OperationMode), this.FtpOperationName);
switch (operation)
{
case OperationMode.PutFiles:
Boolean remoteDirectoryExists = winScpSession.FileExists(this.FtpRemotePath);
if (!remoteDirectoryExists)
{
componentEvents.FireInformation(0, TASK_NAME, String.Format(REMOTE_DIRECTORY_MISSING_MESSAGE_PATTERN, this.FtpRemotePath), String.Empty, 0, ref fireAgain);
}
break;
case OperationMode.GetFiles:
default:
Boolean remoteFileExists = winScpSession.FileExists(this.FtpRemotePath);
if (!remoteFileExists)
{
componentEvents.FireInformation(0, TASK_NAME, String.Format(REMOTE_FILES_MISSING_MESSAGE_PATTERN, this.FtpRemotePath), String.Empty, 0, ref fireAgain);
}
break;
}
}
return(DTSExecResult.Success);
}
catch (Exception exc)
{
String exceptionMessage = exc != null ? exc.Message : UNKNOWN_EXCEPTION_MESSAGE;
componentEvents.FireError(0, TASK_NAME, String.Format(EXCEPTION_MESSAGE_PATTERN, exceptionMessage), String.Empty, 0);
return(DTSExecResult.Failure);
}
}
private void LogRowcount()
{
var rowCountData = new RowCountSerialize
{
RowCountType =
(RowCountSerialize.RowCountTypeEnum)
(ComponentMetaData.CustomPropertyCollection["RowCountType"].Value),
RowCount = _allRowCount
};
if (string.IsNullOrEmpty(_rowCountSumName))
{
rowCountData.ColumnSum = 0;
rowCountData.ColumnName = "";
}
else
{
rowCountData.ColumnSum = _allAggregate;
rowCountData.ColumnName = _rowCountSumName;
}
rowCountData.RowCountComponent = ComponentMetaData.Name;
rowCountData.RowCountObject = (string)(ComponentMetaData.CustomPropertyCollection["RowCountObject"].Value);
VariableDispenser.LockForRead("User::ExecutionID");
IDTSVariables100 variables;
VariableDispenser.GetVariables(out variables);
if (variables.Locked)
{
rowCountData.ExecutionID = (Int64)variables["User::ExecutionID"].Value;
variables.Unlock();
}
_cmd.Parameters["@ExecutionID"].Value = rowCountData.ExecutionID;
_cmd.Parameters["@ComponentName"].Value = rowCountData.RowCountComponent;
_cmd.Parameters["@ObjectName"].Value = rowCountData.RowCountObject;
_cmd.Parameters["@CountType"].Value = rowCountData.RowCountTypeString;
_cmd.Parameters["@RowCount"].Value = rowCountData.RowCount;
_cmd.Parameters["@ColumnSum"].Value = rowCountData.ColumnSum;
_cmd.Parameters["@ColumnName"].Value = rowCountData.ColumnName;
_cmd.ExecuteNonQuery();
//if (_columnInfo == null || _columnInfo.Rows.Count == 0) return;
//var cmd = new SqlCommand("[ssis].[LogColumnInfo]", _connection) { CommandType = CommandType.StoredProcedure };
//cmd.Parameters.Add(new SqlParameter("@ExecutionID", rowCountData.ExecutionID));
//var parameter = new SqlParameter("@InputColumnInfo", SqlDbType.Structured)
//{
// TypeName = "dbo.InputColumnInfo",
// Value = _columnInfo
//};
//cmd.Parameters.Add(parameter);
//cmd.CommandTimeout = 0;
//cmd.ExecuteNonQuery();
}
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;
}
/// <summary>
/// Validates the task and its settings at desing time, and at run time.
/// </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(_defaultApplicationName))
{
componentEvents.FireError(0, _taskName, UNRESOLVED_APPLICATION_NAME_MESSAGE, String.Empty, 0);
return(DTSExecResult.Failure);
}
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)
{
if (this._interval <= 0)
{
componentEvents.FireError(0, "", "Pause length must be greater than 0.", "", 0);
return(DTSExecResult.Failure);
}
return(DTSExecResult.Success);
}
/// <summary>
/// Validates the Task
/// </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 (sleepInterval <= 0)
{
componentEvents.FireError(0, Resources.SleepTaskName, Resources.ErrorSleepInterval, string.Empty, 0);
return(DTSExecResult.Failure);
}
return(base.Validate(connections, variableDispenser, componentEvents, log));
}
/// <summary>
/// Initializes a new instance of the <see cref="ExpressionEditorPublic"/> class for a variable.
/// </summary>
/// <param name="variables">The variables collection for the host container.</param>
/// <param name="variableDispenser">The variable dispenser for the host container.</param>
/// <param name="variable">The variable which hosts the expression.</param>
public ExpressionEditorPublic(Variables variables, VariableDispenser variableDispenser, Variable variable) : this(variables, variableDispenser)
{
this.expressionEditorView.ResultTypeValidate = true;
this.expressionEditorView.Initialize(variableDispenser, variables);
this.expressionEditorView.ResultType = variable.DataType;
this.expressionEditorView.Expression = variable.Expression;
this.TitleLabel.Text = "Expression for variable:";
this.TitleValueLabel.Text = string.Format(CultureInfo.CurrentCulture, "{0} ({1})", variable.QualifiedName, variable.DataType.ToString());
}
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);
}
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);
}
/// <summary>
/// Initializes a new instance of the task at design time.
/// </summary>
/// <param name="connections"></param>
/// <param name="variableDispenser"></param>
/// <param name="events"></param>
/// <param name="log"></param>
/// <param name="eventInfos"></param>
/// <param name="logEntryInfos"></param>
/// <param name="refTracker"></param>
public override void InitializeTask(Connections connections, VariableDispenser variableDispenser, IDTSInfoEvents events, IDTSLogging log, EventInfos eventInfos, LogEntryInfos logEntryInfos, ObjectReferenceTracker refTracker)
{
// Initialize public members.
this.ApplicationName = String.Empty;
this.IsVerbose = true;
// Retrieve system variables.
this.ResolveSystemVariables(ref variableDispenser);
base.InitializeTask(connections, variableDispenser, events, log, eventInfos, logEntryInfos, refTracker);
}
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;
}
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;
}
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;
}
/// <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)
{
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;
}
/// <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;
}
}
/// <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 void InitializeTask(Connections connections, VariableDispenser variableDispenser, IDTSInfoEvents events, IDTSLogging log, EventInfos eventInfos, LogEntryInfos logEntryInfos, ObjectReferenceTracker refTracker)
{
base.InitializeTask(connections, variableDispenser, events, log, eventInfos, logEntryInfos, refTracker);
}
/// <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;
}
/// <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 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 isValidVariable(VariableDispenser variableDispenser, string var, TypeCode type)
{
Variables variables = (Variables)null;
try
{
variableDispenser.LockOneForRead(var, ref variables);
Variable variable = variables[var];
if (variable.DataType != type)
{
return false;
}
}
catch
{
return false;
}
finally
{
if (variables != null)
variables.Unlock();
}
return true;
}
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;
}
}
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;
}
/// <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)
{
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 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;
}
}
