Solved

Managed File Transfer using SSIS

Posted on 2013-06-18
6
954 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
Comment Utility
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
Comment Utility
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
Comment Utility
You can use SFTP task from Link
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:Putoch
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

772 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

13 Experts available now in Live!

Get 1:1 Help Now