Solved

Managed File Transfer using SSIS

Posted on 2013-06-18
6
969 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
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 334 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 166 total points
ID: 39261956
You can use SFTP task from Link
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 334 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

914 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now