Create FTP Connection through script task in SSIS 2008

  • Hi All,

    Please help. I'm new to SSIS 2008. I'm using the code below to FTP File,

    Question :-

    1) Seem that the connection is open even though the SSIS already stopped execute. The source file cant be amend/delete after FTP.

    2) Any code can help to rename the destination file through below code or sub function.

    Imports System

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Runtime

    Imports System.IO

    <System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _

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

    '

    ' Add your code here

    Dim sSource, sTarget, sFileName As String

    Dim YYYY, YY, MM, DD, HH, MI As String

    YYYY = Now.Year

    YY = Mid(YYYY, 3, 2)

    MM = Now.Month

    DD = Now.Day

    HH = Now.Hour

    MI = Now.Minute

    If Len(DD) = 1 Then

    DD = "0" & DD

    End If

    If Len(MM) = 1 Then

    MM = "0" & MM

    End If

    If Len(HH) = 1 Then

    HH = "0" & HH

    End If

    If Len(MI) = 1 Then

    MI = "0" & MI

    End If

    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, "ftpserver1")

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

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

    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

    http://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) = "LocalServerpath"

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

    sFileName = Dts.Variables("User::EXPORT_REPORT_01").Value & MM & DD & YY

    files(0) = sFileName & ".dat"

    sTarget = sFileName & HH & MI & ".dat"

    '

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

    http://ftp.Close()

    Catch ex As Exception

    Dts.TaskResult = DTSExecResult.Failure

    End Try

    Dts.TaskResult = DTSExecResult.Success

    End Sub

    End Class

    Thanks in Advance!!

  • I'm looking to setup a similar process and found this script that might be helpful for you

    http://www.simple-talk.com/sql/ssis/quick-tip-performing-an-ftp-rename-in-a-ssis-script-task/

  • Thanks a lot... Problem resolved.

  • 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 successfully 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

    http://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.

    http://ftp.ReceiveFiles(files, "", true, false);

    // the True makes it overwrite existing file and False is saying that it is not transferring ASCII

    http://ftp.Close();

    }

    catch

    {

    Dts.TaskResult = (int)ScriptResults.Failure;

    }

    Dts.TaskResult = (int)ScriptResults.Success;

    }

    }

    }

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply