Solved

Managed File Transfer using SSIS

Posted on 2013-06-18
6
1,058 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 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Many to one in one row 2 39
Finding Where Clause Value in SQL Views and SP 21 40
Merge join vs exist 3 25
T-SQL: problem comparing datetime 4 48
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

749 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