January 2, 2009 at 6:08 am
I have a foreach loop container that I need to map the collection to 2 variables, not one. I need to map the directory information to one variable and then map the file name to another.
I'm using the container to traverse subfolders and copy files from those subfolders to a different box, but with a common folder instead of subfolders. I know this should be possible in SSIS, but I'm at a loss as to how to set it up.
My major problem is the subfolders and file names are not all the same length, so sticking an Execute SQL task with a substring function is out of the question. Too much work to get it done, and I can't be dynamic.
Does anyone know the answer to this or am I just stuck using individual subfolders on my destination too?
Thanks,
January 2, 2009 at 3:53 pm
Hey Brandie,
I have used the following ConnectionString expression in my destination connector to ADD a folder and date to a file name:
SUBSTRING( @[User::filename],1, FINDSTRING( REVERSE(@[User::filename]) , "\\", 1 ) )
+ "Archive\\"
+ SUBSTRING( @[User::filename],
FINDSTRING( REVERSE(@[User::filename]) , "\\", 1 ) +1,
FINDSTRING( REVERSE(@[User::filename]) , ".", 1 ) -1)+ "_"
+ (DT_STR, 20, 1252) (DT_DBDATE) GETDATE() +
REVERSE(SUBSTRING( REVERSE(@[User::filename]),1,
FINDSTRING( REVERSE(@[User::filename]) , ".", 1 ) ) )
You could also set a variable to just the filename using the variable's expression if you needed that.
REVERSE(SUBSTRING( REVERSE(@[User::filename]),1,
FINDSTRING( REVERSE(@[User::filename]) , "\\", 1 ) -1))
Hope this helps,
ArkWare
January 5, 2009 at 5:07 am
You say you use the string to add a folder. What happens if the folder is already there? Does it overwrite or delete & re-add?
January 5, 2009 at 5:13 am
Actually, now that I'm examining your code, I don't think that will work for me.
What I've got is this:
\\SourceServer\ShareFolder\DB1\DB1_db_200812011600.bak
\\SourceServer\ShareFolder\MyDatabase\MyDatabase_db_200812011700.bak
\\SourceServer\ShareFolder\TempDB\TempDB_db_200812011200.bak
And all files should be copied as:
\\DestServer\DiffShareFolderName\DB1_db_200812011600.bak
\\DestServer\DiffShareFolderName\MyDatabase_db_200812011700.bak
\\DestServer\DiffShareFolderName\TempDB_db_200812011200.bak
Notice the missing subfolders. Also, the ShareFolder name is different between the two UNC paths.
I need the full path to be able to copy the file to begin with and then I need just the file name so I can copy it straight to the destination path without involving the subfolders.
January 5, 2009 at 6:27 am
Brandie,
Does the "DiffShareFolderName" have any relation to the source "ShareFolder"?
The following expression code will pull just the file name from the complete filepath in your variable.
This can be placed in the Expressions of a variable.
REVERSE(SUBSTRING( REVERSE(@[User::filename]),1,
FINDSTRING( REVERSE(@[User::filename]) , "\\", 1 ) -1))
If the "DiffShareFolderName" will be used for all files, you may want to use the
Destination Connector, Expressions for the ConnectionString.
"\\\\DestServer\\DiffShareFolderName\\" + REVERSE(SUBSTRING( REVERSE(@[User::filename]),1,
FINDSTRING( REVERSE(@[User::filename]) , "\\", 1 ) -1))
In my other example the "Archive\\" folder was pre-existing.
January 5, 2009 at 6:40 am
ArkWare (1/5/2009)
Brandie,Does the "DiffShareFolderName" have any relation to the source "ShareFolder"?
Nope. As I said in my last post:
Brandie Tarvin (1/5/2009)
Also, the ShareFolder name is different between the two UNC paths.
Hence the reason I called it DiffShareFolderName.
I'm a little confused. I don't understand how your code only pulls the filename from the complete file path when each source file name is a completely different length and completely different name. I tried running the code in SSMS to see what the results would be, but SSMS doesn't like the "Findstring" function.
And when you say you put the code in the variable's expression, what expression?
Are you talking about the Value field of the variable? If so, is this a self-referencing value? IE, the code is in the User::filename variable Value field or is it in another variable's Value field that references "filename"?
January 5, 2009 at 7:36 am
Brandie,
I thought we were talking SSIS.
A ForEachLoop populating a variable with a complete UNC path.
like: "\\SourceServer\ShareFolder\DB1\DB1_db_200812011600.bak"
Try this....
Open a package in BI, (SSIS).
create a variable, 'SourcePath' as a string with value: "\\SourceServer\ShareFolder\DB1\DB1_db_200812011600.bak"
create another variable, 'FileName' as a string with a value: "test.txt"
in the properties window for this variable, set [EvaluateAsExpression] True.
hilite [Expression] and click on the [...] on the right.
this will open the Expression Builder.
in the Expression box paste this:
REVERSE(SUBSTRING( REVERSE(@[User::SourcePath]),1,
FINDSTRING( REVERSE(@[User::SourcePath]) , "\\", 1 ) -1))
press the 'Evaluate Expresson' button.
you sould see "DB1_db_200812011600.bak" in the Evaluated value: box.
Now, back to your ForEachLoop. Say it populates the variable 'SourcePath'.
For a file copy process you need a Destination Connection...
In the properties of your Destination Connector, set the Expressions for the
ConnectionString to:
"\\\\DestServer\\DiffShareFolderName\\" + REVERSE(SUBSTRING( REVERSE(@[User::SourcePath]),1,
FINDSTRING( REVERSE(@[User::SourcePath]) , "\\", 1 ) -1))
Hope this helps,
ArkWare
January 5, 2009 at 8:05 am
I have a piece of VBScript that will do the folder flattening for you ... but I guess that's not allowed, being dynamic.
Can't find any Windows command to do it - was initially hopeful about XCOPY, but no. In Linux, CP will do the trick, but that's as useful to you as a motorbike ashtray, right? 🙂
Phil
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
January 5, 2009 at 8:15 am
LOL :laugh:
a motorbike ashtray!!!
Thanks, Phil. I needed that this Monday morning!
March 3, 2009 at 11:31 am
I ended up populating a single variable with the full path and qualified name. Then used a script task within the loop to populate two more variables, source & destination. I had to do substring games, but it worked.
I wish there'd been an easier way, though.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply