Home » C++ ProgrammingRSS

FTP script task using connection on SSIS

Hi,
under the script that I use for upload file using SSIS asp.net task.


My problem....
If I comment this line
 'ftp.SendFiles(files, "", True, False) ' the True makes it overwrite existing

upload made successfull without failure instead If I leave uncomment I got an error but files upload anyway correct.

Someone can give me some explains about this behavior?
TNKS Alen, Italy


------------------
Public Sub Main()
        'Create the connection to the ftp server
        Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
        'create the FTP object that sends the files and pass it the connection created above.
        Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))

        Try
            'Set the properties like username & password
            cm.Properties("ServerName").SetValue(cm, "xxxxxx")
            cm.Properties("ServerUserName").SetValue(cm, "xxxxx")
            cm.Properties("ServerPassword").SetValue(cm, "xxxxx")
            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")

            'Connects to the ftp server
ftp.Connect()

            '****************************************
            ' Potrebbe servire in futuro la tengo
            '****************************************
            ''Get file listing
            'Dim fileNames() As String
            'Dim folderNames() As String
            'ftp.GetListing(folderNames, fileNames)
            ''ftp the files
            'ftp.DeleteFiles(fileNames)


            'Build a array of all the file names that is going to be FTP'ed (in this case only one file)
            Dim files(1) As String
            files(0) = "\\manny-slave\appWork\FACT-FINDER\a.txt"
            files(1) = "\\manny-slave\appWork\FACT-FINDER\b.txt"

            'ftp the file
            '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, "", True, False) ' the True makes it overwrite existing file and False is saying that it is not transferring ASCII
ftp.Close()
            Dts.TaskResult = Dts.Results.Success

        Catch ex As Exception

ftp.Close()
            Dts.TaskResult = Dts.Results.Failure

        Finally
ftp.Close()

        End Try

    End Sub

 

4 Answers Found

 

Answer 1

I know that you're executing this script  from within SSIS but this isn't really a question about SSIS. Its more about the FTP capabilities within .Net. Hence, I recommend you visit a more appropriate forum - its unlikely that you'll get a reply from here.

-Jamie

 

Answer 2

Hi, all
 I had made  some changes in the script  according to my requirements.
I was wanted to save all ftp  information into database and <File.Name> That need to be upload  should come from database.

Now, my problem  is this, If password  is wrong / or FTP task  is failed due to any reason. it does not appair , and script complete successfly with green color.

that is wrong, once i couldnt get the connection  why its complete sucessfuly.????
arshad_4b@hotmail.com
--------------------------------------------------

' 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

Imports System.Data.SqlClient

Imports System.Collections

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

PublicSub Main()

'

' Add your code here

'==============================================================

Dim objCon As SqlConnection

Dim objCommand As SqlCommand

Dim objReader As SqlDataReader

Dim sConString AsString

sConString = "server=localhost;database=DtsPkg;uid=sa;pwd=sa"

objCon = New SqlConnection(sConString)

objCon.Open()

objCommand = New SqlCommand("Select top 1* From [DTSSID]", objCon)

objReader = objCommand.ExecuteReader()

objReader.Read()

Dim sDWHServerFTP_IP AsString = objReader.Item("DWHServerFTP_IP").ToString()

'Dim sServerUserName As String = objReader.Item("DWHServerFTP_UserName").ToString()

Dim sDWHServerFTP_Pwd AsString = objReader.Item("DWHServerFTP_Pwd").ToString()

Dim sDWHServerFTP_Port AsString = objReader.Item("DWHServerFTP_Port").ToString()

Dim sDWHServerFTP_Timeout AsString = objReader.Item("DWHServerFTP_Timeout").ToString()

Dim sDWHServerFTP_ChunkSize AsString = objReader.Item("DWHServerFTP_ChunkSize").ToString()

Dim sDWHServerFTP_Retries AsString = objReader.Item("DWHServerFTP_Retries").ToString()

Dim sDWHServerFTP_UserName AsString = objReader.Item("DWHServerFTP_UserName").ToString()

objReader.Close()

'objReader.Item("empno").ToString()

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, sDWHServerFTP_IP) 'You define Server IP also

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

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

cm.Properties("ServerPort").SetValue(cm, sDWHServerFTP_Port)

cm.Properties("Timeout").SetValue(cm, sDWHServerFTP_Timeout) 'The 0 setting  will make it not timeout

cm.Properties("ChunkSize").SetValue(cm, sDWHServerFTP_ChunkSize) '1000 kb

cm.Properties("Retries").SetValue(cm, sDWHServerFTP_Retries)

'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

objCommand.CommandText = "SELECT (select top 1 dbo.DTSSID.PServerBakLocation FROM dbo.DTSSID)+ dbo.tblFname.[FileName] Filename from dbo.tblFname where tblFname.DataTransferYN=0"

objReader = objCommand.ExecuteReader()

Dim objFilesList AsNew System.Collections.Specialized.StringCollection

While objReader.Read()

objFilesList.Add(objReader.Item("filename").ToString)

EndWhile

objReader.Close()







'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) = objReader.Item("PServerBakLocation").ToString() 'this is the file name and location for source

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

Dim sFilesArray(objFilesList.Count) AsString

Dim sFile AsString

ForEach sFile In objFilesList

sFilesArray(objFilesList.IndexOf(sFile)) = sFile

Next

ftp.Connect()

ftp.SendFiles(sFilesArray, "/ftpuser", 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

EndTry

Dts.TaskResult = Dts.Results.Success



objCon.Close()

System.Console.ReadLine()

'==============================================================

EndSub

EndClass

 

Answer 3

The Script to download files  from an ftp  server works well for one file. 

What if I want to download multiple files from an ftp location 
Eg - /NetworkActivity_1379_*_03-16-2010.log.gz (The * here indicates all files matching the pattern)...


Any help appreciated...


Cheers
Pramod Paluri
Senior ETL developer
 

Answer 4

You can download multiplue files  from FTP server  .

i.e.     /NetworkActivity_1379*

The abouve statment download all the files whose name begin  with  NetworkActivity_1379. by the same way you can modify your wild card according to your requirment. I am able to download multiple file  with the same procedure
If you need any further details, I 'll be glad to help you. 
Thanks,
Muhammad.
 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter