How can I copy and rename file on FTP server using SSIS

Question

Post reply

How can I copy and rename file on FTP server using SSIS

bob-245330

Old Hand

Points: 394
More actions
August 18, 2010 at 10:58 pm

#144622

I am doing this in script task. Here is my code. This step is successfull but not doing anything. Can some one help me?
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.VisualBasic.FileIO.FileSystem
Imports System.IO.FileSystemInfo
Imports System.IO
Imports System.Net
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Private Declare Function InternetCloseHandle Lib "wininet.dll" (ByVal HINet As Integer) As Integer
Private Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" (ByVal sAgent As String, ByVal lAccessType As Integer, ByVal sProxyName As String, ByVal sProxyBypass As String, ByVal lFlags As Integer) As Integer
Private Declare Function InternetConnect Lib "wininet.dll" Alias "InternetConnectA" (ByVal hInternetSession As Integer, ByVal sServerName As String, ByVal nServerPort As Integer, ByVal sUsername As String, ByVal sPassword As String, ByVal lService As Integer, ByVal lFlags As Integer, ByVal lContext As Integer) As Integer
Private Declare Function FtpRenameFile Lib "wininet.dll" Alias "FtpRenameFileA" (ByVal hFtpSession As Integer, ByVal lpszExisting As String, ByVal lpszNew As String) As Boolean
Public Sub Main()
Dim INet, INetConn As Integer
Dim RC As Boolean
INet = InternetOpen("MyFTP Control", 1, vbNullString, vbNullString, 0)
INetConn = InternetConnect(INet, "testserver", 0, "username", "password", 1, 0, 0)
MsgBox(INetConn)
RC = FtpRenameFile(INetConn, "\\inetdir\input.txt", "input.hl7") ' want to move to root directory
InternetCloseHandle(INetConn)
InternetCloseHandle(INet)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Thank You

Viewing 6 posts - 1 through 5 (of 5 total)

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

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 1

Since the operation involves remote server...there will be issues related with user authorization.

An approach could be as follows:

Using FTP Task bring the files --> Edit the files using FST --> Using FTP Task Send the files back.

This is modular and straight approach.

Raunak J

bob-245330 Old Hand Points: 394 More actions · Answer 2

Thank you for the reply. We want to rename after the transmission because we don't want the other process on the FTP server picks up before transmission is done. Any sample code to rename file after transmitting?

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 3

Why don't you transmit the edited file to FTP location?

Raunak J

bob-245330 Old Hand Points: 394 More actions · Answer 4

We have a process on FTP server which runs every 5 minutes to pick up hl7 files. If we transmit hl7 file sometimes the process might pick up before transmission is completed. Thats why we thought of transmitting txt file and change file extension.

rerp04 Newbie Points: 7 More actions · Answer 5

Hello i have used your code and it worked for me, try to check the password (happened to me and i got in the msgbox 0) once changed to the correct one the renamed went ok without any issues.