qube09
asked on
SSIS for each loop and the ftp task in sql 2005
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.
IMPORTANT****************
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
thanks
It would be ok to overlay the evaluated expression with the proper values but can not seem to get it to work.
IMPORTANT****************
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
thanks
ASKER
yes but I was hoping to be more precise so that I don't delete a file that I have not downloaded which is I am using the files in the local folder as a guide
OK.... So you have to write expression in FTP task for Remote path...
like this...
Thanks,
Saurabh
like this...
Thanks,
Saurabh
ASKER
actually you have pointed to what may be the best solution. apparently files can come in the following form abcDDhhmmss.nnn (where nnn is a number from 001-999.
how would one set up an expression or a variable to delete on abcDD*.* where DD is yesterday's date?
how would one set up an expression or a variable to delete on abcDD*.* where DD is yesterday's date?
If you want to go with this way... then take variable at package level and set it to yesterday's date in Script task at the top of your package ....
Now you can use this variable in FTP remote path expression...
But I haven't understand how your package is working , to suggest best...
Thanks,
Saurabh
Now you can use this variable in FTP remote path expression...
But I haven't understand how your package is working , to suggest best...
Thanks,
Saurabh
ASKER
overall -
all filenames are unique fortunately
1. download all ftp files in a specific folder
2. get all of filenames and load in a table with an identity column as a base for a simple do loop that loads a table with bulk. the table to be used for history and in conjunction with a second table to ensure that a file is not loaded twice
3. the last step is to create a list of filenames in the local folder;archive and delete from local and ftp folders
the part with the problem is a for each loop with a filename (string) on the for each loop.
there are two tasks in the container-file task and ftp task. the file task copies files to archive and the exec delete via ftp task
the file task copies any number of files to the archive folder on a differnt volume. oddly the
ftp task complains with [FTP Task] Error: Remote path "C:\ech\chr5601.39" doesn't start with "/". I would guess that the filename variable contain path info in addition to filename
and oddly the copy to the archive is not disturbed by that.
all filenames are unique fortunately
1. download all ftp files in a specific folder
2. get all of filenames and load in a table with an identity column as a base for a simple do loop that loads a table with bulk. the table to be used for history and in conjunction with a second table to ensure that a file is not loaded twice
3. the last step is to create a list of filenames in the local folder;archive and delete from local and ftp folders
the part with the problem is a for each loop with a filename (string) on the for each loop.
there are two tasks in the container-file task and ftp task. the file task copies files to archive and the exec delete via ftp task
the file task copies any number of files to the archive folder on a differnt volume. oddly the
ftp task complains with [FTP Task] Error: Remote path "C:\ech\chr5601.39" doesn't start with "/". I would guess that the filename variable contain path info in addition to filename
and oddly the copy to the archive is not disturbed by that.
If the following response is helpful then call it thinking-outside-the-box..
What if you rethink the process... Use a Robo-FTP script running as a windows service to download the files and then delete them off the remote FTP site only if the download is successful. You could write the script in a loop so it pulls the files as soon as they are added to the FTP site. Now your SSIS is simple because it only needs to process local files. You could take this one step further and write the script so that it executes an SQL statement and passes the downloaded file name as a parameter. This would allow you to import the files in almost real time as they are published on the remote FTP server.
What if you rethink the process... Use a Robo-FTP script running as a windows service to download the files and then delete them off the remote FTP site only if the download is successful. You could write the script in a loop so it pulls the files as soon as they are added to the FTP site. Now your SSIS is simple because it only needs to process local files. You could take this one step further and write the script so that it executes an SQL statement and passes the downloaded file name as a parameter. This would allow you to import the files in almost real time as they are published on the remote FTP server.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Otherwise you can delete FTP files on based on pattern...
write remote path like this....
/myftpurl.com/myRemoteFold
or file name start with File and if it is a txt file then write like this.
/myftpurl.com/myRemoteFold
Thanks,
Saurabh