Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SSIS PACKAGE NOT FINDING CORRECT DIRECTORY

Answered SSIS PACKAGE NOT FINDING CORRECT DIRECTORY

  • Tuesday, February 26, 2013 3:00 PM
     
     
    Sign In to Vote
    1

    I have an SSIS package that takes files and moves them over to an FTP site. When I am running it inside Microsoft Visual Studio, it finds the directory that I have put in the package and moves the files to the FTP. 

    But when I set up an SQL Server Agent job to automate the package to run daily, it fails because it looks for the files in the C:\Windows\System32 directory instead of the D:\MyFiles\ directory where they are located.    And because the files are not there, the package fails to run.

    When running the scheduled daily SQL Server Agent job, the package should look at the location  D:\MyFiles\  directory that I built into the package just like it does when I run it in Visual Studio.  

    Can anyone help?  I have worked on this two days trying to figure it out.  Any help would be greatly appreciated.

    Wade

     

All Replies

  • Tuesday, February 26, 2013 3:10 PM
     
     
    Sign In to Vote
    0
    There are multiple ways you can achieve this, but easiest option would be to save the path in the variable and pass that variable while executing it. Hope this helps.

    Thanks, Sohail ~Please mark answered if your question is completely answered; vote it as helpful to help others find a solution quicker. This posting is provided AS IS with no warranties.

     
  • Tuesday, February 26, 2013 3:13 PM
     
     
    Sign In to Vote
    0

    I created a path variable already.  What do you mean by passing that variable while executing?  How do I do that using an SQL Server Agent job?

    Thanks,

    Wade

     
  • Tuesday, February 26, 2013 3:23 PM
     
     
    Sign In to Vote
    0
    If you are saving them in configuration file, you can call that configuration file while executing, so that at run time it will take those parameters.

    Thanks, Sohail ~Please mark answered if your question is completely answered; vote it as helpful to help others find a solution quicker. This posting is provided AS IS with no warranties.

     
  • Tuesday, February 26, 2013 5:36 PM
     
     
    Sign In to Vote
    0

    Thanks Sohail for your help.  I looked at building a configuration file. Will I use the Path option?  Do I use the type of Environment Variable, XML Configuration File or some other type.  If I create this configuration file, will it expect my package to be in that path or my files or both?

    Thanks,

    Wade

    • Edited by Wade T Tuesday, February 26, 2013 5:36 PM
    •  
     
  • Tuesday, February 26, 2013 6:21 PM
     
     
    Sign In to Vote
    0

    Wade,

    if you have defined a package variable then you can simply set the value for the variable from the SQL Agent itself. You do not need a package configuration file. The article in the link below will show how to use "Set Values" Tab in the SQL Agent Properties window to assign package variable values.

    Setting Package Variables in a SQL Agent Job

    To answer your question about configuration files. When you deploy the SSIS package by using a manifest file in case of a file system deployment the configuration file will be copied along with the package to the destination folder. The Variable value will be assigned from the Config File. If you choose to assign it from the SQL Agent Job itself then you should not use the configuration as yuo would simply be overwriting the same value.

    Regards, Dinesh


    • Edited by Dinesh Menon Tuesday, February 26, 2013 6:29 PM
    •  
     
  • Tuesday, February 26, 2013 8:20 PM
     
     
    Sign In to Vote
    0

    Dinesh,

    Thanks for your help.  I am fairly new to SSIS.  I am doing a rename of files in my SSIS package and it is not looking for the files in D:\MyFiles\ like I put in creating the package.  It fails and says it can not find the files in C:\WIndows\System32.  How do I put the location of my files D:\MyFiles\ as a path / directory in the "Set Values" fields? 

    Thanks,
    Wade


    • Edited by Wade T Tuesday, February 26, 2013 8:20 PM
    •  
     
  • Wednesday, February 27, 2013 3:21 PM
     
     Answered Has Code
    Sign In to Vote
    1

    Wade,

    From your earlier post with Sohail, you did mention that you had added a variable in the package of type string. Now i am assuming that you have set the value for that variable as D:/MyFiles in your package. Set the Variable value through Expressions for the FTP Task/Connection.

    If you want to set the values from the SQL Agent Job then all you have to do is set value for the variable which in turn will update the FTP Connection Property.

    that you can do by navigating to the SQL Agent Job - Step > Properties > Set Values Tab

    In the Tab add the Variable value to the left in the format

    \Packages.Variables[User::myVariable].Properties[Value]

    and set the value field as D:/Files or whatever folder you want.

    Stack Overflow question on Setting FTP Name dynamically

    Regards, Dinesh