Post reply

SSIS - FTP Task and SQL Agent Job

  • August 7, 2013 at 3:56 am

    #297379

    Hi everyone

    I have created a SSIS Package that gets a list of files on a FTP site, receives and then deletes each file using a ForEach enumerator.

    It is working 100% when run using BIDS, but when I schedule this as a job, it fails:

    Description: The object in the variable "User::FileList" does not contain an enumerator

    But when I run the BIDS version directly after this error, it pulls down the files?

    What would cause the Job not to get the file list? Its a plain Script Task using a FTP Connection.

    Any ideas?

  • August 7, 2013 at 3:59 am

    #1639298

    Why not use the FTP task?

    What do you do with that variable?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • August 7, 2013 at 4:10 am

    #1639303

    Have you checked that the account the SQL Agent service runs under has the neccessary permissions to access the FTP folder? It sounds like it is not populating the variable.

    MCITP SQL 2005, MCSA SQL 2012

  • August 7, 2013 at 4:22 am

    #1639307

    @Koen: I can't use a normal FTP task as there are multiple files that I need to download from the folder and then delete each one as I download it.

    @RTaylor2208: The FTP connection runs with its own credentials though? I have also created a Proxy Account with my server administrator as the credentials, but its still failing?

  • August 7, 2013 at 4:44 am

    #1639314

    You have mentioned that it runs fine in BIDS, where are you running BIDS on the server that the SQL Agent job is running from or from your workstation?

    MCITP SQL 2005, MCSA SQL 2012

  • August 7, 2013 at 4:52 am

    #1639317

    I am running both on the Server

  • August 7, 2013 at 5:00 am

    #1639318

    It will depend on how your server is setup, but can you log on to the server as the account your SQL Agent runs under and run the package interactivly in BIDS?

    This will help emulate how the package is running when ran via the agent.

    MCITP SQL 2005, MCSA SQL 2012

  • August 7, 2013 at 5:18 am

    #1639324

    Hi, it doesn't allow me to log into the server with the Account the Agent runs off.

    I have another FTP application that works a bit differently, will try and implement that solution and see how it behaves.

  • August 13, 2014 at 2:30 pm

    #1737823

    Hi..if you don't mind please can you share your code with me that connects to an ftp site/moves/deletes/copies files back to your local environment using the SQL server Agent ? Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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