SSIS xml logging
Not a .net developer, just a sql guy trying to automate downloads for my company.
EDIT*Please disregard the use of "_S+e+x_" in the code, as i couldn't post to the forum because of the way Microsoft.SqlServer.Dts.Runtime.DTS... was spelled.
Main Issue
I have everything downloading beautifully, however, i cannot figure out how to log the download meta data (filenames, times, errors, etc.). I've dealt with XML a lot in SSIS and i really want the xml log in order to fill my download auditing tables with that transpired in the scheduled script executions. I've applied the session.XmlLogPath, but it doesn't seem to do anything. I've been through the documentation, and i very well could be missing something, but it looks like i am calling it write in the script.
So basically, i am stuck on the logging part of this project.
If i cannot get an XML file out of the SSIS script task, is there a way to save the xml to an output variable in SSIS?
Or, is there a logging technique i haven't read about that will help me out?
I very much appreciate the responses!
Thanks!
Environment META
Goal
Set Up
Right now, the package is simple:
Exec SQL task to fill script variables from a jobs table. Then Run a script task, using c# 2017.
Example of variables being set at runtime:
Code being used:
EDIT*Please disregard the use of "_S+e+x_" in the code, as i couldn't post to the forum because of the way Microsoft.SqlServer.Dts.Runtime.DTS... was spelled.
Main Issue
I have everything downloading beautifully, however, i cannot figure out how to log the download meta data (filenames, times, errors, etc.). I've dealt with XML a lot in SSIS and i really want the xml log in order to fill my download auditing tables with that transpired in the scheduled script executions. I've applied the session.XmlLogPath, but it doesn't seem to do anything. I've been through the documentation, and i very well could be missing something, but it looks like i am calling it write in the script.
So basically, i am stuck on the logging part of this project.
If i cannot get an XML file out of the SSIS script task, is there a way to save the xml to an output variable in SSIS?
Or, is there a logging technique i haven't read about that will help me out?
I very much appreciate the responses!
Thanks!
Environment META
- Using Visual Studio Professional 2017
- Integration Services Project (2017 SSDT v1.0)
- WinSCP-5.11.3-Automation.zip (exe & dll)
- Assembly installed on the GAC
Goal
- Create General download package, that is variable fed by SQL table, to schedule (sql server agent) for various download projects from our vendors.
- Log sftp event and meta data for stake holder report tracking.
Set Up
Right now, the package is simple:
Exec SQL task to fill script variables from a jobs table. Then Run a script task, using c# 2017.
Example of variables being set at runtime:
Code being used:
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using WinSCP;
namespace ST_465ac362c5e347efa0c10d3f4d89aa7b
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
string winscpPath = Dts.Variables["winSCPPath"].Value.ToString();
string username = Dts.Variables["ftpUsername"].Value.ToString();
string password = Dts.Variables["ftpPassword"].Value.ToString();
string ftpSite = Dts.Variables["ftpSite"].Value.ToString();
int ftpPort = (int)Dts.Variables["ftpPort"].Value;
string localPath = Dts.Variables["localPath"].Value.ToString();
string remoteFTPDirectory = Dts.Variables["remoteFTPDirectory"].Value.ToString();
string fileMask = Dts.Variables["fileMask"].Value.ToString();
string sshKey = Dts.Variables["sshKey"].Value.ToString();
string xmlLogPath = Dts.Variables["xmlLogPath"].Value.ToString();
SessionOptions sessionOptions = new SessionOptions
{
Protocol = Protocol.Sftp,
HostName = ftpSite,
UserName = username,
Password = password,
PortNumber = ftpPort,
SshHostKeyFingerprint = sshKey
};
try
{
using (Session session = new Session())
{
//Set XML Logging
session.XmlLogPath = xmlLogPath;
// WinSCP .NET assembly must be in GAC to be used with SSIS,
// set path to WinSCP.exe explicitly, if using non-default path.
session.ExecutablePath = winscpPath;
// Connect
session.Timeout = new TimeSpan(0, 2, 0); // two minutes
session.Open(sessionOptions);
TransferOptions transferOptions = new TransferOptions();
transferOptions.TransferMode = TransferMode.Binary;
try
{
session.GetFiles(remoteFTPDirectory + "/" +
fileMask, localPath, false, transferOptions);
}
catch (Exception e)
{
Dts.Events.FireError(0, null,
string.Format("Error when using WinSCP to download file: {0}", e), null, 0);
Dts.TaskResult = (int)DT_S+e+x_ecResult.Failure;
}
}
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
Dts.Events.FireError(0, null,
string.Format("Error when using WinSCP to download file: {0}", ex), null, 0);
Dts.TaskResult = (int)DT_S+e+x_ecResult.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.DT_S+e+x_ecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DT_S+e+x_ecResult.Failure
};
#endregion
}
}