Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

No SFTP Task Component in SSIS 2005/2008? No Problem! Expand / Collapse
Author
Message
Posted Monday, May 10, 2010 10:17 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 128, Visits: 912
Comments posted to this topic are about the item No SFTP Task Component in SSIS 2005/2008? No Problem!
Post #919419
Posted Monday, May 10, 2010 10:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 7, 2012 12:31 PM
Points: 6, Visits: 103
Cool article. I like CozyRoc's SFTP task too
Post #919430
Posted Tuesday, May 11, 2010 12:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 14, 2012 1:10 PM
Points: 15, Visits: 113
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.
Post #919461
Posted Tuesday, May 11, 2010 2:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 18, 2012 4:11 AM
Points: 27, 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)
Post #919501
Posted Tuesday, May 11, 2010 7:12 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 1, 2013 8:10 AM
Points: 445, Visits: 840
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.






Post #919659
Posted Tuesday, May 11, 2010 7:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 11, 2014 1:06 PM
Points: 67, Visits: 259
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.
Post #919681
Posted Tuesday, May 11, 2010 7:47 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 128, Visits: 912
"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.
Post #919684
Posted Tuesday, May 11, 2010 8:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 10:33 AM
Points: 1, Visits: 159
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]

Post #919710
Posted Tuesday, May 11, 2010 8:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:04 PM
Points: 6,594, Visits: 8,882
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
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
Post #919714
Posted Tuesday, May 11, 2010 9:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 19, 2011 11:57 AM
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::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;
}


Post #919764
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse