locked
FTP Resume on error

    質問

  • Hi All

    I'm having a bit of trouble with a C# script task in SSIS.

    The basis is to download files from FTP site but due to the server bouncing 426 errors the downloads terminate at random times and the FTP task built in with SSIS doesn't handle retries so I cobbled this script together from a number of posts on the web.

    The issue I have is that I have added in the below snippet which should handle the retry but it doesnt seem to be working due to when a 426 error is raised the script just stops running and doesnt move on to a retry or the next file.

                    var filepath = @"filepath"+file;               
                    FileInfo fileinfo = new FileInfo(filepath);
                    if (fileinfo.Exists)
                    {
                        reqFTP.ContentOffset = fileinfo.Length;
                        writeStream = new FileStream(filepath, FileMode.Append, FileAccess.Write);
                    }
                    else
                    {
                        writeStream = new FileStream(filepath, FileMode.Create, FileAccess.Write);
                    }

    Can anyone suggest where I am going wrong

    Thanks in advance.

    Ant

    -----------------------------------------------------------------------------------------------------------------

    /*
       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 Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using System.IO;
    using System.Net;
    using System.Text;

    namespace ST_7961e95198104be195690d4237e54824.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()
            {
                // TODO: Add your code here
                string[] files = GetFileList();
                foreach (string file in files)
                {
                    Download(file);
                }

                Dts.TaskResult = (int)ScriptResults.Success;
            }

            private void Download(string file)
            {                       
                try
                {                
                    string uri = "ftp://Server/Folder/" + file;
                    Uri serverUri = new Uri(uri);
                    if (serverUri.Scheme != Uri.UriSchemeFtp)
                    {
                        return;
                    }
                    FtpWebRequest reqFTP;                
                    reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://Server/Folder/" + file));                                
                    reqFTP.Credentials = new NetworkCredential("user", "pass");                
                    reqFTP.KeepAlive = true;
                    reqFTP.UseBinary = true;
                    reqFTP.Proxy = null;
                    reqFTP.UsePassive = false;
                    reqFTP.Timeout = -1;
                    reqFTP.Method = WebRequestMethods.Ftp.DownloadFile;
                    FileStream writeStream;
                    var filepath = @"filepath"+file;               
                    FileInfo fileinfo = new FileInfo(filepath);
                    if (fileinfo.Exists)
                    {
                        reqFTP.ContentOffset = fileinfo.Length;
                        writeStream = new FileStream(filepath, FileMode.Append, FileAccess.Write);
                    }
                    else
                    {
                        writeStream = new FileStream(filepath, FileMode.Create, FileAccess.Write);
                    }
                    FtpWebResponse response = (FtpWebResponse)reqFTP.GetResponse();
                    Stream responseStream = response.GetResponseStream();
                    int Length = 2048;
                    Byte[] buffer = new Byte[Length];
                    int bytesRead = responseStream.Read(buffer, 0, Length);               
                    while (bytesRead != 0)
                    {   
                        writeStream.Write(buffer, 0, bytesRead);
                        bytesRead = responseStream.Read(buffer, 0, Length);
                    }                
                    writeStream.Close();
                    response.Close();
                }
                catch (WebException wEx)
                {
                    MessageBox.Show(wEx.Message, "Download Error");
                    Dts.TaskResult = (int)ScriptResults.Failure;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Download Error");
                    Dts.TaskResult = (int)ScriptResults.Failure;
                }
            }


            public string[] GetFileList()
            {
                string[] downloadFiles;
                StringBuilder result = new StringBuilder();
                WebResponse response = null;
                StreamReader reader = null;
                try
                {
                    FtpWebRequest reqFTP;
                    reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://Server/Folder/"));
                    reqFTP.UseBinary = true;
                    reqFTP.Credentials = new NetworkCredential("user", "pass");
                    reqFTP.Method = WebRequestMethods.Ftp.ListDirectory;
                    reqFTP.Proxy = null;
                    reqFTP.KeepAlive = true;
                    reqFTP.UsePassive = false;
                    reqFTP.Timeout = -1;
                    response = reqFTP.GetResponse();
                    reader = new StreamReader(response.GetResponseStream());
                    string line = reader.ReadLine();
                    while (line != null)
                    {
                        result.Append(line);
                        result.Append("\n");
                        line = reader.ReadLine();
                    }
                    // to remove the trailing '\n'
                    result.Remove(result.ToString().LastIndexOf('\n'), 1);
                    return result.ToString().Split('\n');
                }
                catch (Exception ex)
                {
                    if (reader != null)
                    {
                        reader.Close();
                        MessageBox.Show(ex.Message, "GetListError");
                        Dts.TaskResult = (int)ScriptResults.Failure;
                    }
                    if (response != null)
                    {
                        response.Close();
                        MessageBox.Show(ex.Message, "GetListError");
                        Dts.TaskResult = (int)ScriptResults.Failure;
                    }                
                    downloadFiles = null;
                    return downloadFiles;
                }
            }
        }
    }


            
    • 移動 Leo Liu - MSFT 2012年3月26日 7:07 Moved for better support. (From:Visual C# General)
    2012年3月21日 16:00

すべての返信