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

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.

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)...

• Edited by DayHappy Monday, July 20, 2009 7:03 PM

 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

        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;

        }

    }

}

Hello,

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

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

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

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?

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.

Hi,

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

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

Thanks,

Rency

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,

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.

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)

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,

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

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

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

        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;
        }

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 ftp_user_name@ftp.domain.com or ftp.domain.com:21.ftp_user_name.ftp_password but they don't work.  Any thoughts would be appreciated.

Kind regards,

Chris

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.

 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

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!

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.

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

Thanks Ton saved my time....

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.

• Edited by Monday, December 03, 2012 3:31 AM additional information

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.

Hello,

Really its very helpful. I need to exactly as it is... but unfortunately I am getting error.

On FTP my files are on particular folder like   Data\SQL\SystemInfo\20131016

I use that command in for ftp folder. After I changed and retried it but no luck.. I am getting the error

=====================================================================

Hello Santhosh,

Can you help me in fixing the script in my environment. I need to use the same one you did but getting errors. I am using ipaddress in place of servername.

================================================================