SSIS Script task that FTP's files

Question

I could not find the exact details on how to create a SSIS script that would ftp files on these forums, so I am adding my code to help save time for anyone else that might be wanting to do something similar.  Here is the VB code for my script task to FTP files (hope this helps someone):

' 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

Public Class ScriptMain

Public Sub Main()

Try

'Create the connection to the ftp server

Dim cm As ConnectionManager = Dts.Connections.Add("FTP")

'Set the properties like username & password

cm.Properties("ServerName").SetValue(cm, "Enter your Server Name here")

cm.Properties("ServerUserName").SetValue(cm, "Enter your FTP User Name here")

cm.Properties("ServerPassword").SetValue(cm, "Enter your FTP Password here")

cm.Properties("ServerPort").SetValue(cm, "21")

cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout

cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb

cm.Properties("Retries").SetValue(cm, "1")

'create the FTP object that sends the files and pass it the connection created above.

Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))

'Connects to the ftp server

ftp.Connect()

'Build a array of all the file names that is going to be FTP'ed (in this case only one file)

Dim files(0) As String

files(0) = "Drive:\FullPath\YourFileName"

'ftp the file

'Note: I had a hard time finding the remote path directory. I found it by mistake by creating both the FTP connection and task in the SSIS package and it defaulted the remote path setting in the FTP task.

ftp.SendFiles(files, "/Enter Your Remote Path", True, False) ' the True makes it overwrite existing file and False is saying that it is not transferring ASCII

ftp.Close()

Catch ex As Exception

Dts.TaskResult = Dts.Results.Failure

End Try

Dts.TaskResult = Dts.Results.Success

 End Sub

End Class

Answer 1

Matthew,

 

this was very very helpful.  I could not get the FTP task to delete a remote file for the life of me.  I used your script above and just modified the ftp.SendFiles to ftp.DeleteFiles and it worked like a charm.

 

Thanks,

 

S

Answer 2

This is extremely useful.

 

ftp.SendFiles(files, "", True, False)

 

I just used a blank in the remote path, and it works, it does'nt work with any slashes.

Thanks so much, this got my code working. There is probably a bug in the FTP componenet in SSIS.

Answer 3

Thanks for your code.. It's very helpful.. 

 

 

 

Answer 4

Thanks so much for your post!

 

However, I cannot get the modified script below to delete all of the files on the FTP server. I would like to delete all of the files on the server. The file names begin like "romps.dat." and has a final extension like "192". So, an example of a file on the server is "romps.dat.192"

 

If I explicitly state the file name, it works great, but for some reason I cannot get it to delete anything when I insert the wildcard. I've tried it with only one file and with multiple files.

 

 

'Connects to the ftp server

            ftp.Connect()

            'Build a array of all the file names that is going to be FTP'ed

            Dim files(0) As String
            files(0) = "romps.dat.*"

            'ftp the files

            ftp.DeleteFiles(files)

            ftp.Close()

 

Thanks in advance for your help!

Patrick

Answer 5

Many FTP sites do not support wildcards.

Answer 6

Any suggestions?

 

Thanks,

Patrick

 

Answer 7

Patrick Browder wrote:

Any suggestions?   Thanks, Patrick

Run a directory listing, load that up into an array, and then loop through that array (list, or whatever it's called in .Net world) to delete each file.

Answer 8

That helped. Here's the script if anyone's interested:

 

   Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))

            'Connects to the ftp server

            ftp.Connect()

            'Get file listing
            Dim fileNames() As String
            Dim folderNames() As String
            ftp.GetListing(folderNames, fileNames)

            'ftp the files

            ftp.DeleteFiles(fileNames)

            ftp.Close()

 

Patrick

 

Answer 9

 

Thanks for the code!

I noticed however that the example always returns success. To fix this the codeline

Dts.TaskResult = Dts.Results.Success

should be moved to the last row before the catch.

Answer 10

l ran into an error exception at the ftp.Connect() when executing the code below which is almost exactly the same

Message = "Exception from HRESULT: 0xC001602A"

        Try

            'Create the connection to the ftp server
            Dim cm As ConnectionManager = Dts.Connections.Add("FTP")

            'Set the properties like username & password
            cm.Properties("ServerName").SetValue(cm, "ftp.(5 char text).com")
            cm.Properties("ServerUserName").SetValue(cm, "5 char text")
            cm.Properties("ServerPassword").SetValue(cm, "5 char text")
            cm.Properties("ServerPort").SetValue(cm, "21")
            cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout
            cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb
            cm.Properties("Retries").SetValue(cm, "1")

            'create the FTP object that sends the files and pass it the connection created above.
            Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))

            'Connects to the ftp server
            ftp.Connect() "EXCEPTION OCCUR HERE"

            'Build a array of all the file names that is going to be FTP'ed (in this case only one file)
            Dim files(0) As String
            files(0) = "c:\tempfolder\1.xls"
            'ftp the file
            'Note: I had a hard time finding the remote path directory. I found it by mistake by creating both the FTP connection and task in the SSIS package and it defaulted the remote path setting in the FTP task.
            ftp.SendFiles(files, "ftp.(5 char text).com/tempfolder", True, False) ' the True makes it overwrite existing file and False is saying that it is not transferring ASCII
            ftp.Close()

            Dts.TaskResult = Dts.Results.Success
        Catch ex As Exception
            Dts.TaskResult = Dts.Results.Failure
        End Try

can anyone give me a hand please. Thx

Alex

Answer 11

First thank you for taking the time to read my post!

I have tried the code you provided here and i keep getting the following error:

[Connection manager "{3246C151-503C-4DE3-AFB6-54FF6D3820A7}"] Error: An error occurred in the requested FTP operation. Detailed error description: The connection with the server was reset .

This error occurs on the GetListing line.  I preceded that line with a line to set the working directory and I received no errors.

Do you have any suggestions?

Answer 12

I ended up using an FTP class library found on the Code Project at - http://www.codeproject.com/cs/internet/ftpdriver1.asp?df=100&forumid=11325&exp=0&select=900150

This library was great for obtaining the directory listing and deleting files from a Unix server (does not work so well on a Windows box).  However, the retrieval of files was much slower than the SSIS FTP connection. 

Please note that you need to create your own solution and add the files from the Code Project website into that solution,  You also need to sign your solution, add it to the GAC and copy your solution's DLL to the main C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 folder in order to reference it inside of the SSIS Script editor.

Here is the complete script I am using in case anybody else runs into this issue:

Imports Custom.FTP   'This is the Code Project class reference
Imports System
Imports System.Data
Imports System.IO
Imports System.Text
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

    ' This class utilizes the FTP Class Library found by going to:
    '      http://www.codeproject.com/cs/internet/ftpdriver1.asp?df=100&forumid=11325&exp=0&select=900150
    ' in order to retireve a list of file names from a Unix FTP Server
    ' It then uses an SSIS FTP Connection for faster retrieval of the actual files
    'NOTE: The Custom FTP class also successfully deletes files from a Unix server as well

    Public Sub Main()

        Dim fileNames() As String
        Dim ftpConn As FtpConnection
        Dts.TaskResult = Dts.Results.Success

        Try
            'Setup a custom FTP connection to Unix
            ftpConn = FtpConnection.Create(Dts.Variables("ServerName").Value.ToString, _
            Short.Parse(Dts.Variables("ServerPort").Value.ToString), Console.Out, Console.Out, _
            Dts.Variables("FTPUser").Value.ToString, Dts.Variables("FTPPassword").Value.ToString)
            'Next we need to setup a directory link to the site
            Dim directoryList As DirectoryList = New PassiveDirectoryList(ftpConn)

            'Now we retrieve the directory listing which comes back in a byte array:
            Dim sbClientFeedback As New StringBuilder()
            Dim sbServerFeedback As New StringBuilder()
            Dim clientOutput As New StringWriter(sbClientFeedback)
            Dim serverOutput As New StringWriter(sbServerFeedback)
            Dim rawDirectory() As Byte = directoryList.GetList(Nothing, clientOutput, serverOutput)

            'Next we need to convert the binary to ASCII and convert the text into meaningful file nodes:
            Dim textDirectory As String = System.Text.Encoding.ASCII.GetString(rawDirectory)
            Dim fileNodes As UnixFileNode() = DirectCast(New UnixFileNode().FromFtpList(textDirectory, ftpConn.CurrentWorkingDirectory), UnixFileNode())

            'Next we grab the relevant names out of the list and add them to a string collection:
            ReDim fileNames(fileNodes.Length)
            Dim intFileCounter As Int32 = 1

            For Each fileNode As UnixFileNode In fileNodes
                If fileNode.FullName.IndexOf(".xml.gz.lmp") > 0 Then
                    fileNames(intFileCounter) = fileNode.FullName.Replace(".lmp", "").Replace("/", "")
                    intFileCounter += 1
                End If
            Next

            'Next we resize the array:
            ReDim Preserve fileNames(intFileCounter - 1)
        Catch ex As Exception
            Dts.TaskResult = Dts.Results.Failure
        Finally
            'Now we close this connection as it is not the most efficient for retrieving files:
            ftpConn.Close()
        End Try

        If Dts.TaskResult = Dts.Results.Success Then
            Dim ssisFtp As FtpClientConnection
            Try
                'Now we create an SSIS connection for pulling the files:
                Dim ssisConn As ConnectionManager = Dts.Connections.Add("FTP")
                'Set the properties like username & password
                Dim intRetries As Int32 = Int32.Parse(Dts.Variables("FtpRetry").Value.ToString)
                ssisConn.Properties("ServerName").SetValue(ssisConn, Dts.Variables("ServerName").Value.ToString)
                ssisConn.Properties("ServerUserName").SetValue(ssisConn, Dts.Variables("FTPUser").Value.ToString)
                ssisConn.Properties("ServerPassword").SetValue(ssisConn, Dts.Variables("FTPPassword").Value.ToString)
                ssisConn.Properties("ServerPort").SetValue(ssisConn, Dts.Variables("ServerPort").Value.ToString)
                'The 0 setting will make it not timeout
                ssisConn.Properties("Timeout").SetValue(ssisConn, Dts.Variables("TimeOut").Value.ToString)
                ssisConn.Properties("ChunkSize").SetValue(ssisConn, Dts.Variables("ChunkSize").Value.ToString) '1000 kb
                ssisConn.Properties("Retries").SetValue(ssisConn, intRetries.ToString)

                'Next we create the FTP Connection
                ssisFtp = New FtpClientConnection(ssisConn.AcquireConnection(Nothing))

                'And we pass the modified file names in to retrieve the inflated files:
                ssisFtp.Connect()

                'Since the FTP connection seems to timeout with large files, we need to verify that all of the files downloaded
                Dim requiredFiles() As String = VerifyFiles(fileNames, Dts.Variables("FtpDestination").Value.ToString)
                Dim intCount As Int32 = 0

                While (requiredFiles.Length > 0 OrElse (requiredFiles.Length = 1 AndAlso requiredFiles(0) <> "")) AndAlso intCount <= intRetries
                    ssisFtp.ReceiveFiles(requiredFiles, Dts.Variables("FtpDestination").Value.ToString, True, False)
                    requiredFiles = VerifyFiles(requiredFiles, Dts.Variables("FtpDestination").Value.ToString)
                    intCount += 1
                End While

                If intCount > intRetries AndAlso (requiredFiles.Length > 0 OrElse (requiredFiles.Length = 1 AndAlso requiredFiles(0) <> "")) Then
                    'We have to try to ask for each file seperately:
                    Dim singleFile(0) As String
                    For Each thisFile As String In requiredFiles
                        singleFile(0) = thisFile
                        ssisFtp.ReceiveFiles(singleFile, Dts.Variables("FtpDestination").Value.ToString, True, False)
                    Next

                    'We perform one final check and then throw an error or warning:
                    requiredFiles = VerifyFiles(requiredFiles, Dts.Variables("FtpDestination").Value.ToString)
                    If requiredFiles.Length > 0 OrElse (requiredFiles.Length = 1 AndAlso requiredFiles(0) <> "") Then
                        'In this FTP scenario, the files are all pointers to 0-byte files so I fire a warning:
                        Dts.Events.FireWarning(0, "Unix FTP Task", "The Following Did Not Transfer: " & String.Join(Environment.NewLine, requiredFiles), _
                         "", 0)
                    End If
                End If

            Catch ex As Exception
                Dts.TaskResult = Dts.Results.Failure
            Finally
                ' Finally we close the SSIS FTP Conection
                ssisFtp.Close()
            End Try
        End If
    End Sub

    Public Function VerifyFiles(ByVal CurrentList As String(), ByVal TargetDirectory As String) As String()
        Dim thisDir As New DirectoryInfo(TargetDirectory)
        Dim sbFiles As New StringBuilder()
        Dim haveFile As Boolean

        For Each listItem As String In CurrentList
            If listItem <> Nothing AndAlso listItem <> "" Then
                haveFile = False
                For Each downloadedFile As FileInfo In thisDir.GetFiles()
                    If downloadedFile.Name = listItem Then
                        haveFile = True
                        Exit For
                    End If
                Next
                If Not haveFile Then
                    sbFiles.Append(listItem + "|")
                End If
            End If
        Next
        Dim outputFiles() As String
        If sbFiles.Length > 0 Then
            outputFiles = sbFiles.ToString().Substring(0, sbFiles.Length - 1).Split("|".ToCharArray())
        Else
            ReDim outputFiles(1)
            outputFiles(1) = ""
        End If

        Return outputFiles
    End Function

End Class

Answer 13

Thanks for the code. I'm trying to receive files from an ftp server. so I changed the code to receive files and it doesn't download anything. Am I missing something?

 

 

files(0) = filepath and name

ftp.ReceiveFiles(files, LocPath, True, False) ' the True makes it overwrite existing file and False is saying that it is not transferring ASCII

ftp.Close()

 

 

 

Answer 14

Sorry for the delay in getting back to you - notifications are not working for me.  If you are connecting to a standard FTP site then use the FTP Task - this stuff really only works for non-Windows servers.

 

Answer 15

 

Does anybody know howto modify this code to use NVS DnsNameing convention when Ftping to a mainframe. My from file is Named C:\file1.txt my to file needs to be  'xxxx.xx.xxxx.CREATE.REQUEST(+1)'

Answer 16

This looks like just what I need.

Could you please tell me the easiest way to execute the script within and SSIS dtsx package?

Would I need to create a full VS .NET project, or can i just paste this script into a (filename).cmd file and add to the command files properties of the package?

 

Thanks,

 

Paul

Answer 17

I modified this to recieve a file from a mainframe to a directory on my PC. It looks as if it's successful, but it does not download the file. These were my modifications:

 

files(0) = "'xxx.xxx.xxx.xxxx(0)'"

'ftp the file

ftp.ReceiveFiles(files, "C:\test\xxx.zip",True, False)

 

I've tried the files(0) = to both with single quotes in the double quotes: "'xxx.xxx.xxx.xxxx(0)'" and without the single quotes: "xxx.xxx.xxx.xxxx(0)". In both cases it shows as if it executed successfully, but it doesn't do a thing. The file is there and I can download it using a bat program or directly through FTP.

 

Any suggestions would be much appreciated. Thanks

Answer 18

Viwaltzer wrote:

I modified this to recieve a file from a mainframe to a directory on my PC. It looks as if it's successful, but it does not download the file. These were my modifications:   files(0) = "'xxx.xxx.xxx.xxxx(0)'" 'ftp the file ftp.ReceiveFiles(files, "C:\test\xxx.zip",True, False)   I've tried the files(0) = to both with single quotes in the double quotes: "'xxx.xxx.xxx.xxxx(0)'" and without the single quotes: "xxx.xxx.xxx.xxxx(0)". In both cases it shows as if it executed successfully, but it doesn't do a thing. The file is there and I can download it using a bat program or directly through FTP.   Any suggestions would be much appreciated. Thanks

 

it might behoove you to use the execute process task for ftp'ing.

 

hth

Answer 19

 

Thanks! Works great :-)

Answer 20

Hi,

 

Is anyone still following this thread?

 

I have a similar issue. The FTP Task in SSIS 2005 can connect to an AS/400 FTP Server, and it can list the files needed, but the file names are cryptic, and fail. Here is the listing:

 

FC003      3166662 10/31/08  19:46:53 *STMF     BSWAF20081030.TXT

FC003      3256877 10/30/08  17:42:52 *STMF     BSWAF20081029.TXT

FC003      3165556 10/29/08  19:43:53 *STMF     BSWAF20081028.TXT

 

...

 

When I put the whole "filename" as shown above, I get a 550 error - Specified directory does not exist.

When I only key the BSWAF20081030.TXT file name, I get the same error.

 

I am able to FTP GET this file from the FTP Client that comes with Windows XP.

 

Thoughts?

 

Bob

 

Answer 21

Hello, hello, Steve B.? Are you there?

 

Can you help? It appears that IBM and Microsoft are not talking about this issue between the IBM FTP Server and the SSIS FTP Client. They don't like each other I guess. That leaves poor little developers like me out in the cold.

 

See the prior question. I work for an important Microsoft Client, and I am looking bad because I cannot get the FTP connectors to work properly for the AS/400 FTP Server.

 

Anyone?

 

Bobby T

 

Answer 22

Bobby T. wrote:

Hello, hello, Steve B.? Are you there?   Can you help? It appears that IBM and Microsoft are not talking about this issue between the IBM FTP Server and the SSIS FTP Client. They don't like each other I guess. That leaves poor little developers like me out in the cold.   See the prior question. I work for an important Microsoft Client, and I am looking bad because I cannot get the FTP connectors to work properly for the AS/400 FTP Server.   Anyone?   Bobby T

 

if you can ftp to AS/400 using a third-party console application, then you can use the execute process task to do it for you.

 

hth

Answer 23

Have you tried SSIS+ library?

Answer 24

I created two tasks to ftp the same file. One using the code sample posted here and the other using an FTP task. The code sample ran 5 times faster than the FTP task. No idea why, but needless to say I'll be coding the FTP process from now on.

 

Answer 25

Hi Matthew,

Thanks for sharing the code snippet. I was looking for a solution to avoid a CWD/CD command that is inherent in the FTP Task and your script did the trick.

 

Is there a way to get file listing from FTP sites based on a pattern ("*2008.DAT")?

The listing method pulls back an array of folders and files but does not let the user to specify any options.

 

Thanks again,

V

 

Answer 26

You legend - I've been looking for 2 days to see if there's a way I could replicate the ftp -s command (where I can pass a script into the ftp command).   This is going to save me a LOT of time.  Thanks
niall

Answer 27

hi all,.
the said code was helpfull. as i am novice so it was v.helpfull 2me.

My needs are..

I have to load the bak files to the ftp server. the file name should be in format of

backup_[YYYY][MM][DD][HH][MM]

YYYY – Four digit year

MM- 2 digit Month

DD – 2 digit Date

HH – Constant at “23” - This is because the backup happens every night at 23:30

MM – Constant at “30” – This is because the backup happens every night at 23:30

their would be multiple file in folder. and the folder location and credential will comes from db.
tasks.
1. check the file name and its validation
2. check most updated files in local directory (these most-updated files will load to the  ftp server)
3. If Updated files are not available then Setp 1 will run again after every 30 min
4. If file are available then these file should be removed from the local directory, after uploading

Reply me asap
please.

Answer 28

Alex,
Did you every find the cause to the exception during the .Connect call?  I have the same problem.

Answer 29

Hi

We've got an SSIS task that FTPs up to a windows server (not sure if its 2005 or 2008, but I suspect the latter).

We're using basically the same script as the first post in this thread, but when we call the SendFiles method we get error code 0xC001602A with no message.

At first we obviously assumed that the whole send was failing but it looks like the actual action is succceeding, but it is still reporting an error?

Any help much appreciated

Many Thanks

Mark Middlemist

Answer 30

Hi Again

Just a little follow-up

We haven't as yet got this fixed but the one thing I should add is that it is actually carrying out the action, whether it be creating/overwriting or deleting a file, but it is still reporting the error. Any thoughts anyone?

Many thanks in advance for any help

Mark Middlemist

Answer 31

Hello Patrick,

I am having real trouble with deleting files within a FTP server....I used the above code snippet from Mathew to delete files and it worked absolutely fine and then i stumbled across another problm of deleting files having date extn in it.

I reckon script task doesn't quite support wildcards and hence cant use filename*.csv to delete files.

I then used ur code

ftp.Connect()
'Get file listing
Dim fileNames() As String
Dim folderNames() As String
ftp.GetListing(folderNames, fileNames)
'ftp the files
ftp.DeleteFiles(fileNames)

And it deleted all the files from the FTP directory.......is there anyway I can use ur code snippet and delete a file like filename_170609.csv (today's file with today's date extn)...

---

Thanks a bunch ! Sweta

Answer 32

Thanks so much Matthew Qualls . It works great.

Answer 33

Hi Matthew,

  I tried the above and put messageboxes in the code and it was showing the FTP connection was established but file is not written on Mainfrmae. any clue?

Thanks,
Srinivas

Answer 34

HI,


I have used the same code in script task, tested the connection successfully. Bu when i sned the files, script task remains in execution mode (yellow).

i tried remote location with blank also "".

Please help me to solve this issue.

 Public Sub Main()
        
        Dim success As Boolean

        Try

            'Create the connection to the ftp server

            Dim cm As ConnectionManager = Dts.Connections.Add("FTP")

            'Set the properties like username & password

            cm.Properties("ServerName").SetValue(cm, "XXXXXXXXXXXXXXXX")

            cm.Properties("ServerUserName").SetValue(cm, "XXXXXXX")

            cm.Properties("ServerPassword").SetValue(cm, "XXXXXX")

            cm.Properties("ServerPort").SetValue(cm, "21")

            cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout

            cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb

            cm.Properties("Retries").SetValue(cm, "1")

            'create the FTP object that sends the files and pass it the connection created above.

            Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))

            'Connects to the ftp server
            ftp.Connect()

            success = ftp.Connect()
            If (success = True) Then

                'Build a array of all the file names that is going to be FTP'ed (in this case only one file)

                Dim files(0) As String '= "\\S-SQL16\IPM Source Data\DataAcademySource\ADDRESS_ROLES.csv"


                files(0) = "\\S-SQL16\IPM Source Data\DataAcademySource\ADDRESS_ROLES.csv"

                'Dim remotePath As String = "/"
                'ftp the file 

                'Note: I had a hard time finding the remote path directory. I found it by mistake by creating both the FTP connection and task in the SSIS package and it defaulted the remote path setting in the FTP task.

                ftp.SendFiles(files, "\", True, False) ' the True makes it overwrite existing file and False is saying that it is not transferring ASCII

                Dts.TaskResult = Dts.Results.Success

            Else



                Dts.Events.FireError(0, "ScripTask", _
                "Connection Failed", String.Empty, 0)
            End If


            ftp.Close()

        Catch ex As Exception

            Dts.TaskResult = Dts.Results.Failure

        End Try



    End Sub

End Class

Answer 35

Hi,

 try this

        public void Main()

        {

 

            string serverName  = Dts.Variables["User::FTPServerName"].Value.ToString();

            string remotepath = Dts.Variables["User::FTPDestinationPath"].Value.ToString();

            string userID  = Dts.Variables["User::FTPUserID"].Value.ToString();

            string password = Dts.Variables["User::FTPPassword"].Value.ToString();

            string sourcePath = Dts.Variables["User::SourcePath"].Value.ToString();

 

            try

            {

                string Result = FtpPut(serverName, userID, password,sourcePath, remotepath);

                Dts.TaskResult = (int)ScriptResults.Success;

            }

            catch(Exception)

            {

                Dts.TaskResult = (int)ScriptResults.Failure;

            }

        }

private string FtpPut(string serverName, string userID, string password, string sourcePath, string destinationPath)

        {

            string result = string.Empty;

            string sourceFile = Path.Combine(sourcePath, "StagingData.txt");

            string batchFilePath = Path.Combine(sourcePath, "FTPBatch.txt");

 

            StringBuilder sb = new StringBuilder();

            sb.Append("open " + serverName  + Environment.NewLine);

            sb.Append(userID + Environment.NewLine);

            sb.Append(password + Environment.NewLine);

            sb.Append("ascii" + Environment.NewLine);

            sb.Append("quote site conddisp=delete cylinders primary=30 secondary=20 recfm=fb" + Environment.NewLine);

            sb.Append("quote site lrecl=1000" + Environment.NewLine);

            sb.Append("put ");

            sb.Append("\"");

            sb.Append(sourceFile);

            sb.Append("\"");

            sb.Append(" '" + destinationPath + "'" + Environment.NewLine);

            sb.Append("close" + Environment.NewLine);

            sb.Append("bye" + Environment.NewLine);

 

            string realBatchText = sb.ToString();

 

           

 

            //create the batch file.

            byte[] realBatchTextBytes = new UTF8Encoding(false, true).GetBytes(realBatchText);

            using (Stream writer = new FileStream(batchFilePath, FileMode.Create, FileAccess.Write, FileShare.None))

            {

                writer.Write(realBatchTextBytes, 0, realBatchTextBytes.Length);

            }

 

            //Process Info to FTP and run Batch File created

            ProcessStartInfo startInfo = new ProcessStartInfo();

            startInfo.Arguments = string.Format("-s:\"{0}\"", batchFilePath);

            startInfo.FileName = "ftp.exe";

            startInfo.CreateNoWindow = true;

            startInfo.UseShellExecute = false;

            startInfo.WindowStyle = ProcessWindowStyle.Hidden;

            startInfo.RedirectStandardOutput = true;

 

            //Start Process

            using (Process process = new Process())

            {

                process.StartInfo = startInfo;

                process.Start();

                result = process.StandardOutput.ReadToEnd().ToString();

                process.Close();

            }

            File.Delete(batchFilePath);

            return result;

        }

    }

}

 

Answer 36

Hello,

I am using SQl server 2005, i can use only VB.NET

Answer 37

This is the function converted from C# to VB.net. hope this will be helpful

Private Function FtpPut(ByVal serverName As String, ByVal userID As String, ByVal password As String, ByVal sourcePath As String, ByVal destinationPath As String) As String

 

 

        Dim result As String = String.Empty

        Dim sourceFile As String = Path.Combine(sourcePath, "StagingData.txt")

        Dim batchFilePath As String = Path.Combine(sourcePath, "FTPBatch.txt")

 

        Dim sb As StringBuilder = New StringBuilder()

 

        sb.Append("open " + serverName + Environment.NewLine)

        sb.Append(userID + Environment.NewLine)

        sb.Append(password + Environment.NewLine)

        sb.Append("ascii" + Environment.NewLine)

        sb.Append("quote site conddisp=delete cylinders primary=30 secondary=20 recfm=fb" + Environment.NewLine)

        sb.Append("quote site lrecl=1000" + Environment.NewLine)

        sb.Append("put ")

        sb.Append("""" + sourceFile + """")

        sb.Append(" '" + destinationPath + "'" + Environment.NewLine)

        sb.Append("close" + Environment.NewLine)

        sb.Append("bye" + Environment.NewLine)

 

 

        Dim realBatchText As String = sb.ToString()

        'create the batch file.

 

        Dim realBatchTextBytes As Byte() = New UTF8Encoding(False, True).GetBytes(realBatchText)

 

        Using writer As Stream = New FileStream(batchFilePath, FileMode.Create, FileAccess.Write, FileShare.None)

            writer.Write(realBatchTextBytes, 0, realBatchTextBytes.Length)

        End Using

 

        'Process Info to FTP and run Batch File created

        Dim process As New Process

 

        With process

            With .StartInfo

                .FileName = "ftp.exe"

                .Arguments = String.Format("-s:\"" + batchFilePath + """)

                .CreateNoWindow = True

                .UseShellExecute = False

                .WindowStyle = ProcessWindowStyle.Hidden

                .RedirectStandardOutput = True

            End With

            .Start()

            result = .StandardOutput.ReadToEnd().ToString()

            .Close()

        End With

        File.Delete(batchFilePath)

        Return result

    End Function

 

Thanks,
Srinivas

Answer 38

You could use the SSIS FTP Task++ from eaSkills. It has wildcard support and SFTP capabilities and it's free.

http://www.easkills.com/ssis/ftptask

Answer 39

Try the SSIS FTP Task++ from eaSkills. It works for me using a Unix FTP Server and it's free.

http://www.easkills.com/ssis/ftptask

Answer 40

hello Mark,  I just  had the same as you had.... still working on solving the problem...  I suspect that has something to do with VISTA.... my SSIS FTP package was working good in XP...  I just have my OS changed to VISTA last week and noticed this problem today...

Did you find out any thing with your problem ?

 

Thanks

kythanh

Answer 41

whn i use this code its getting an error saying "dts is not declared"

Answer 42

Hi,

I believe there may be a bug in the ftp.SendFiles command.  In my case, if the file already exists on the server, the command throws error "System.Runtime.InteropServices.COMException (0xC001602A): Exception from HRESULT: 0xC001602A"  This happens even though I have the overwrite property set to True.  The only workaround I've been able to find for this is to first delete the file using ftp.DeleteFiles, then call ftp.SendFiles.

I'm running SQL Server 2008 (10.0.2531) on Windows Server 2008 R2 and my SSIS package is coded in Visual Studio 2008 R2.

Anyone else have this issue and come up with a way to get ftp.SendFiles to overwrite?

Answer 43

It took awhile, but I found the real bug (or I could be doing something wrong).

In my Script Task, if all I do is execute the command ftp.SendFiles it will send the files and overwrite them if they exist - all good there.  However, in my case I want to immediately confirm the file is actually on the FTP server - I don't want to trust that the file is there soley on the fact that ftp.SendFiles didn't throw an exception, so I call ftp.GetListing and loop over the results to ensure the file I just sent exists on the FTP server, so far so good.  If this is the first time the file is being transferred and thus you are not overwriting anything this will work, however, if you are overwriting a file it will fail consistently. 

The only way I've been able to get this to work is to first copy all the files to the FTP server, disconnect from the FTP server using ftp.Close, reconnect using ftp.Connect and then call ftp.GetListing.  If anyone is interested, I can post code samples which will reproduce this bug on your machine.

Answer 44

Hi Matheww, Thank you for the code.I am a newbie to SSIS. It was very helpful. The script task succeeded, however it did not FTP the file to the server. Need help with this. Thanks, Nivi.

Answer 45

Hi,

those who are facing 0xC001602A exception, try setting the UsePassiveMode to true.

cm.Properties("UsePassiveMode").SetValue(cm, true)

Thanks,

Rency

---

Rency

Answer 46

Hi,

I am a newbie to SSIS and been looking for awhile whether it is possible to write a dynamic script (SQL sp or VB or whatever) that dynamically passes different FTP related parameters (FTP server, login, password, filepath, file, etc...) and FTPs/uploads files to different FTP servers.

Basically, I am trying to create a SSIS package that will reference a SQL table for various input parameters (i.e. file name, FTP server where to send the file, user login/password for that particular FTP server, etc...)

Not sure if this is clear what I am asking, but using FTP task in SSIS does not allow this - it seems that you can only specify one FTP server and related credentials and cannot vary this dynamically at package's run time.

Thanks,

Answer 47

Hi Rossoneri76,
If you are going to use the FTP task built in to SSIS, You can store certain settings within SQL, FTP server, Login etc the only thing you can't do is the FTP password as it needs to be manually added within the package. It can be done with script though.

If you store the settings within SQL, use a Execute SQL to get the values and store each one of them in a seperate variable.. then within the FTP connection manager, go to expressions, select the relevant one and but in the relevant variable. Therefore when the package runs it picks up the settings from sql assigns them to a variable and then the ftp connection manager picks up the required settings.

Hope that helps.

 

 

Answer 48

How do I pass three files all at once? Below did not work for me. It only passed the first file.

Dim files(0) As String

files(0) = "Drive:\FullPath\FileName1" & "Drive:\FullPath\FileName2" & "Drive:\FullPath\FileName3"

ftp.SendFiles(files, "/Enter Your Remote Path", True, False)

Answer 49

Thanks - I do this script but receive files (ftp.ReceiveFiles).  The files are saved to the D: drive yet once received the C: drive space is eaten up.  It'll eventually release it but I can't figure out how to force it - I assume it's a cache thing?  Issue being I send over several large files so the C: drive goes from over 1GB to about 50MB as the files get transferred.  Sometimes in the middle of it a chunk of drive space is reclaimed but usually it gets eaten up over the FTP process and then later (sometimes minutes, sometimes days) the drive space will automagically free up.  Definitely related to this process - I can click the SSIS with this script task and see the drive space disappear as it runs.  I can't determine how to force the cache to empty?

Answer 50

Hi Matthew, I am trying to use your script and I get an error and I don't know what I am missing.

Error 2 Name 'Dts' is not declared. C:\Users\XXXXXX\AppData\Local\Temp\13\SSIS\ST_fc630fdb35fe4f6ab3d11eba5905f5e5\ScriptMain.vb 64 13 ST_fc630fdb35fe4f6ab3d11eba5905f5e5

Am I missing a reference??

I will appreciate your help.

Thanks,

 

Answer 51

I see why you build a client connection from code. It seems hard or undoable to add an FTP task from the toolbox and program it's properties in a loop.

Answer 52

Hi Matthew, How do you put your code in SSIS? I'd like move my output files in batch to FTP. Please help me.

Thank you,

Thomas

 

Answer 53

Thanks for your suggesttion, it worked when remotedirectory is ""

Answer 54

Thank You Mathew you have saved me from hours of painful gui work.  Your code is awesome the one thing I noticed and would suggest to those of you encountering no file after a successfull run is did you have the correct \ at the end of the recieve file location.  I took me a few minutes to realize that I was not ending my path with a \.

My question is this how can you force this to fail in an SSIS package within a scheduled job on SQL 2005.  If i am locked out of the account it still shows success even though no connection or file was made.  it does display the below error in bids if I am debugging but settin the package to fail on error does not seem to achnowledge the error. (see below)

Error: 0xC001602A at ssis_fts_corp_prbz_net_ES1, Connection manager "{6449CE4A-6536-432E-945A-1FA79480F2A6}": An error occurred in the requested FTP operation. Detailed error description: The password was not allowed

thanks in advance for your time.

-Jbird

Answer 55

Thanks Matthew. That was very helpful. I have run this in C# for SSIS 2008 R2 and it works. Below is my code sample based off your VB version. Included is a check to only download files which do not exist on the local directory.

        public void Main()
        {

            string[] ftp_fileList;
            string[] ftp_folderList;
            string[] local_fileList;
            string[] getFiles;
            string serverName = Dts.Variables["User::serverName"].Value.ToString();
            string serverUserName = Dts.Variables["User::serverpUserName"].Value.ToString();
            string serverPassword = Dts.Variables["User::serverPassword"].Value.ToString();
            string localFolder = Dts.Variables["User::localFolder"].Value.ToString();


            ConnectionManager cm = Dts.Connections.Add("FTP");
            cm.Properties["ServerName"].SetValue(cm, serverName);
            cm.Properties["ServerUserName"].SetValue(cm, serverUserName);
            cm.Properties["ServerPassword"].SetValue(cm, serverPassword);
            cm.Properties["ServerPort"].SetValue(cm, "21");
            cm.Properties["Timeout"].SetValue(cm, "0");
            cm.Properties["ChunkSize"].SetValue(cm, "1000");
            cm.Properties["Retries"].SetValue(cm, "1");

            FtpClientConnection ftp = new FtpClientConnection(cm.AcquireConnection(null));

            ftp.Connect();

            //Get a list of all available files on the ftp server.
            ftp.GetListing(out ftp_folderList, out ftp_fileList);

            //Get a list of all existing files on the local drive.
            local_fileList = Directory.GetFiles(localFolder);

            //Build a list of files which need to be downloaded.
            StringBuilder getList = new StringBuilder();
            foreach (string ftpFile in ftp_fileList)
            {
                string comp = localFolder + ftpFile;
                if (Array.IndexOf(local_fileList, comp) == -1)
                {
                    getList.Append(ftpFile);
                    getList.Append("|");
                }
            }
            if (getList.Length > 0)
            {
                getList.Remove(getList.Length - 1, 1);
                getFiles = getList.ToString().Split('|');

                ftp.ReceiveFiles(getFiles, localFolder, true, false);
            }

            ftp.Close();

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

Answer 56

Hi there,

Wondering if anyone has had to do something like this before:

I have to export some files to an external FTP site outside our domain.  Initially I could not connect to it using something like FileZilla so contacted our IT team about it.  Apparently we have to specify a proxy server first and then connect to the FTP site.

Using a tool like FileZilla I can specify a generic proxy of "HTTP/1.1 using CONNECT method", then enter in the proxy host (proxy.domain.local), a port and my domain user name and password.  So then when I connect to the ftp site (ftp.domain.com) and specify a user name and password it works.

How do you do this using an SSIS Script Task???  I can use a SSIS Script task to FTP files to other FTP sites that don't need to go through the proxy server but I just can't do it to external sites that require authentication from the proxy server.

I've searched this forum, seen some connections like [e-mail] or ftp.domain.com:21.ftp_user_name.ftp_password but they don't work.  Any thoughts would be appreciated.

Kind regards,

Chris

Answer 57

Hi Chris,

The standard SSIS FTP Task (and classes) doesn't support advanced features like proxy support. If you can use third-party solutions, check the commercial CozyRoc SFTP Task. It includes support for both SSH and FTPS protocols and supports HTTP, SOCKS4, SOCKS5, FTP Site, FTP Open and FTP User proxy types.

---

SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Answer 58

 Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain


    Public Sub Main()
        Try
	   Dim dbConn As ConnectionManager = Dts.Connections("connectionstringnamehere")
            

            dbConn.ConnectionString = "Data Source=datasource;User ID=userid;Password=password;Initial Catalog=databasename;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;"

            Dim ftpConn As ConnectionManager = Dts.Connections("ftpconnectionstringname")
            ftpConn.Properties("ServerName").SetValue(ftpConn, "servername")
            ftpConn.Properties("ServerPort").SetValue(ftpConn, "21")
            ftpConn.Properties("ServerUserName").SetValue(ftpConn, "username")
            ftpConn.Properties("ServerPassword").SetValue(ftpConn, "password")


            Dts.TaskResult = Dts.Results.Success

        Catch ex As Exception
            Dts.TaskResult = Dts.Results.Failure
        End Try
    End Sub

End Class

create connection strings for production server and ftp sertver first and then use this script in script task.

srikrishna

Answer 59

I have been working on scheduling an SSIS package to ftp files for a couple of days. This worked flawlessly.

Thank you so much for your time and effort putting this script task together.

You the MAN!

Answer 60

Can you please tell me how you would set this up for SFTP instead of FTP?

Thanks to you , the FTP script works, but now I need to create a package sending files to an SFTP site.

Answer 61

i changed the TennesseePaul's C# code to VB code....and added some extra code in it works perfect

Thanks Ton saved my time....

Imports System
Imports System.Collections
Imports System.Data
Imports System.IO
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Enum ScriptResults
    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Class ScriptMain
    Public Sub Main()
        Try
            Dim ftp_fileList As String()
            Dim ftp_folderList As String()
            Dim local_fileList As String()
            Dim getFiles As String()
            Dim LocalPaths As String = Dts.Variables("User::LocalPaths").Value.ToString()
            Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
            'Set the properties like username & password
            cm.Properties("ServerName").SetValue(cm, "xxxxxx")
            cm.Properties("ServerUserName").SetValue(cm,"xxx")
            cm.Properties("ServerPassword").SetValue(cm, "xxx")
            cm.Properties("ServerPort").SetValue(cm, "21")
            cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout
            cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb
            cm.Properties("Retries").SetValue(cm, "1")
            'create the FTP object that sends the files and pass it the connection created above.
            Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))

            ftp.Connect()

            ftp.SetWorkingDirectory(Dts.Variables("FtpPaths").Value.ToString())
            'Get a list of all available files on the ftp server.
            ftp.GetListing(ftp_folderList, ftp_fileList)

            'Get a list of all existing files on the local drive.
            local_fileList = Directory.GetFiles(LocalPaths)

            'Build a list of files which need to be downloaded.
            Dim getList As New Text.StringBuilder()
            For Each ftpFile As String In ftp_fileList
                Dim comp As String = LocalPaths & ftpFile
                If Array.IndexOf(local_fileList, comp) = -1 Then
                    getList.Append(ftpFile)
                    getList.Append("|")
                End If
            Next
            If getList.Length > 0 Then
                getList.Remove(getList.Length - 1, 1)
                getFiles = getList.ToString().Split("|"c)

                ftp.ReceiveFiles(getFiles, LocalPaths, True, False)
            End If

            ftp.Close()

            Dts.TaskResult = ScriptResults.Success
        Catch ex As Exception

            ' Fire error and set result to failure
            Dts.Events.FireError(0, "FTP Script Task", "Error: " + ex.Message, String.Empty, 0)
            Dts.TaskResult = ScriptResults.Failure

        End Try
    End Sub
End Class

---

s

Answer 62

hi ,Rency Fernandes

your code :
cm.Properties("UsePassiveMode").SetValue(cm, true)

it does not working.

it almost throw exception 
RESULT:0xC001602A,{A754A946-7A38-4022-A8B9-31CE3C4E82CB},,.... The operation timed out

but thank for all the same.

Answer 63

The time out typically is not code related, it is your environment, also consider posting your issue using your own thread.

---

Arthur My Blog

Answer 64

Consider posting your issue into your own (new) thread.

I can assume here the values do not get passed as you expect them because it does not connect doing this two different ways.

---

Arthur My Blog

Answer 65

I was going to create my own thread, I was just following the forum rules for posts.  I will move this to another thread.