• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1290
  • Last Modified:

Managed File Transfer using SSIS

Hi,
I would like to set up an automated process to send files to a particular server. The server will be using managed file transfer - and I have SSIS 2005 and 2008.
I have tried to set this up using the FTP task on SSIS but I am getting a failure with connection.

I'm not sure if this is due to a connectivity issue, or because I simply can not use the FTP task when what I should be using is a MFT?

can anyone help advise me please?
Thanks,
P
1
Putoch
Asked:
Putoch
3 Solutions
 
Anthony PerkinsCommented:
SSIS FTP task only supports standard FTP.  It does not support FTP over SSL or SFTP.  For that you will have to use a third party tool.
0
 
yechanCommented:
To add to acperkins comment.  One tool that we using to establish a SFTP connection is winscp.  I believe it's free too.
0
 
Alpesh PatelAssistant ConsultantCommented:
You can use SFTP task from Link
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
PutochAuthor Commented:
Thank you guys, I am trying to automate the process. I did try using filezilla and Winscp successfully when doing a manual transfer. I see the SFTP task from Link but I am currently using SS2005 not 2008 which Is a requirement.

So I have been looking at trying to use the Script task to load the file.
I am not familiar with using this, and have been trying to understand the process by looking online.
One example I was looking at was here: http://stackoverflow.com/questions/409491/best-method-to-sftp-or-ftps-files-via-ssis

But now i'm trying to understand how to read this -
are there any good websites or references that you would advise to use to help me with this?
In the simplest form I just want to:

open a connection to the sftp server
copy a file from my directory
paste this copy to a specific directory on the SFTP server.

I have tried using a batch command that opens the connection (using username and password) and then moving the file to the specified location - I have done this on the command line manually - but I cannot set this up as a scheduled job as it won't allow the use of a password I need a public key authentication which I can't get right now.

So I thought perhaps using the SSIS Script I could call my batch files and get my process to work that way?
I think once I:
 declare the location of the bat file no1. (to open connection and then tells bat file no 2 to run)
declare the location of bat file no 2 (which changes the directory to the specified directory and moves the files to that location)
declare the server ip, server user name and server password

And then I need a command to help run those batch files using those variables.

I've tried to put something together below but without proper understanding on how it should flow



 Public Sub Main()
		'
        Dim PathToBatch_bat As String = "d:\filelocation\PSFTP.bat"
        Dim PathToScript_bat As string =  "d:\filelocation\movefile.bat"
        Dim ServerName As String = "123.456.789"
        Dim ServerUserName As String = "user"
        Dim ServerPassword As String = "password"
        Dim ServerSubdirectory As String = "/location on SFTP/to move files/to"

Dim command_line As String = PathToBatch_bat & " " & ServerName & " -l " & ServerUserName & " -pw " & ServerPassword & " -b " & PathToScript_bat

Open in new window


Can anyone help a little please?
0
 
yechanCommented:
Not sure what program you are using.  But if you are using WinSCP, here is the page that shows how to upload files onto the SFTP server.

http://winscp.net/eng/docs/scriptcommand_put

Below is an example I am using as an Argument when calling the WinSCP executable.

"/console /command \"option batch abort\" \"open sftp://UserAccount:Password@123.123.123.123:99\" "\"put C:\someFile.zip /someFolderOnTheSFTP "\exit"

It's not 100% correct because it's part of an expression and trying to escape backslashes always gives me a headache.  Hopefully it will provide as a guide to get you started.
0
 
PutochAuthor Commented:
It turned out that there was an issue on the other server side, when I was trying to connect, it didn't recognize my ip so it wouldn't allow me to load. once that was resolved, I was able to use filezilla to manually transfer. and i'm working on creating automated file transfer using SSIS and .net I will give you an update when I am successful with that and hopefully this will help someone else. thanks all.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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