?
Solved

Managed File Transfer using SSIS

Posted on 2013-06-18
6
Medium Priority
?
1,159 Views
1 Endorsement
Last Modified: 2016-02-11
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
Comment
Question by:Putoch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39258247
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
 
LVL 1

Assisted Solution

by:yechan
yechan earned 1002 total points
ID: 39260146
To add to acperkins comment.  One tool that we using to establish a SFTP connection is winscp.  I believe it's free too.
0
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 498 total points
ID: 39261956
You can use SFTP task from Link
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:Putoch
ID: 39263763
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
 
LVL 1

Accepted Solution

by:
yechan earned 1002 total points
ID: 39266821
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
 

Author Closing Comment

by:Putoch
ID: 39344885
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question