SSIS ForEach FTP: Ability to pass Servername, Username, Password

Question

SSIS ForEach FTP: Ability to pass Servername, Username, Password

leighton_d

SSC Eights!

Points: 883
More actions
January 8, 2009 at 7:09 am

#126826

I have a SSIS package setup to process a table that contains "group" information. The information includes the output filename, FTP servername, Username, Password, and so forth. Each group generates a separate .txt file that needs to be transferred to a remote FTP server. The package works great creating the files, but I've not found a way to pass the remote FTP servername, username, and password to the SSIS FTP task. I found the expressions that allow me to pass parameters for local path, remote path, etc.
For now, I'm going to setup separate FTP tasks outside of the ForEach container that will fire off after all of the files have been generated. Ultimately, I want to have each file sent as it's generated so that as I add new groups to the master control table, they will automatically be handled within the ForEach container and not require anything else to be added to the package.
Any idears?
Thanks!

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

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

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

leighton_d (1/8/2009)
I have a SSIS package setup to process a table that contains "group" information. The information includes the output filename, FTP servername, Username, Password, and so forth. Each group generates a separate .txt file that needs to be transferred to a remote FTP server. The package works great creating the files, but I've not found a way to pass the remote FTP servername, username, and password to the SSIS FTP task. I found the expressions that allow me to pass parameters for local path, remote path, etc.
For now, I'm going to setup separate FTP tasks outside of the ForEach container that will fire off after all of the files have been generated. Ultimately, I want to have each file sent as it's generated so that as I add new groups to the master control table, they will automatically be handled within the ForEach container and not require anything else to be added to the package.
Any idears?
Thanks!

You can set expression for properties in a connection manager. The expression can point to a variable you setup and iterate in your for each loop. To setup expressions right-click on your connection manager and select Properties. Scroll-down and find expressions.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

leighton_d SSC Eights! Points: 883 More actions · Answer 2

Thanks for the quick response! As much as I found other elements of SSIS packages that would accept parameters, I figured that there had to be a way to do the same with the FTP task. Since I've new to the SSIS realm (about 3 days now:)...), I'm very much in the learning mode.

leighton_d SSC Eights! Points: 883 More actions · Answer 3

I found the "Expressions" in the connection manager, but there is not an expression for "ServerPassword".

?

COZYROC One Orange Chip Points: 28499 More actions · Answer 4

leighton_d (1/8/2009)
I found the "Expressions" in the connection manager, but there is not an expression for "ServerPassword".
?

Most probably this is made on purpose for security reasons. You have to setup the password using in the ConnectionString property.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

leighton_d SSC Eights! Points: 883 More actions · Answer 5

Ah! Makes sense. Might you have a connection string example that would speed me on my way?

Thanks again...

COZYROC One Orange Chip Points: 28499 More actions · Answer 6

leighton_d (1/8/2009)
Ah! Makes sense. Might you have a connection string example that would speed me on my way?
Thanks again...

Try setting each and every property like this:

ServerName=server name;ServerUserName=user name;ServerPassword=password;

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

leighton_d SSC Eights! Points: 883 More actions · Answer 7

Thanks a bunch! I'm hoping to finish this project today and start on another one. I've been generating and sending these files from our SQL2000 for the last few years. DTS routines generate the files, and then I add a step to the SQL job to FTP file files. Every time I needed to add a new group, I had to create a new DTS routine and a new job. SSIS allows me to automate everything and utilize control tables to do everything. Now, I'll only need to add records into the control tables anytime we add new groups into the process. Woo-hoo!

Have a fun one!

Doug

leighton_d SSC Eights! Points: 883 More actions · Answer 8

Well, I'm finally getting the time to work on this again. I setup an FTP task and set the connection property to:

ServerName=ftp.cnotify.com; ServerUsername=CentralHigh; ServerPassword=Fjr3ri8t;

When I executed the FTP task, I received the following error message:

"Nonfatal errors occurred while saving the package: Error at cNotifyAbsence: The connection "ServerName=ftp.cnotify.com; ServerUsername=CentralHigh; ServerPassword=Fjr3ri8t;" is not found."

I left the "FtpConnection" parameter in the General section of the FTP Task blank, figuring that setting the "Connection" property with a variable would take care of it. It appears to be expecting an FTP connection in the Connection Manager.

Any other ideas?

Thanks!

COZYROC One Orange Chip Points: 28499 More actions · Answer 9

leighton_d (2/7/2009)
Well, I'm finally getting the time to work on this again. I setup an FTP task and set the connection property to:
ServerName=ftp.cnotify.com; ServerUsername=CentralHigh; ServerPassword=Fjr3ri8t;
When I executed the FTP task, I received the following error message:
"Nonfatal errors occurred while saving the package: Error at cNotifyAbsence: The connection "ServerName=ftp.cnotify.com; ServerUsername=CentralHigh; ServerPassword=Fjr3ri8t;" is not found."
I left the "FtpConnection" parameter in the General section of the FTP Task blank, figuring that setting the "Connection" property with a variable would take care of it. It appears to be expecting an FTP connection in the Connection Manager.
Any other ideas?
Thanks!

You should not set the FTP task property, but ConnectionString property of your FTP Connection manager.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

leighton_d SSC Eights! Points: 883 More actions · Answer 10

Ok, this isn't fun anymore!

I set it up to modify the ConnectionString of the FtpConnectionManager. That made sense once you pointed out the obvious to me :Whistling:.

When I execute the FtpTask by itself, it fails, but without an error message being displayed, I'm at a loss to determine why. So, I backed up a bit, and setup the FtpTask with hard-coded values just to ensure it works at at. I created a new FtpTask, creating a new FtpConnectionManager to go with it. I specified the ServerName, Username, and Password. I then clicked "test connection" and it tested successfully. In the FtpTask "File Transfer" section, I manually selected the text file to send, and manually keyed in the remote path/filename. I set the IsTransferAscii to "True".

When I execute the FtpTask, it briefy turns yellow, then red. Is it possible to display the reason that it's bombing? This is my first project in this environment and I have lots to learn, but I'm under a time constraint to get this project up and running.

Thanks again!

leighton_d SSC Eights! Points: 883 More actions · Answer 11

I did some exploring and found the error message displayed in the "Output" window:

SSIS package "cNotifyAbsence.dtsx" starting.

Error: 0xC002918B at FTP Task, FTP Task: Unable to send files using "FTP Connection Manager".

Task failed: FTP Task

SSIS package "cNotifyAbsence.dtsx" finished: Success.

I then transferred the file using WsFtp just to verify that the remote Ftp server was functioning properly, and it worked fine.

I enjoy a mystery as much as the next guy, but not when there's a deadline looming...

COZYROC One Orange Chip Points: 28499 More actions · Answer 12

leighton_d (2/8/2009)
I did some exploring and found the error message displayed in the "Output" window:
SSIS package "cNotifyAbsence.dtsx" starting.
Error: 0xC002918B at FTP Task, FTP Task: Unable to send files using "FTP Connection Manager".
Task failed: FTP Task
SSIS package "cNotifyAbsence.dtsx" finished: Success.
I then transferred the file using WsFtp just to verify that the remote Ftp server was functioning properly, and it worked fine.
I enjoy a mystery as much as the next guy, but not when there's a deadline looming...

This is a different issue compared to your original problem. It will be better to create a separate message thread for it. I have found here[/url] issue similar to your new issue. I think what you have to do is find out what is happening from the server side, check the log, etc.

Having said that, I will not discuss this new issue any further because it is not related to the original issue anymore.

If you are under time pressure, I would recommend to check our commercial library SSIS+ . It contains enhanced Secure FTP (SFTP) Task, with support for SSH connections and FTP over SSL (FTPS) and normal connections. It is tested already with many remote FTP server types.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

leighton_d SSC Eights! Points: 883 More actions · Answer 13

Thanks a bunch for all of your assistance. It just kills me that such a fundamental function doesn't work, especially when it's a two Microsoft boxes talking to each other. From the same machine that I'm working on the package, I'm able to use the Windows FTP client to connect to the remote FTP server and upload a file without a problem. This at least indicates that it's not a network, authentication, or permissions issue on the remote server.

To expedite this project, I'm going to go back to the "tried and true" method I've been using. I add an additional step to the job that executes the package. This step issues a command line call that runs the Windows FTP client. I create a text file with the username, password, and transfer commands. I realize that this isn't the best way to approach the problem, but I can't afford to spend anymore time on this right now. I have a huge backlog of projects with short deadlines. I will revisit this issue as time permits.

Again, much thanks!

satish.t Mr or Mrs. 500 Points: 521 More actions · Answer 14

The format is below

"Server IP: Port.loginAccount.loginPWD"

EX: "192.168.1.1:21"+ @[user::FtpAcct]+"."+ @[user::FtpPwd]

192.162.1.1:21.Username.password

This worked for me... 🙂

All the best