SSIS XML logging :: Support Forum

JackalSnacks
Joined:: 2018-01-31
Posts:: 1

SSIS XML logging

2018-01-31 22:33

Not a .NET developer, just a SQL guy trying to automate downloads for my company.

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)DTSExecResult.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
   }
}

Reply with quote

martin◆ Site Admin
Joined:: 2002-12-10
Posts:: 39,466
Location:: Prague, Czechia

Re: SSIS xml logging

2018-02-01

The XML log file is used internally by the WinSCP .NET assembly. And it's deleted when Session object is disposed.

All you can do is to copy the file before disposing Session object.
But XML log file lacks closing </Session> element at that point.

Option to preserve the log is tracked here:
Bug 942 – Configurable path to XML log file for the .NET assembly

Reply with quote

Guest

Re: SSIS xml logging

2018-02-01 17:55

Thank you for the response. It is disappointing that I am unable to log with the .NET library. It seems my option for logging the transfer results via SSIS would be to use a dynamic batch file instead of a C# script?

Reply with quote