Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 13759
  • Last Modified:

FTP files using SSIS script task.

I have a requirement in SSIS to connect to a remote ftp site, download all files to a local drive and then remove only the remote files that I've transferred. The file names will be different each time.

I've got the attached code in a script task in my SSIS package:

Which always gives me the following error:

Exception from HRESULT: 0xC0016031
  at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSFtpClientConnection90.ReceiveFiles(String[] remoteFileNames, String localPath, Boolean overWrite, Boolean transferASCII)
   at Microsoft.SqlServer.Dts.Runtime.FtpClientConnection.ReceiveFiles(String[] remoteFileNames, String localPath, Boolean overwrite, Boolean isTransferAscii)
   at ScriptTask_613a13b6b4eb4c41bb9ab2fb7e238a98.ScriptMain.Main()


Files exist and the connection appears to be made...

Am I missing something obvious here?

JC


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, "******")
 
        cm.Properties("ServerUserName").SetValue(cm, "*******")
 
        cm.Properties("ServerPassword").SetValue(cm, "******")
 
        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))
 
        ftp.Connect()
 
        Dim fileNames() As String
        Dim folderNames() As String
 
        ftp.GetListing(folderNames, fileNames)
        
 
        ftp.ReceiveFiles(fileNames, "o:\pods\", True, True)
 
        'ftp.DeleteFiles(fileNames)
 
        ftp.Close()
 
 
        'Catch ex As Exception
 
        '    Dts.TaskResult = Dts.Results.Failure
 
        'End Try
 
        'Dts.TaskResult = Dts.Results.Success
 
    End Sub
 
End Class

Open in new window

0
misdevelopment
Asked:
misdevelopment
  • 2
1 Solution
 
misdevelopmentAuthor Commented:
As a further note, I've just noticed this:

Error: 0xC0016031 at Package, Connection manager "{867A9FF2-E663-4F72-BA5D-6BAFC76FE273}": No files to transfer. This error can occur when performing a Send or Receive operation and no files are specified for the transfer.

Which is suggesting that no files are being picked up from getlistings.... or that I'm not passing them to the string array properly? Hmmmmm anyone?
0
 
misdevelopmentAuthor Commented:
I feel silly.

I didn't set the working directory to "/outgoing"

Nothing to see here :-)

Thanks,

JC
0
 
EE_AutoDeleterCommented:
Melvinivitch,
Because you have presented a solution to your own problem which may be helpful to future searches, this question is now PAQed and your points have been refunded.

EE_AutoDeleter
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now