SSIS FTP Task to get data from Mainframe

SSIS and Import/Export
1

I am trying to use the SSIS FTP task to receive data from mainframe.
I have input the name of my mainframe file under the "RemotePath" in the "FTP Task Editor"
but I am getting error that the RemotePath does not start with "/".
Any help ?

2

I found that the FTP task is too simple for that kind of thing. I use script tasks for that.

3

can you share your script task ?

4 
/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using System.IO;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_0096d4e7d1244db1a288955e5724ed34.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        /*
		The execution engine calls this method when the task executes.
		To access the object model, use the Dts property. Connections, variables, events,
		and logging features are available as members of the Dts property as shown in the following examples.

		To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
		To post a log entry, call Dts.Log("This is my log text", 999, null);
		To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

		To use the connections collection use something like the following:
		ConnectionManager cm = Dts.Connections.Add("OLEDB");
		cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

		Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
		
		To open Help, press F1.
	*/

        public void Main()
        {
            // Retrieve needed variables
            string ftpLocalPath = Dts.Variables["FTPLocalPath"].Value.ToString();
            string ftpRemotePath = Dts.Variables["FTPRemotePath"].Value.ToString();
            string fileNamePrefix = Dts.Variables["FileNamePrefix"].Value.ToString();

            try
            {
                // Connect to FTP server
                var ftpServer = Dts.Connections["ftpServer"];
                var ftpConn = new FtpClientConnection(ftpServer.AcquireConnection(null));
                ftpConn.Connect();

                // Get listing of files in remote directory
                ftpConn.SetWorkingDirectory("'" + ftpRemotePath + "'");
                String[] folderNames;
                String[] fileNames;
                ftpConn.GetListing(out folderNames, out fileNames);

                // Process all filenames received
                bool fileFound = false;
                if (fileNames != null)
                {
                    foreach (var row in fileNames)
                    {
                        // Retrieve file(s) matching the prefix
                        var splitRow = row.Split(new Char[] { '\t', ' ' }, StringSplitOptions.RemoveEmptyEntries);
                        var fn = splitRow[splitRow.Length - 1];

                        if (fn.StartsWith(fileNamePrefix))
                        {
                            ftpConn.ReceiveFiles(new String[] { fn }, ftpLocalPath, true, false);
                            fileFound = true;

                            // Return name of file retrieved from FTP server
                            Dts.Variables["FTPFileName"].Value = fn;
                        }
                    }
                }
                ftpConn.Close();

                if (!fileFound)
                {
                    throw new FileNotFoundException("No files found matching prefix '" + fileNamePrefix + "'");
                }
            }
            catch (Exception ex)
            {
                Dts.Events.FireError(0, "FTP Script Task", "Error: " + ex.Message, String.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}