Skip to content

Instantly share code, notes, and snippets.

@devjaime

devjaime/scriptMain.cs

Created July 13, 2021 02:23
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save devjaime/80ae78f66c33d2f56b4c534ec6b8e136 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save devjaime/80ae78f66c33d2f56b4c534ec6b8e136 to your computer and use it in GitHub Desktop.
Download ZIP
ETL SSIS upload file sftp
Raw
scriptMain.cs
#region Help: Introduction to the script task
/* The Script Task allows you to perform virtually any operation that can be accomplished in
* a .Net application within the context of an Integration Services control flow.
*
* Expand the other regions which have "Help" prefixes for examples of specific ways to use
* Integration Services features within this script task. */
#endregion
#region Namespaces
using Microsoft.SqlServer.Dts.Runtime;
using System;
using System.Reflection;
using WinSCP;
using System.IO;
#endregion
namespace ST_3fa8046142d14005be5e743f08cee82a
{
/// <summary>
/// ScriptMain is the entry point class of the script. Do not change the name, attributes,
/// or parent of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region Help: Using Integration Services variables and parameters in a script
/* To use a variable in this script, first ensure that the variable has been added to
* either the list contained in the ReadOnlyVariables property or the list contained in
* the ReadWriteVariables property of this script task, according to whether or not your
* code needs to write to the variable. To add the variable, save this script, close this instance of
* Visual Studio, and update the ReadOnlyVariables and
* ReadWriteVariables properties in the Script Transformation Editor window.
* To use a parameter in this script, follow the same steps. Parameters are always read-only.
*
* Example of reading from a variable:
* DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;
*
* Example of writing to a variable:
* Dts.Variables["User::myStringVariable"].Value = "new value";
*
* Example of reading from a package parameter:
* int batchId = (int) Dts.Variables["$Package::batchId"].Value;
*
* Example of reading from a project parameter:
* int batchId = (int) Dts.Variables["$Project::batchId"].Value;
*
* Example of reading from a sensitive project parameter:
* int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();
* */
#endregion
#region Help: Firing Integration Services events from a script
/* This script task can fire events for logging purposes.
*
* Example of firing an error event:
* Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);
*
* Example of firing an information event:
* Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)
*
* Example of firing a warning event:
* Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);
* */
#endregion
#region Help: Using Integration Services connection managers in a script
/* Some types of connection managers can be used in this script task. See the topic
* "Working with Connection Managers Programatically" for details.
*
* Example of using an ADO.Net connection manager:
* object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);
* SqlConnection myADONETConnection = (SqlConnection)rawConnection;
* //Use the connection in some code here, then release the connection
* Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);
*
* Example of using a File connection manager
* object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);
* string filePath = (string)rawConnection;
* //Use the connection in some code here, then release the connection
* Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);
* */
#endregion
/// <summary>
/// This method is called when this script task executes in the control flow.
/// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
/// To open Help, press F1.
/// </summary>
private const string AssemblyPath = @"C:\Program Files (x86)\WinSCP\WinSCPnet.dll";
static ScriptMain()
{
DebugLoading("Setting up assembly resolve handler");
AppDomain.CurrentDomain.AssemblyResolve += AssemblyResolve;
}
private static void DebugLoading(string message)
{
message = DateTime.Now.ToLongTimeString() + ": " + message + Environment.NewLine;
// Uncomment to debug assembly loading issues
// File.AppendAllText(LoadingLog, message);
}
private static Assembly AssemblyResolve(object sender, ResolveEventArgs args)
{
try
{
DebugLoading($"Resolving assembly {args.Name}");
string name = new AssemblyName(args.Name).Name;
DebugLoading($"Assembly name {name}");
if (name.Equals("WinSCPnet", StringComparison.InvariantCultureIgnoreCase))
{
DebugLoading($"Loading {name} from {AssemblyPath}");
Assembly assembly = Assembly.LoadFile(AssemblyPath);
DebugLoading("Loaded");
return assembly;
}
DebugLoading("Not WinSCPnet");
return null;
}
catch (Exception e)
{
DebugLoading($"Exception: {e}");
throw;
}
}
public void Main()
{
// Setup session options
SessionOptions sessionOptions = new SessionOptions
{
Protocol = Protocol.Sftp,
// To setup these variables, go to SSIS > Variables.
// To make them accessible from the script task, in the context menu of the
// task, choose Edit. On the Script task editor on Script page,
// select ReadOnlyVariables, and tick the below properties.
HostName = (string)Dts.Variables["$Package::HostName"].Value,
UserName = (string)Dts.Variables["$Package::UserName"].Value,
Password = (string)Dts.Variables["$Package::Password"].Value,
SshHostKeyFingerprint = (string)Dts.Variables["$Package::Fingerprint"].Value
};
try
{
using (Session session = new Session())
{
// As WinSCP .NET assembly has to be stored in GAC to be used with SSIS,
// you need to set path to WinSCP.exe explicitly,
// if using non-default location.
string oldFilePathInstaleap = (string)Dts.Variables["$Package::dirFile"].Value + "file.txt"; // Full path of old file
string newFilePathInstaleap = (string)Dts.Variables["$Package::dirFile"].Value + "file" + DateTime.Now.ToString("dd-MM-yyyy") + ".txt"; // Full path of new file
string newFilePathLog = (string)Dts.Variables["$Package::dirFilelog"].Value + "file" + DateTime.Now.ToString("dd-MM-yyyy") + "_log.txt";
if (File.Exists(newFilePathInstaleap))
{
File.Delete(newFilePathInstaleap);
}
if (File.Exists(newFilePathLog))
{
File.Delete(newFilePathLog);
}
File.Copy(oldFilePathInstaleap, newFilePathLog);
File.Move(oldFilePathInstaleap, newFilePathInstaleap);
session.ExecutablePath = (string)Dts.Variables["$Package::dirWinSCP"].Value + "winscp.exe";
// Connect
session.Open(sessionOptions);
// Upload files
TransferOptions transferOptions = new TransferOptions();
transferOptions.TransferMode = TransferMode.Binary;
TransferOperationResult transferResult = session.PutFiles(
newFilePathInstaleap, "/", false, transferOptions);
// Throw on any error
transferResult.Check();
// Print results
bool fireAgain = false;
foreach (TransferEventArgs transfer in transferResult.Transfers)
{
Dts.Events.FireInformation(0, null,
string.Format("Upload of {0} succeeded", transfer.FileName),
null, 0, ref fireAgain);
}
}
Dts.TaskResult = (int)DTSExecResult.Success;
}
catch (Exception e)
{
Dts.Events.FireError(0, null,
string.Format("Error when using WinSCP to upload files: {0}", e),
null, 0);
Dts.TaskResult = (int)DTSExecResult.Failure;
}
}
#region ScriptResults declaration
/// <summary>
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
///
/// This code was generated automatically.
/// </summary>
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment