No SFTP Task Component in SSIS 2005/2008? No Problem!

Question

Post reply

No SFTP Task Component in SSIS 2005/2008? No Problem!

Stan Kulp-439977

SSChampion

Points: 10062
More actions
May 10, 2010 at 10:17 pm

#222367

Comments posted to this topic are about the item No SFTP Task Component in SSIS 2005/2008? No Problem!

Viewing 15 posts - 1 through 15 (of 60 total)

You must be logged in to reply to this topic. Login to reply

Craig Lucas SSC Veteran Points: 242 More actions · Answer 1

Craig Lucas

SSC Veteran

Points: 242

May 10, 2010 at 10:52 pm

#1165332

Cool article. I like CozyRoc's SFTP task too

albertarun SSC Veteran Points: 231 More actions · Answer 2

I am using a similar SFTP 3rd party application in one of my ODS loading SSIS package.

The problem is that the error handling is generalized and not specific.

I suggest searching for dlls that can be called through script task which can pass error messages to package variable which can be included in the sent mail task. You can also compare already downloaded files against your audit table there by avoiding duplicate downloads.

mackie Right there with Babe Points: 745 More actions · Answer 3

Nice article, thanks for sharing Stan. I very recently sat with the same issue, and after some investigation into different solutions to the issue I decided to use Core FTP light. I added variables to my SSIS package, configured an Execute Process task that passed the valid commands to the executable (via variables), added file archiving steps to ensure good housekeeping, and voila! Bob's your uncle. Worked like a charm. No extra coding required (other than basic variable expressions) 🙂

mrpolecat SSCertifiable Points: 6964 More actions · Answer 4

I've been doing this with DTS since SQL7. One catch I don't see mentioned is that you have to log in manually the first time to cache the key. Otherwise the process hangs waiting for the response from the user to verify the key. This can also be a problem when the key gets changed.

A. Fox SSC Enthusiast Points: 168 More actions · Answer 5

What concerns me about this approach is that you leave a batch file hanging around that contains your password. You have to be very careful in your cleanup and error handling routines so that you don't leave the auto-generated batch file sitting in your working directory.

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 6

"You have to be very careful in your cleanup and error handling routines so that you don't leave the auto-generated batch file sitting in your working directory."

That's why the last section of code deletes the batch file and the sftp script.

Peter-507067 SSC Journeyman Points: 81 More actions · Answer 7

Good article, I have been using bitvise's product sftpc.exe which has very good logging giving me the ability to not only use the SSIS embedded start process task but I can start it with a logging exe to produce transfer log files for each file which are in turn emailed to distribution lists.

Within the Start process task I have setup two Expressions to set the Arguments and Executable.

Arguments - @[User::SFTPC_LOG_ARGS] + " " + @[User::SFTPC_LOG_FILE_PATH] + " " + @[User::SFTPC_LOCATION] + " " + @[User::UserName] + "@" + @[User::Destination] + ":" + @[User::Port] + " -pw=" + @[User::Password] + " " + @[User::MD5_BB] + " -cmd=" + @[User::Command]

Executable - @[User::SFTPC_LOG_LOCATION]

WayneS SSC Guru Points: 95409 More actions · Answer 8

Good article.

Having that username/password in a batch file is an issue. If (for any reason, like the FTP server is down) the package fails, that file could be left behind, and then anyone could see it. You might be deleting it in the last step, but if the package fails before that step then that last step won't get run.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

markmeiller SSC-Addicted Points: 446 More actions · Answer 9

Good article. I have in the past used a simalar solution with Winscp, however we recently change some of our poliacys and the SSIS packagewas moved to a server where 3rd party apps where forbiden. To get around this i found out that you could easly use the c# ftp class in a script task to do the same thing & it's more relable, and faster cause the server doesn't have to fire up another application.

//Get Package Varables

String MyFTPSiteName = Dts.Variables["User::FTPHost"].Value.ToString();

String MyFTPLogin = Dts.Variables["User::FTPUser"].Value.ToString();

String MyFTPPassWord = Dts.Variables["User::FTPPassWord"].Value.ToString();

String MyFolder = Dts.Variables["User::Folder"].Value.ToString();

String MyPeriod = Dts.Variables["User::Period"].Value.ToString();

String MyFileName = Dts.Variables["User::File"].Value.ToString();

//Accept TLS/SSL Certificate

ServicePointManager.ServerCertificateValidationCallback = new System.Net.Security.RemoteCertificateValidationCallback(AcceptAllCertificatePolicy);

//Upload Data File

{

FtpWebRequest MyRequest = FtpWebRequest.Create(MyFTPSiteName + "/" + MyFileName ) as FtpWebRequest;

MyRequest.EnableSsl = true;

MyRequest.Credentials = new NetworkCredential(MyFTPLogin, MyFTPPassWord);

MyRequest.Method = WebRequestMethods.Ftp.UploadFile;

//Read File into byte array

FileInfo MyFileI = new FileInfo(MyFolder + "\\"+MyFileName );

FileStream MyFStream = new FileStream(MyFolder + "\\" + MyFileName, FileMode.Open, FileAccess.Read);

BinaryReader MyReader = new BinaryReader(MyFStream);

byte[] MyFile = MyReader.ReadBytes(int.Parse(MyFileI.Length.ToString()));

MyReader.Close();

MyFStream.Close();

MyRequest.ContentLength = MyFile.Length;

//Upload File

Stream MyStream = MyRequest.GetRequestStream();

MyStream.Write(MyFile, 0, MyFile.Length);

MyStream.Close();

FtpWebResponse MyResponse = MyRequest.GetResponse() as FtpWebResponse;

MyResponse.Close();

}

public bool AcceptAllCertificatePolicy(object sender, X509Certificate certificate, X509Chain chain, SslPolicyErrors sslPolicyErrors)

{

return true;

}

albertarun SSC Veteran Points: 231 More actions · Answer 10

awesome. this was something that I was looking for. Adding 3rd party apps to my ETL is a little mickey mouse for me.

Thank You very much.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

Nice article.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

John Barnett SSCrazy Points: 2086 More actions · Answer 12

An alternative method would be to set up a "virtual" FTP server using stunnel (www.stunnel.org) pointing to the remote sftp server, which you then connect using normal ftp uploads, and sftp handles the SSL element.

mecurioJ SSC Rookie Points: 41 More actions · Answer 13

Interesting article, but I chose a slightly different path in handling things. I am using the portable version of WinSCP, meaning there is no installation needed. They also expose Command Line arguments, meaning that all the stuff stored in the batch file is passed along as variables in the package. So I can store the variables in a database somewhere, call them when the package loads, then pass them along to the command line options. Consider the following:(written in C#...)

//Attaching the debugger in a 64-bit instance of SSIS will not work.

//MessageBox.Show("Attach the Debugger", "Attach the Debugger");

// TODO: Add your code here

ScriptResults TaskResult;

//jf 04132010 Created objects to hold variables set at package level

String WinSCPPath = Dts.Variables["WinSCPPath"].Value.ToString();

String WinSCPCom = Dts.Variables["WinSCPCom"].Value.ToString(); ;

String WinSCPini = Dts.Variables["WinSCPini"].Value.ToString();

String WinSCPLoggingPath = Dts.Variables["WinSCPLoggingPath"].Value.ToString();

String WinSCPLogFileName = Dts.Variables["WinSCPLogFileName"].Value.ToString();

String LoginName = Dts.Variables["LoginName"].Value.ToString();

String LoginPassword = Dts.Variables["LoginPassword"].Value.ToString();

String SiteURI = Dts.Variables["SiteURI"].Value.ToString();

String HostKey = Dts.Variables["HostKey"].Value.ToString();

String FileToPush = Dts.Variables["FileToPush"].Value.ToString();

String UploadDirectory = Dts.Variables["UploadDirectory"].Value.ToString();//UploadDirectory

//jf 04132010 Created object to hold reference for FireAgain of the FireProgress Control

bool fireAgain = false;

try

{

//jf 04132010 Attach winscp to the process within the Package

System.Diagnostics.Process winscp = new System.Diagnostics.Process();

Dts.Events.FireProgress("WinSCP process Created", 0, 0, 100, "WinSCP", ref fireAgain);

//jf 04132010 Pass variables into the Process object

//Tell the Process where to find the WinSCP object for loading

winscp.StartInfo.FileName = String.Format("{0}{1}"

, WinSCPPath

, WinSCPCom);

//Pass the location of the ini file and the logging xml file to the Process

winscp.StartInfo.Arguments = String.Format("/log={3}{1}"

, WinSCPPath

, WinSCPLogFileName

, WinSCPini

, WinSCPLoggingPath);

Dts.Events.FireProgress("Assigned Logging File", 10, 0, 100, "WinSCP", ref fireAgain);

//Do not launch a new shell "cmd" window

winscp.StartInfo.UseShellExecute = false;

//Redirect the input and output to the Logging Component

winscp.StartInfo.RedirectStandardInput = true;

winscp.StartInfo.RedirectStandardOutput = true;

//Do not show a cmd window

winscp.StartInfo.CreateNoWindow = true;

//Start the Process Thread

winscp.Start();

Dts.Events.FireProgress("WinSCP process Launched", 20, 0, 100, "WinSCP", ref fireAgain);

//Pass configuration information to WinSCP

winscp.StandardInput.WriteLine("option batch abort");

winscp.StandardInput.WriteLine("option confirm off");

//Pass Login information to WinSCP for Authentication

winscp.StandardInput.WriteLine(String.Format("open {0}:{1}@{2} -hostkey=\"{3}\""

, LoginName

, LoginPassword

, SiteURI

, HostKey));

Dts.Events.FireProgress("Remote SFTP Connection Established", 40, 0, 100, "WinSCP", ref fireAgain);

//Execute WinSCP Commands

winscp.StandardInput.WriteLine("ls");

//--change to the needed directory

winscp.StandardInput.WriteLine(String.Format("cd {0}", UploadDirectory));

Dts.Events.FireProgress("Remote SFTP File Action", 60, 0, 100, "WinSCP", ref fireAgain);

//--List the directory

winscp.StandardInput.WriteLine("ls");

//--push the file to the server

winscp.StandardInput.WriteLine(String.Format("put {0}", FileToPush));

Dts.Events.FireProgress("Process Finished", 80, 0, 100, "WinSCP", ref fireAgain);

Dts.Events.FireProgress(String.Format("put {0}", FileToPush), 0, 0, 0, "WinSCP", ref fireAgain);

//Close input to the file

winscp.StandardInput.Close();

Dts.Events.FireProgress("Remote SFTP Disconnected, WinSCP Closed", 100, 0, 100, "WinSCP", ref fireAgain);

//wait for WinSCP to Exit

winscp.WaitForExit();

MessageBox.Show(winscp.StandardOutput.ReadToEnd(), "WinSCP Output");

TaskResult = ScriptResults.Success;

}

catch (Exception e)

{

//If there is problem, fail the event and output the exception Message...

Dts.Events.FireError(999, e.Message, "WinSCP", string.Empty, 0);

TaskResult = ScriptResults.Failure;

//MessageBox.Show(e.Message);

}

Dts.TaskResult = (int)TaskResult;

Notice that we can also fire events that bubble up a message within the context of the package. This means no third party installation on the server, no batch file to be overwritten and most important of all, everything is saved in a script task in the package.

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 14

Pretty cool.

You should submit that as an article. I did what I did because it's the only thing I could figure out.

Your way is better.