Help - Production - The variable "User::SourcePath" doesn't contain file path(s).

Question

Help - Production - The variable "User::SourcePath" doesn't contain file path(s).

chockenhull

SSC Veteran

Points: 266
More actions
February 22, 2008 at 2:30 pm

#120031

:w00t:
I have a Foreach loop to process all files of *.txt in a directory. Within the loop is an FTP task followed by a File System Task.
The File System Task has the following definition;
IsDestinationPathVariable - false
DestinationConnection - Export
Operation - Move file
IsSourcePathVariable - true
Source Variable - User::MoveFile (this is used in the Foreach as the Variable Mappings value)
The Export (DestinationConnection) definiton;
Expression for ConnectionString - @[User::SourcePath] + "\\Backup".
SourcePath Variable definition;
Scope - MoveToBackUp (the ControlFlow name - there for package scope)
Data Type - String
Value - C:\TranHistory\Export
The source path does exist and there are files there.
It all runs fine in the IDE. However when I deploy on the Server (file based system), set the SQL Agent job, and run I get the above message.
Why?? How do I resolve??

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Not 100% sure, but I think it's just a problem with permissions. What login is the agent using and does it have the privs to see the same directories as you do when you are logged in?

--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)

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

Check to see that you are grabbing the full path on the for each group and not the file name only.

chockenhull SSC Veteran Points: 266 More actions · Answer 3

To Jeff:

The Agent is running under NT AUTHORITY\NetworkService. Other steps of the this package will run correctly, if I disable the FTP step.

To JD:

I've already re-written the FTP portion in script. Over the weekend, I found so many issues with the FTP control that it seemed quicker to do it in code due to the whole password issue & other things. I've kept the original package so maybe I can try this. To be honest, I've run into so many problems with SSIS that I tired of dealing with it.

I do have Wrox SSIS book, however I must just be slow because it really hasn't helped me get this job done in a timely manner.

Thank you both for your help.