tim laqua dot com Thoughts and Code from Tim Laqua

30Oct/101

Getting a useful FTP error message out of SSIS

The plan: Upload a zip file for a bunch of different clients to various user-specified ftp servers - looks something like this:

A quick note on what that "Update FTP Connection" task is doing - it's modifying the properties of the "FTP" connection manager to the appropriate Server/Username/Password for this particular client:

1
2
3
4
5
6
7
8
9
10
        public void Main()
        {
            ConnectionManager ftp = Dts.Connections["FTP"];
            ftp.Properties["ServerName"].SetValue(ftp, (string)Dts.Variables["FTPServer"].Value);
            ftp.Properties["ServerUserName"].SetValue(ftp, (string)Dts.Variables["FTPUser"].Value);
            ftp.Properties["ServerPassword"].SetValue(ftp, (string)Dts.Variables["User::FTPPassword"].Value);
 
            Dts.TaskResult = (int)ScriptResults.Success;
 
        }

The problem: Anyone who has tried to upload multiple files to multiple FTP sites in an SSIS package very quickly ran in to FTP errors (that's what happens when you let users tell you what their FTP url and authentication is). You can handle these errors and log them via the standard OnError handler dumping the ErrorCode, ErrorDescription, and usually SourceName out to a flat file or table.

Now, when you look at the ErrorDescription for an FTP Task - you'll be disappointed. It usually states "Unable to connect to FTP server..." or something to that effect. Looking at the execution results in the progress tab, you'll see that the FTP Connection Manager threw a more detailed description of the error:

Good luck getting at that detailed error description - My first attempt here was to acquire a connection and connect via the connection manager and trap the error that came back. While that worked, the error that was trapped was HRESULT 0xC001602A, which basically means that something bad happened.

Now we move on to phase two: Retry the FTP operation via .NET FtpWebRequest and trap that error. This can either flat out replace the FTP Task as a script task or, not sure why I did it this way - I suppose I like to try and use SSIS built in tasks as much as possible - you can retry the operation in the OnError handler assuming you have the requisite information (url, username, and password) available.

Create the OnError handler (either at the package level for everything or just on the executable you care about - I chose the latter):

Then add a data flow task - inside that add a Script Component (as a source) and an appropriate destination (Here it's going to an OleDb destination):

Configure the Script Component for ReadOnly access to the requisite variables:

And configure the Output buffer:

Now go edit the actual script:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Net;
using System.IO;
using System.Text;
 
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void CreateNewOutputRows()
    {
        string errorDescription = Variables.ErrorDescription;
 
        if (Variables.ErrorDescription.Contains("Unable to connect to FTP server"))
        {
            try
            {
                FtpWebRequest ftp = (FtpWebRequest)WebRequest.Create("ftp://" + Variables.FTPUrl);
                ftp.Method = WebRequestMethods.Ftp.UploadFile;
                ftp.Credentials = new System.Net.NetworkCredential(Variables.FTPUser, Variables.FTPPassword);
 
                StreamReader sourceStream = new StreamReader(Variables.FTPLocalPath);
                byte[] fileContents = Encoding.UTF8.GetBytes(sourceStream.ReadToEnd());
                sourceStream.Close();
                ftp.ContentLength = fileContents.Length;
 
                Stream requestStream = ftp.GetRequestStream();
                requestStream.Write(fileContents, 0, fileContents.Length);
                requestStream.Close();
 
                FtpWebResponse response = (FtpWebResponse)ftp.GetResponse();
 
                response.Close();
 
                errorDescription = "Upload retry attempt via .NET was successful.";
            }
            catch (Exception e)
            {
                errorDescription = e.Message;
            }
        }
 
 
        ErrorSourceBuffer.AddRow();
        ErrorSourceBuffer.ErrorCode = Variables.ErrorCode.ToString();
        ErrorSourceBuffer.ErrorDescription = errorDescription;
        ErrorSourceBuffer.ErrorSource = Variables.SourceName;
        ErrorSourceBuffer.FTPUrl = Variables.FTPUrl;
        ErrorSourceBuffer.FTPUser = Variables.FTPUser;
        ErrorSourceBuffer.FTPPassword = Variables.FTPPassword;
    }
}

And bob's your uncle. Now you have meaningful text in the ErrorDescription column of the output buffer instead of some vague "something's wrong" message.

Here's a flat file destination walkthrough for the OnError handler: http://vsteamsystemcentral.com/cs21/blogs/applied_business_intelligence/archive/2007/04/08/ssis-design-pattern-custom-error-handling.aspx

Comments (1) Trackbacks (0)
  1. Hi,

    Thanks for this great work.

    How would you do the Custom Error Handling in SSIS 2008? It works fine in SSIS 2005, but even when you upgrade the script from SSIS 2005 to SSIS 2008, it doesn’t work.

    http://vsteamsystemcentral.com/cs21/blogs/applied_business_intelligence/archive/2007/04/08/ssis-design-pattern-custom-error-handling.aspx


Leave a comment

No trackbacks yet.