Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

SSIS FTP Task

Nick Rubino
 Experience 1 : No ServerPassword? 
The client required a scheduled transfer of multiple TIF files from one server to another over FTP.  Since this task was initially implemented using SSIS, the change seemed to be trivial; I would create a new FTP Connection in the job’s Connection Manager window and replace the current copy task (a Script Task using System.IO functions) with an FTP task inside a For Each Loop to iterate over each file, copying them one by one to the FTP server. 
 
 
 

 

The “Set FTP Paths” Script Task sets path variables that the FTP Task uses.

 

 

After a few “trial and error”s due to some changes in the parameters the process ran successfully in debug mode from my local computer.  Happy that I had completed the task, I started the deployment to our QA server so that I could test a scheduled instance of the job.  This is where I ran into a bit of a headache.

Deployment consisted of creating a configuration file (.dtsConfig file) with all the specific parameters, copying this along with the job file up to the server, and scheduling the job using the config file in Jobs under SQL Server Agent of SQL Server Management Studio.  After getting through a few environment errors related to the configuration file, I found the following error in my log.

“An Error occurred in the requested FTP operation. Detailed error description: The password was not allowed.”

After a bit of troubleshooting I finally realized the password in the Connection String that I was setting using an expression in the FTP Connection was not being carried over correctly to my QA server.  In fact neither was the Username and the only reason the job was running correctly on my local computer was that I had manually set the values into the FTP Manager Connection Editor window when I initially created the FTP Connection.  I then went on to try to set the Username and Password specifically in the Expression Editor for the FTP Connection.  As you can see in the below screen shot, SSIS lets you set the Username (ServerUserName) but does not let you set the Password for an FTP Server.

(As mentioned in the comment section of a link I reference below, it makes absolutely no sense for them to exclude this property considering the passwords for FTP transfers are sent in plain text .. but I wont get into that.)

The reason the job kept failing out on the QA server was that the Username and Password were not being included in the ConnectionString of the FTP Connection in the dtsConfig file. After a bit of internet searching I came across this article.  In it Frank goes over exactly what the problem was with my job; when you create an FTP Connection in SSIS you cannot set the password through a Connection String expression.  As he displays, one way to get around this is to write a Script Task and manually set the “ServerPassword” parameter with a hard coded string.

SSIS 2012 seems to use the FTP Task so unless something changes, be ready to use this hack in your future SSIS packages using FTP.

Experience 2 : What’s Thumbs.db?

This experience was a little less frustrating than Experience 1 but still annoying.  For my specific package, after the FTP transfer is completed, there is validation performed that compares a count of how many files were supposed to be sent with how many files were actually sent.  This validation failed so I went out to the destination FTP server and found a file called Thumbs.db.  For whatever reason I did not use an expression to filter only the .TIF files I wanted from the source folder, and instead just took every file there assuming there would never be anything else in there.

The counts in the validation were off and were causing the job to fail due to the Thumbs.db file Windows Server 2003 creates to show the thumbnails of images in a Windows explorer window.  This file is not visible when viewing the folder but the FTP process was picking it up and causing the validation to fail.  After adding a simple filter of *.TIF files, the process did not pick up the Thumbs.db and ran successfully.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>