File System Task issue

Question

File System Task issue

jdgonzalez@jdandrachel.com

Hall of Fame

Points: 3161
More actions
January 28, 2010 at 2:51 pm

#137622

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.

Viewing 13 posts - 1 through 12 (of 12 total)

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

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

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.

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

wschampheleer SSCertifiable Points: 5504 More actions · Answer 2

Try putting quotes ( " ) around paths and filenames that contain spaces. Use a backslash ( \ ) to escape them in expressions e.g.

"\"" + @[Template::FolderExtractFiles] + "\""

Regards,

Willem
http://wschampheleer.wordpress.com[/url]

jdgonzalez@jdandrachel.com Hall of Fame Points: 3161 More actions · Answer 3

@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?

Gift Peddie SSC Guru Points: 73570 More actions · Answer 4

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://www.sqlservercentral.com/Forums/Topic661486-148-1.aspx

Kind regards,
Gift Peddie

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

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?

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

wschampheleer SSCertifiable Points: 5504 More actions · Answer 6

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] + "\""

Regards,

Willem
http://wschampheleer.wordpress.com[/url]

Gift Peddie SSC Guru Points: 73570 More actions · Answer 7

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

jdgonzalez@jdandrachel.com Hall of Fame Points: 3161 More actions · Answer 8

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

Gift Peddie SSC Guru Points: 73570 More actions · Answer 9

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

yosiasz SSCommitted Points: 1699 More actions · Answer 10

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

yosiasz SSCommitted Points: 1699 More actions · Answer 11

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 12

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

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)