Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Stan Kulp-439977
Stan Kulp-439977
SSC Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 1095
Comments posted to this topic are about the item No SFTP Task Component in SSIS 2005/2008? No Problem!
Craig Lucas
Craig Lucas
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 103
Cool article. I like CozyRoc's SFTP task too
albertarun
albertarun
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 121
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
mackie
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 219
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
mrpolecat
SSC-Addicted
SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)

Group: General Forum Members
Points: 452 Visits: 856
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
A. Fox
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 279
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
Stan Kulp-439977
SSC Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 1095
"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
Peter-507067
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 170
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:BigGrinestination] + ":" + @[User:Tongueort] + " -pw=" + @[User:Tongueassword] + " " + @[User::MD5_BB] + " -cmd=" + @[User::Command]

Executable - @[User::SFTPC_LOG_LOCATION]
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6243 Visits: 10403
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

markmeiller
markmeiller
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 83
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:Tongueeriod"].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;
}



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search