January 28, 2010 at 2:51 pm
I've been working on this all day and I am stumped. I'm trying to use the Foreach Loop task to pass a file name to a File System task so that it can move a file. Because the number of files in the Foreach Item Enumerator collection can vary I was hoping to use expressions to dynamically determine the file to grab and move. This (at least for me) appears to be easier said than done.
I've created a package scoped variable called @fileName that received a file name for the collection. That all works well. Confirmed by displaying a message box to show the name it was currently reading.
In the file system task I added an expression for the destination ("C:\\Documents and Settings\\jdgonzalez\\Desktop\\fileMove\\" + @[User::loopFileName]) and one for the source ("C:\\Documents and Settings\\jdgonzalez\\Desktop\\" + @[User::loopFileName])
Properties for file system task:
IsDestinationPathVariable: False
DestinationConnection: blank (but it gets filled in with C:\Documents and Settings\jdgonzalez\Desktop\fileMove\)
OverwriteDestination: False
Operation: Move file
IsSourcePathVariable: False
SourceConnection: blank (but it gets filled in with C:\Documents and Settings\jdgonzalez\Desktop\)
I know I could easily script it out with a script task but I try to avoid using the script task is there is something native that can accomplish the same thing.
When I run the package I get:
Error at Package: The connection "C:\Documents and Settings\jdgonzalez\Desktop\fileMove\" is not found. This error is thrown by Connections collection when the specific element is not found
Error at Package: The connection "C:\Documents and Settings\jdgonzalez\Desktop\" is not found. This error is thrown by Connections collection when the specific element is not found
Do I need to created a connection and set the connection string for the connection dynamically instead of using the source and destination properties in the file system task?
I'm at a loss on this one.
January 29, 2010 at 12:11 am
I've also had problems doing this sort of thing in the past and ended up scripting it ... I know others have too (I've seen the forum posts) - this component does not always work as easily as you would hope.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 29, 2010 at 12:30 am
January 29, 2010 at 7:56 am
@Phil... Glad to see I'm not the only person having problems with it. I wound up scripting it too. Do you know if this is some sort of bug?
January 29, 2010 at 11:05 am
J.D. Gonzalez (1/29/2010)
@Phil... Glad to see I'm not the only person having problems with it. I wound up scripting it too. Do you know if this is some sort of bug?
I think most issues are permissions related because in a company network the runtime needs elevated permissions to run such code. Check the thread below for some ideas.
http://qa.sqlservercentral.com/Forums/Topic661486-148-1.aspx
Kind regards,
Gift Peddie
January 29, 2010 at 12:28 pm
It does feel like it is permissions related, but ...
..if it were, why would the Script Task work and the File System Task have problems?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 29, 2010 at 1:07 pm
Did you try my suggestion?
Change the destination to
"\"C:\\Documents and Settings\\jdgonzalez\\Desktop\\fileMove\\" + @[User::loopFileName] + "\""
and the source to
"\"C:\\Documents and Settings\\jdgonzalez\\Desktop\\" + @[User::loopFileName] + "\""
January 29, 2010 at 1:14 pm
Phil Parkin (1/29/2010)
It does feel like it is permissions related, but .....if it were, why would the Script Task work and the File System Task have problems?
Script task will reference system.IO and COM in code File system tasks does not do that, I also found a blog posting which says to use rename.
http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html
Kind regards,
Gift Peddie
January 29, 2010 at 1:28 pm
wschampheleer (1/29/2010)
Did you try my suggestion?Change the destination to
"\"C:\\Documents and Settings\\jdgonzalez\\Desktop\\fileMove\\" + @[User::loopFileName] + "\""
and the source to
"\"C:\\Documents and Settings\\jdgonzalez\\Desktop\\" + @[User::loopFileName] + "\""
Yes. I tried it out yesterday with no luck.
I agree with Phil regarding the permissions. I was doing everything locally. I am also an admin on my box
January 29, 2010 at 1:32 pm
J.D. Gonzalez (1/29/2010)
wschampheleer (1/29/2010)
Did you try my suggestion?Change the destination to
"\"C:\\Documents and Settings\\jdgonzalez\\Desktop\\fileMove\\" + @[User::loopFileName] + "\""
and the source to
"\"C:\\Documents and Settings\\jdgonzalez\\Desktop\\" + @[User::loopFileName] + "\""
Yes. I tried it out yesterday with no luck.
I agree with Phil regarding the permissions. I was doing everything locally. I am also an admin on my box
The code you used is actually rename without implementation detail which is relevant when code access the operating system. So check the blog post for details, you may have missed something.
Kind regards,
Gift Peddie
January 29, 2010 at 2:48 pm
are you in a networked environment? try it on a file share with unc path \\servername\folder~1\ etc. I do not know if SSIS has issues with folders longer than 8 characters. Is this still an issue. when you have folder with a space in between go into cmd and to that specific folder and do dir /x it
/X This displays the short names generated for non-8dot3 file
names. The format is that of /N with the short name inserted
before the long name. If no short name is present, blanks are
displayed in its place.
I know in my case this has solved that problem. might work for you
January 29, 2010 at 2:49 pm
are you in a networked environment? try it on a file share with unc path \\servername\folder~1\ etc. I do not know if SSIS has issues with folders longer than 8 characters. Is this still an issue. when you have folder with a space in between go into cmd and to that specific folder and do dir /x it
/X This displays the short names generated for non-8dot3 file
names. The format is that of /N with the short name inserted
before the long name. If no short name is present, blanks are
displayed in its place.
For example Documents and Settings is DOCUME~1
I know in my case this has solved that problem. might work for you
January 29, 2010 at 10:38 pm
Heh... On most systems, especially those dedicated to ETL and the like, I avoid all of those problems with SSIS (and DTS)... I don't use them. 😛 I do it all from T-SQL. 😉
--Jeff Moden
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply