Herpington_McDerpington (3/4/2015)

---

I'm working on an SSIS task for the following process.

Download FTP File with naming convention of "YESTERDAYSFILE_YESTERDAYSDATE_VARIABLETIMESTAMP.msg"

I've got this part working fine using a wildcard to select the file (only 1 file will ever match.

The issue I'm having is renaming the file.

When I use a File System Task to find the file I'm using this Expression to find the target file:

"E:\\ATT\\FTP\\DAILY DNC SCRUB FILE " + (DT_STR, 4, 1252) YEAR( GETDATE() ) + (LEN((DT_STR, 2, 1252) MONTH( GETDATE() ) ) == 1 ? ("0" + (DT_STR, 2, 1252) MONTH( GETDATE() ) ) : (DT_STR, 2, 1252) MONTH( GETDATE() ) )

+ (LEN((DT_STR, 2, 1252)DAY( DATEADD("dd", -1, GETDATE()) ) ) == 1? ("0" + (DT_STR, 2, 1252) DAY( DATEADD("dd", -1, GETDATE()) ) ) : (DT_STR, 2, 1252) DAY ( DATEADD("dd", -1, GETDATE()) ) ) + "*.msg"

However, this fails due to illegal characters in the source file name - obviously the asterisk I'm using to target the source file.

The first question I asked myself: Why do I even have a File System Task? Why not just rename the file as part of the FTP Task? It would seem, as far as I can tell, that it's simply not possible to do that. When I attempted that it was throwing an error indicating that I hadn't supplied a drive, although the expression I'd used did include a drive as well as an existing path.

I presume at this point I'll need another step in between the FTP Task and the File System Task to list the folder contents to target the file, but I'm not up to speed on that and while I research that further I figured I'd drop a post to see if others have any experience with what I'm trying to do. As always thank you for your time and assistance.