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

• Jeff Moden

  SSC Guru

  Points: 1004589

  More actions

  February 22, 2008 at 8:59 pm

  #781334

  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

  February 24, 2008 at 5:11 pm

  #781454

  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

  February 25, 2008 at 8:59 am

  #781595

  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.