SSIS - FTP using Script Task

I was asked to write a SSIS package to download a file from a website (using FTP with a WWW... weird) at 2:00 am automatically. SSIS's FTP tool couldn't do it because it passes the username and password on the URL. (Rotten idea from a security POV, but that's what I was given.) It took me about two weeks, but I cobbled this code together. I had to perform several more major steps after getting the code to run, and I hope I remember them all here. To get it to run automatically:

1. Create your SSIS package (.dtsx)
2. Copy just the .dtsx file to the directory where you want to run it from
3. Create a new domain user with "Log on as a batch file" permissions
4. If your SSIS package connects to a database, add that user as a login on your SQL Server (Server / Security / Logins).
5. On the database server you want to run it on, create a batch file to execute your SSIS package. The "output.txt" file pipes the output to a separate file, which can be very handy.
   
   d:
   cd \directory
   dtexec /FILE "your-file-name.dtsx" /CHECKPOINTING OFF /REPORTING E > "your-file-name-lastrun-output.txt"
6. Launch Task Scheduler on the machine you want to run it on, give it the name of your batch file, give it a schedule, and tell it to execute as that new domain user.

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Net

Imports System.IO

Imports System.Collections.Generic

Imports System.Text

<system.addin.addin("scriptmain", )="" ,="" description:="" publisher:="" version:="1.0"> _

<system.clscompliantattribute(false)> _

Partial Public Class ScriptMain

    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults

        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success

        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

    End Enum

    Public Sub Main()

        Try

            Dim restURL As New StringBuilder()

            Dim objRequest As FtpWebRequest

            Dim objResponse As FtpWebResponse

            Dim objStream As StreamReader

            Dim arySourceFiles(0) As String

            Dim aryDestinationFiles(0) As String

            Dim intFile As Int16

            ' - original file names from FTP site

            arySourceFiles(0) = "original_filename.txt"

            ' - new file names - rename arySourceFiles(n) to aryDestinationFiles(n)

            aryDestinationFiles(0) = "\\servername\d$\directory\new_filename.txt"

            ' - download each file

            For intFile = 0 To UBound(arySourceFiles)

                objRequest = DirectCast(FtpWebRequest.Create("ftp://nt_domainname%5Cusername:password@www.website.com/directory1/directory2/" & arySourceFiles(intFile)), FtpWebRequest)

                objResponse = DirectCast(objRequest.GetResponse(), FtpWebResponse)

                objStream = New StreamReader(objResponse.GetResponseStream())

                File.WriteAllText(aryDestinationFiles(intFile), objStream.ReadToEnd)

            Next

            Dts.TaskResult = ScriptResults.Success

        Catch webEx As WebException

            Dim [error] As New StringBuilder()

            'catch protocol errors

            If webEx.Status = WebExceptionStatus.ProtocolError Then

                [error].AppendFormat("Status code: ", DirectCast(webEx.Response, HttpWebResponse).StatusCode)

                [error].AppendFormat("Status description: ", DirectCast(webEx.Response, HttpWebResponse).StatusDescription)

                ' post the error message we got back.  This is the old error catch code that might work better with SSIS.

                Dts.Events.FireError(0, String.Empty, webEx.Message.ToString(), String.Empty, 0)

                Dts.TaskResult = ScriptResults.Failure

            End If

        End Try

    End Sub

End Class