SSIS and FTP'ing multiple files to a server

Question

SSIS and FTP'ing multiple files to a server

Joel Witherspoon

SSC Veteran

Points: 275
More actions
October 13, 2010 at 3:57 pm

#149021

I'm a virtual beginner with SSIS but here's what I want to do:
I have 12 txt files that I need to send to an FTP server
I've successfully created an FTP connection manger (successful in that it works)
I don't want to create an FTP task for each file.
I've tried the ForEach loop container - with an FTP Task inside, but I've had no luck getting it to FTP multiple files.
Does anyone have step-by-step instructions on how to accomplish uploading 12 or more files to a server via FTP?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

Joel Witherspoon (10/13/2010)
I'm a virtual beginner with SSIS but here's what I want to do:
I have 12 txt files that I need to send to an FTP server
I've successfully created an FTP connection manger (successful in that it works)
I don't want to create an FTP task for each file.
I've tried the ForEach loop container - with an FTP Task inside, but I've had no luck getting it to FTP multiple files.
Does anyone have step-by-step instructions on how to accomplish uploading 12 or more files to a server via FTP?

The ForEach loops is likely the way to do it. If you can provide more information about what you tried and what happened we might be able to help you make it work.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Joel Witherspoon SSC Veteran Points: 275 More actions · Answer 2

Thank you for the reply.

In the BI studio, I have a sequence container that exports data into text files on my server. The text files are on this path: H:\ASPData\Data

I create an FTP connection manager (FTPConn) that I've tested and it works.

I drag a ForEach Loop Container (FELC) into the development area. In the FELC, the Enumerator is the Foreach File Enumerator. No Expressions.

The enumerator configuration is Folder: H:\ASPData\Data. The files: *.txt

Fully qualified. No variable mappings.

I place an FTP Task in the FELC. I configure the FTPConnection to use FTPConn. The Remote path is thus:

IsRemotePathVariable: False

RemotePath: /

OverwriteFileAtDest:True

The Local path is this:

IsLocalPathVariable: False

LocalPath: ASP File Connection Manager (Usage type: Existing folder. Folder: H:\ASPData\Data)

Operation: Send files

IsTransferAscii: False

In the FTP Task, there is a red circle with the error: Error 1 Validation error. FTP Task FTP Task: File usage type of connection "ASP File Connection Manager" should be "FileExists" for operation "Send".

Ganesh Lohani Hall of Fame Points: 3091 More actions · Answer 3

Change the following in your BIDS project.

In FELC, perform a variable mapping ( Let's say varA).

Make IsLocalPathVarianble = TRUE and

assign the variable you created earlier (VarA) to LocationPath.

This should solve the issue.

Thanks

Joel Witherspoon SSC Veteran Points: 275 More actions · Answer 4

Hot Damn!

It worked. I had created a user variable in the Foreach loop and I created another variable in the FTP Task. They were completely different. Once I used the variable in the FTP Task it worked. Thanks.

Ganesh Lohani Hall of Fame Points: 3091 More actions · Answer 5

Ganesh Lohani

Hall of Fame

Points: 3091

October 15, 2010 at 2:28 pm

#1237882

I am glad to know that you did it

Thanks

ken.tan Valued Member Points: 63 More actions · Answer 6

Hi,

I tried the steps in this forum, but I am getting this error message:

Error: 0xC0029179 at FTP Task, FTP Task: The variable "User::varA" is empty.

Task failed: FTP Task

Also, I cannot find the ASP Connection Manager as a connection type. I am running Visual Studio 2005.

Thank you in advance for your help.

Ken

Ganesh Lohani Hall of Fame Points: 3091 More actions · Answer 7

Try this:

Click FTP Task once inside the FELC, then go to the property page window

- it should be visible in right side of the project. Set delay validation to TRUE.

You should be OK then.

Thanks