File System Task - Source file wildcard?

Question

File System Task - Source file wildcard?

Herpington_McDerpington

Ten Centuries

Points: 1016
More actions
March 4, 2015 at 10:50 am

#295690

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.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

If you can find a DOS command to do it (DOS rename supports wildcards, I think), you should be able to use an Execute Process task to run it.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 2

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.

A bit off topic but is it necessary in SSIS to escape the "\"es like that?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246979 More actions · Answer 3

Hi Jeff, have a quick read here and you'll see that the answer is 'yes'.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Herpington_McDerpington Ten Centuries Points: 1016 More actions · Answer 4

Phil that's a great recommendation. I'm going to head down that path today! At a cursory glance it looks like I can definitely implement command line syntax, so I don't see why it wouldn't work.

Thank you thank you thank you!

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 5

Phil Parkin (3/5/2015)
Hi Jeff, have a quick read here and you'll see that the answer is 'yes'.

Thanks, Phil. I really appreciate it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)