troubleshooting Question

SSIS for each loop and the ftp task in sql 2005

Avatar of qube09
qube09 asked on
Microsoft SQL ServerMicrosoft SQL Server 2005SSIS
8 Comments1 Solution2001 ViewsLast Modified:
I have been asked to create an SSIS package to download recurring files from an ftp site. I have set up a package that readily downloads and inserts multiple files into a table. The problem is removing the ftp files. I had set up a for each loop that reads the files in the load folder and then archives them and then attempts to delete the files from the ftp site. The archive, using a filename variable works but unfortunately presents an invalid filename to the ftp server. The archiver reads a local folder - c:\locfolder - and correctly copies multiple files to the archive. When presented to ftp the filename is set to - c:\localfolder\filename (where filename is the correct file name. The ftp task uses the same variable for filename.How to resolve that issue?

It would be ok to overlay the evaluated expression with the proper values but can not seem to get it to work.

1. this is sql 2005 sp2 not sql2008 or sql2012
2. i am using a for each loop with a file task and an ftp task and only one variable filename
which is produced by looping through the local folder
3. replacing the ssis tasks with a script is ok but no c# only vb

ANY solution must be clear on the specifics:
1. variables and expressions must be clear
2. if a script no c# and any variables clear
3. no internet links only a very clear answer to how to resolve the expression problem or
a script
Saurabh Bhadauria

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros