Script task Issue during FTP

Question

Script task Issue during FTP

vijay.msbi

SSC-Addicted

Points: 422
More actions
February 26, 2010 at 9:41 am

#138455

Hello,
exactly i am using bellow code to send a file to FTP server.But my script task remains in execution mode all the time ( Yellow ).
can any please suggest wat happening in my code . I have tested my FTp connection in Script task as well as in FTP connection manger : working perfectly
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Try
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
cm.Properties("ServerName").SetValue(cm, "S-SQL15.apollo.zeus.internal")
cm.Properties("ServerUserName").SetValue(cm, "FTPUser")
cm.Properties("ServerPassword").SetValue(cm, "ftpconnection@123")
cm.Properties("ServerPort").SetValue(cm, "21")
cm.Properties("Timeout").SetValue(cm, "0")
cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb
cm.Properties("Retries").SetValue(cm, "1")
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
'Connects to the ftp server
http://ftp.Connect()
Dim files(0) As String
files(0) = "C:\TestFtp.txt"
http://ftp.SendFiles(files, "/", True, False)
http://ftp.Close()
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class

Viewing 11 posts - 1 through 10 (of 10 total)

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

Tim Mitchell SSCoach Points: 15681 More actions · Answer 1

Vijay,

Just curious, is there a reason you can't use the native SSIS FTP task rather than the script task? If you can, it will probably save you time and effort.

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

vijay.msbi SSC-Addicted Points: 422 More actions · Answer 2

HI,

i am using Sql server 2005, tried with FTP task,getting one error: Destination folder does not start with "/"

I found that FTP task not working properly in SQL server 2005. so opting for script task..

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 3

The Path should always start with "/"

what is the destination path that you trying to send to.. may be we help you by correcting the path

vijay.msbi SSC-Addicted Points: 422 More actions · Answer 4

Hi,

My destination path : D:\IPM Source Data\DataAcademySource

i tried with forward slash (/) infront of my target path, but it is not working. even if i go for FTp task by configuring FTp connection manager, receiving merror message : target path should start with /

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

You can't reference drive paths from http://FTP..

In FTP all paths are relative to your root path which should always be /

Figure out what your relative path is by using the command line version of FTP and that should help you fix your problem..

CEWII

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 6

Assuming that you have already changed the Folder share settings... what name did you give to your shared folder..

If you did not already change the Folder properties..do it by rt clking on the folder -->Properties-->sharing (check Share this Folder) and then give it a name.. now you have access to the shared folder so for example if you call your shared drive as "SharedFolder"

you can use it as /Sharedfolder in your FTP connection

Or if you want to access sub directory inside the "SharedFolder"

/SharedFolder/SubDirectory

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

Hold on..

FTP does not work on shared folders. FTP works on the FTP protocol. shared folders work on the SMB protocol. They are NOT interchangeable.

Now within FTP you might have mapped some shared folders on other machines but they still flow from the users root of /

So have you done what I suggested yet?

CEWII

vijay.msbi SSC-Addicted Points: 422 More actions · Answer 8

Elliott,

As you said, \\S-SQL15\IPM Source Data\Source is my relative path as well as my Destination folder.so i can take / in destination variable.

Bu as per you last post, FTp does not work on shared folders.

"IPM Source Data" folder is Shared folder, is it ok to keep it as shared folder.

When tried through command prompt to connect FTP server, After connection successful, if i execute any FTp command,

system hangs out. is this due to shared folder with in relative path.

even i have tried removing the sharing option of the folde, but still FTP Client hangs out

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 9

vijay.msbi (3/3/2010)
Elliott,
As you said, \\S-SQL15\IPM Source Data\Source is my relative path as well as my Destination folder.so i can take / in destination variable.
Bu as per you last post, FTp does not work on shared folders.
"IPM Source Data" folder is Shared folder, is it ok to keep it as shared folder.
When tried through command prompt to connect FTP server, After connection successful, if i execute any FTp command,
system hangs out. is this due to shared folder with in relative path.
even i have tried removing the sharing option of the folde, but still FTP Client hangs out

Ok..

If I read that right. \\S-SQL15\IPM Source Data\Source represents where you are wanting to copy files from your side TO the FTP side. Correct? That should be fine provided the user calling the SSIS package has permissions to that UNC.

It should be ok to keep it as a shared folder, however, I usually recomment not having spaces in folder names like this. But that is me..

I think the FTP hanging problem is simpler than that.. I am going to guess about a few things but I'm fairly sure I am right.

1. You are using the MS provided command line FTP tool, which you may not know does NOT support Passive connections.

2. You are trying to contact an FTP server that is outside your network.

3. Your machine is sitting behind a firewall.

What all this means is that this isn't so easy. The reason I wanted you to do it manually is so that you could see the whole relative path without having to grapple with the code to get there. So I'm going to make another recommendation. If you use Firefox (I use both IE and FF), get the add-on called FireFTP. Use this add-on to connect to the FTP server using Passive connections (which I think is the default) and navigate to the directory where you need to get to, once there, copy the path out and use it in your code.

I know I have reference passive connections a couple times, in FTP the way it works is that it uses two connections, one for commands, and one for data. The problem is how the data one is created causes problems with MOST firewalls. So a passive connection handles the connectivity differently to prevent this issue.

Is this clear?

CEWII

vijay.msbi SSC-Addicted Points: 422 More actions · Answer 10

Thanks Elliott. I have contacted Admin team to fix issue.