locked
How do I get SQL Agent to perform an FTP connection through a SSIS package? RRS feed

  • Question

  • I have created a number of SSIS packages that I am running through SQL Agent in various jobs.  I have set up a Credential / Proxy for the job steps and for the most part everything runs no problem.  The issue I have is with the step where I make a connection to a FTP server and download files.  In BIDS the step works fine but in SQL Agent the step times out.  I presume that it has to do with the account that it is being run under but that is what I thought the Credential / Proxy was supposed to handle (as it does for all the other steps).  I am running on SQL Server 2008 R2.  I have created a test job that has only one step which is to connect to the FTP server and download some text files.  I have tried utilizing package configuration files and have tried various ProtectionLevels to no avail.

    Anyone have any thoughts or direction for me to take?  Will continue to research and will post if I resolve this.

    Thanks, Andrew

    Wednesday, November 10, 2010 8:50 PM

Answers

  • Alrighty - the solution is in sight.  Apparently there was a firewall issue.  Even though the package with the ftp connection runs through BIDS and through the "Execute Package Utility" with the current firewall settings, SQL Agent still fails (filezilla also works with these settings).  BUT in changing the firewall settings I can get everything to work now.  My last task will be to find the most restictive I can make the firewall while still allowing SQL Agent to operate.

    Thanks Reza for trying :)

    Andrew

    • Marked as answer by Eric Wisdahl Friday, November 12, 2010 2:31 AM
    Thursday, November 11, 2010 8:04 PM

All replies

  • Are the development machine and production machine which you run sql server job Same?

    http://www.rad.pasfu.com
    Wednesday, November 10, 2010 9:04 PM
  • I have two different environments: one dev and one prod.  In the dev environment the FTP step actual works fine.  Only in the prod environment does it not work.  I had originally created the packages in dev and ported them over to prod. ... but in order to test (to try to resolve the issue) I have created a new package right on the production SQL Server where I am using SQL Agent.
    Wednesday, November 10, 2010 9:18 PM
  • maybe FTP port ( 21 by default ) closed in production environment by administrators for security reasons. did you checked it yet?

    http://www.rad.pasfu.com
    Wednesday, November 10, 2010 9:28 PM
  • Port 21 is the one used but it is open.  I previously had downloaded filezilla to test the connection with and that runs fine.  I also can run the package through BIDS on the production server fine just not through SQL Agent.  One other FYI, the user I have for the Credential on dev and prod are different but both are part of their repsective machine administrator groups.

    Wednesday, November 10, 2010 9:34 PM
  • Can you try to create a batch file with the same code and RunAs the same credentials instead on the server and check if it downloads?


    Arthur My Blog
    By: TwitterButtons.com
    Wednesday, November 10, 2010 9:40 PM
  • I have not worked with FTP in batch before but I can try that.  Preference though would be to have the SSIS package run though as it does in the development environments SQL Agent and as it does in BIDS on the production server.

    Wednesday, November 10, 2010 9:45 PM
  • could you try with same credential that you used in development machine on sql server agent  and let us know the result?

    http://www.rad.pasfu.com
    Wednesday, November 10, 2010 9:58 PM
  • Sadly, I cannot.  The two client environments are on different domains.
    Wednesday, November 10, 2010 10:02 PM
  • how did you deployed package? what is Protection Level of package?

    http://www.rad.pasfu.com
    Wednesday, November 10, 2010 10:03 PM
  • On the dev environment the ProtectionLevel is set to EncryptSensitiveWithUserKey and that is what I tried first on the prod server.  Subsequently I also tried utilizing a package config file (with ProtectionLevel then of DontSaveSensitive) and have also tried working with the ServerStorage.

    For deployment I copied the relevant dtsx files to a solution on the prod server.  I verified that the packages all had tested connections in the Connection Manager.  I have run all the packages in the BIDS on the prod server and they all worked.  I have created a test package from scratch on the prod server to test the ftp connection.  This test package runs from BIDS fine.  I have set the SQL Agent file to access through the file system (no luck) and have saved the package to the SSIS Packages and had the job step access from there (no luck either).

    Wednesday, November 10, 2010 10:11 PM
  • Alrighty - the solution is in sight.  Apparently there was a firewall issue.  Even though the package with the ftp connection runs through BIDS and through the "Execute Package Utility" with the current firewall settings, SQL Agent still fails (filezilla also works with these settings).  BUT in changing the firewall settings I can get everything to work now.  My last task will be to find the most restictive I can make the firewall while still allowing SQL Agent to operate.

    Thanks Reza for trying :)

    Andrew

    • Marked as answer by Eric Wisdahl Friday, November 12, 2010 2:31 AM
    Thursday, November 11, 2010 8:04 PM
  • I need to know what program you had to open please.
    Sunday, October 5, 2014 7:38 PM