Script Task: FTP

This Script appeared originally on MSDN's Integration Services Forum courtesy Matthew Qualls: http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/2b7cf6b5-d16d-44cd-954d-68e9ba98ee91/

' 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

Last edited Sep 11, 2009 at 10:04 PM by toddmcdermid, version 1

Comments

swethareddypalla Oct 29, 2012 at 2:02 PM 
I am i doing any mistake hear. I don't know remote path and my file name is pmain.rs.out.x085.ae i have to see the data in that file. when i run the package it is successfull but I am not seeing that file can any one please help me out.


public void Main()
{
try
{
// TODO: Add your code here
ConnectionManager cm = Dts.Connections.Add("FTP");

//Set the properties like username & password


//cm.Properties["ServerName"].SetValue(cm, "10.32.130.28"/*Dts.Variables["ftpDropLoc"].Value.ToString()*/);

cm.Properties["ServerName"].SetValue(cm, "facs.state.mi.us");

cm.Properties["ServerUserName"].SetValue(cm, "#085AE1");

cm.Properties["ServerPassword"].SetValue(cm, "MEDC$A12");

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.


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

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


string[] files = new string[] { @"C:\AMS_FTP_ETL\pmain.rs.out.x085.ae" };




//files[0] = @"C:\Report1.pdf";//Dts.Variables["fileDropLoc"].Value.ToString();


//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.ReceiveFiles(files, "", true, false);
// the True makes it overwrite existing file and False is saying that it is not transferring ASCII


ftp.Close();
}


catch
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
Dts.TaskResult = (int)ScriptResults.Success;
}

}
}