SSIS Foreach Container - Chopping part of a filepath off
-
Really confused today, and I've no idea why SSIS is doing this for any other reasons than to torment me.
I have a for each loop container with the collection page looking like this:
As you can clearly see, Fully Qualified is selected. I then have a file in a folder "\\srvfil01\Claims Autoload\RSA\RSA Motor\Pending", called "Monthly 31-12-2018.xlsx". So, one would expect the value of SourceFilePath (which is the variable the path is mapped to) to have the value "\\srvfil01\Claims Autoload\RSA\RSA Motor\Pending\Monthly 31-12-2018.xlsx". Well, like myself you'd be wrong. The value of the variable is "\\srvfil01\Claims Autoload\RSA\RSA Motor\Monthly 31-12-2018.xlsx" meaning that every other step fails, as the file "\\srvfil01\Claims Autoload\RSA\RSA Motor\Monthly 31-12-2018.xlsx" (unsurprisingly) doesn't exist.
Anyone know how to get SSIS to behave and actually give me the Fully Qualified path? I've never had this problem before, so honestly, I'm confused.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Monday, January 14, 2019 5:04 AMReally confused today, and I've no idea why SSIS is doing this for any other reasons than to torment me.
I have a for each loop container with the collection page looking like this:
As you can clearly see, Fully Qualified is selected. I then have a file in a folder "\\srvfil01\Claims Autoload\RSA\RSA Motor\Pending", called "Monthly 31-12-2018.xlsx". So, one would expect the value of SourceFilePath (which is the variable the path is mapped to) to have the value "\\srvfil01\Claims Autoload\RSA\RSA Motor\Pending\Monthly 31-12-2018.xlsx". Well, like myself you'd be wrong. The value of the variable is "\\srvfil01\Claims Autoload\RSA\RSA Motor\Monthly 31-12-2018.xlsx" meaning that every other step fails, as the file "\\srvfil01\Claims Autoload\RSA\RSA Motor\Monthly 31-12-2018.xlsx" (unsurprisingly) doesn't exist.
Anyone know how to get SSIS to behave and actually give me the Fully Qualified path? I've never had this problem before, so honestly, I'm confused.
I assume it's because Pending\* is not a standard filespec.
Do you have several 'Pending' folders underneath \\srvfil01\Claims Autoload\ ?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin - Monday, January 14, 2019 5:33 AM
I do, yes; so SSIS only retrieves files within a folder called Pending. I've used this set up before, however, and it works fine; this is a new project with similar requirements. I could set up a check before the rest of the tasks, i within the ForEach, but I don't understand why the "Pending\" is dropped when the behaviour isn't consistent.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
I've coded around it for now, with an Expression;
@[User::SourceFilePath] = LEFT( @[User::SourceFilePath], LEN( @[User::SourceFilePath] ) - FINDSTRING(REVERSE( @[User::SourceFilePath] ), "\\", 1 )) + "\\Pending" + RIGHT( @[User::SourceFilePath], ABS(FINDSTRING(REVERSE( @[User::SourceFilePath] ), "\\", 1 )))
Still no idea why, but it does the job. Odd. :unsure:Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Monday, January 14, 2019 5:51 AM
Rather unsatisfying!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Since you are traversing all the folders within \\srvfil01\Claims Autoload, why not just set your file mask as *.xlsx and then check the returned value for "\Pending\" before processing? It might run a little longer, but seems like it would avoid the grief you are going through currently.
Luther
- latkinson - Tuesday, January 15, 2019 6:46 AM
Right now, that won't be a problem, however, considering that the amount of files will grow in those folders, and fiels outside of a pending folder are only the one's I'm interested in, this would eventually start to have a decent impact on performance; so I'd rather make the considerations now rather than in a year (or 2's) time when I have use's complaining they need the process "fixed" NOW.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Tuesday, January 15, 2019 7:24 AM
Maybe there is an alternative approach.
1) (Programmatically) Create a list of all the Pending folders (with full UNC paths). You may be able to use an Object variable to hold the list.
2) Use the list as the basis of your FOREACH loopThe absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply