none
How to pass parameters in sql job for ftp task in SSIS

    Question

  • Hi,

    I have a scenario where i need to download a particular file  from remote server to local server using FTP task but i need to give the parameter file name in the sql job so that when i run the job it should download only that  file.

    can you help me to achieve this.

    thanks,

    aravind-

    Wednesday, July 17, 2013 4:27 PM

Answers

  • Finally i gotout to work myself...

    First i created 2 variables param_Filename and localFileDirectory in ssis pacakge

    1) param_fileName -leave this value empty in SSIS pacakge

    we will be  passing file name like this '/Enter Your Remote Path/ABC_20130717' in the sql job.

    2) localFileDirectory - value will be Drive:\FullPath

    Paste the code in the script task and in the scripttask editor -> readonly variables- add both the variables to it.

        Public Sub Main()
            Try

    remote.directory.org
                '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, "remote.directory.org")
                cm.Properties("ServerUserName").SetValue(cm, "username")
                cm.Properties("ServerPassword").SetValue(cm, "password")
                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) = Dts.Variables("param_fileName").Value.ToString
                '"/EA/Test_Assessment/ACT/ACT20130601"
                'ftp the file
                ftp.ReceiveFiles(files, Dts.Variables("localFileDirectory").Value.ToString, True, False)

                ftp.Close()

            Catch ex As Exception

                Dts.TaskResult = ScriptResults.Failure

            End Try
            Dts.TaskResult = ScriptResults.Success
        End Sub

    ----------------------------------------

    then save the package in integration server , then create the job in SSMS -> give ur packages details in the step and in the setvalues tab add the parameter lik this \Package.Variables[User::param_fileName].Properties[Value] - /Enter Your Remote Path/ABC_20130717

    Run the job. i hope it will run successfully.

    thanks,

    aravind-

    • Marked as answer by aravind T Thursday, July 18, 2013 4:32 AM
    Thursday, July 18, 2013 4:32 AM

All replies

  • Hi,

    Here you go

    http://sqlage.blogspot.com/2013/07/ssis-how-to-use-ftp-task-download-file.html

    Thank you


    http://sqlage.blogspot.com/

    Wednesday, July 17, 2013 6:51 PM
  • Hi Aamir,

    thanks for the response but the way you have given is the simple FTP task download. the requirement is there a way to pass filename from sql job so that the FTP task will download the particular file from remote server to local server

    Wednesday, July 17, 2013 9:11 PM
  • Finally i gotout to work myself...

    First i created 2 variables param_Filename and localFileDirectory in ssis pacakge

    1) param_fileName -leave this value empty in SSIS pacakge

    we will be  passing file name like this '/Enter Your Remote Path/ABC_20130717' in the sql job.

    2) localFileDirectory - value will be Drive:\FullPath

    Paste the code in the script task and in the scripttask editor -> readonly variables- add both the variables to it.

        Public Sub Main()
            Try

    remote.directory.org
                '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, "remote.directory.org")
                cm.Properties("ServerUserName").SetValue(cm, "username")
                cm.Properties("ServerPassword").SetValue(cm, "password")
                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) = Dts.Variables("param_fileName").Value.ToString
                '"/EA/Test_Assessment/ACT/ACT20130601"
                'ftp the file
                ftp.ReceiveFiles(files, Dts.Variables("localFileDirectory").Value.ToString, True, False)

                ftp.Close()

            Catch ex As Exception

                Dts.TaskResult = ScriptResults.Failure

            End Try
            Dts.TaskResult = ScriptResults.Success
        End Sub

    ----------------------------------------

    then save the package in integration server , then create the job in SSMS -> give ur packages details in the step and in the setvalues tab add the parameter lik this \Package.Variables[User::param_fileName].Properties[Value] - /Enter Your Remote Path/ABC_20130717

    Run the job. i hope it will run successfully.

    thanks,

    aravind-

    • Marked as answer by aravind T Thursday, July 18, 2013 4:32 AM
    Thursday, July 18, 2013 4:32 AM