May 3, 2016 at 9:28 pm
Hello. I have a directory that has close to 2100 files in it. Every day 2 more files are added to that directory. On average, once every 4-6 months, 4 files are put into this network directory. The files need to stay where they are because of an application that uses information in them every once in awhile.
Every morning (early hours) need to take the latest 2 files (sometimes the latest 4) and copy them to another directory so that they can be zipped and uploaded. At the time the copy needs to take place, those 2 (sometimes 4) files always have file attributes (modified/created date) of the previous day. The file names always contain the date from 2 days ago. For example today is 5-3-2016. The files I needed to grab this morning have this as part of the file name...
_160501_
...with a file extension of .837.
I've tried 6-7 tutorials but the package either doesn't copy anything or I get the error...
An error occurred with the following error message: "Illegal characters in path.".
I tried one solution that involved an Execute Process Task and Robocopy.exe using the switch /MAXAGE:1 where it grabs everything from the previous 24 hours. When you put that in SSIS and there are no files to copy, it errors out the package. I don't want the package to error out because files weren't there. I only want the package to error out on a real error.
I've never had to build any part of a package before that needs to use wild cards on file names and my attempts keep coming back to the illegal characters in path error. The other tutorials/blog posts I've followed seem like they were written for 2005 and 2008 and pretty dated. I'm using 2012.
I need to either attack this by using the created/modified date of yesterday to copy files to another folder or I need to be able to use a wild card on file names that has the date of 2 days ago in this format...
YYMMDD
...with underscores on both sides and the file extension would always be .837
What I have now is a Foreach Loop Container with a File System task in it that is set to copy. I'm using a variable for the source that goes as follows to try and iterate and look for a date in the file name...
@[User::home837] + "*_" + RIGHT((DT_WSTR,4)YEAR(dateadd("day", -2,GETDATE())) ,2)
+ ( month(dateadd("day",-2,getdate())) < 10 ? "0" + (DT_WSTR, 4) month(dateadd("day",-2,getdate())) : (DT_WSTR, 4) month(dateadd("day",-2,getdate())))
+ ( day(dateadd("day",-2,getdate())) <10 ? "0" + (DT_WSTR, 4) day(dateadd("day",-2,getdate())): (DT_WSTR, 4) day(dateadd("day",-2,getdate()))) + "_*.837"
When I hit the evaluate button on that I get the UNC path followed by *_160501_*.837 To me it looks like that should work but it's not. I get the illegal characters error.
Can anyone point me to a more recent forum post that would work in SSIS 2012?
TIA,
John
May 3, 2016 at 10:02 pm
I've tried a few variations of this...
The File System task operates on a single file or directory. Therefore, this task does not support the use of wildcard characters to perform the same operation on multiple files. To have the File System task repeat an operation on multiple files or directories, put the File System task in a Foreach Loop container, as described in the following steps:
Configure the Foreach Loop container On the Collection page of the Foreach Loop Editor, set the enumerator to Foreach File Enumerator and enter the wildcard expression as the enumerator configuration for Files. On the Variable Mappings page of the Foreach Loop Editor, map a variable that you want to use to pass the file names one at a time to the File System task.
Add and configure a File System task Add a File System task to the Foreach Loop container. On the General page of the File System Task Editor, set the SourceVariable or DestinationVariable property to the variable that you defined in the Foreach Loop container.
...found here...
https://msdn.microsoft.com/en-us/library/ms140185.aspx
...but it's still not working.
May 9, 2016 at 11:17 am
The file system task does not support using wildcards, as it is designed to work with a specific file.
To work around this, you would typically use a ForEach loop. The ForEach loop does support wildcards, and will execute whatever tasks you put within the loop once for EACH file that is found matching your specified pattern.
It looks to me like you've added the ForEach loop, but are still trying to use a wildcard for the file system task within the loop. Instead, what you want to do is configure the ForEach loop to assign the name of the file it finds to a variable, and then use that variable in your file system task. That way, the ForEach loop will find files based on your wildcard match, and for each file found that matches it will run your file system task for that specific file.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply