SQL Server Job which downloads the files using Script Task fails after few runs on the Agent

Question

Post reply

SQL Server Job which downloads the files using Script Task fails after few runs on the Agent

kashyap4007

SSC Eights!

Points: 859
More actions
December 16, 2018 at 11:12 am

#382488

I have an SSIS Package which uses a script task to download some zip files daily from websites.
The Package is running fine when I run from Visual Studio.
The Package is also running fine when I deploy it and schedule the Job on SQL Server Agent.
But the real issue is when the Package is executing from the SQL Server agent, It is running fine only from few hours and then it is failing with the below Error Message.

To make the Job run successfully, All I Need to do is RDP to the Server where the Jobs are running, Open the IE Browser once and close it.
That's it. Again the Job is running fine for few more hours( I scheduled it to run every hour for testing) and stops again with the same above error message.
I am using a Proxy Account to run from SQL Server Agent.
To be more specific:
Below is the error I am getting Initially.

When I RDP to the server and then run the job, the error I am getting is:

But when I RDP to the server and open the IE once then the Job is running fine for next few runs.
Can someone help me with this please.
FYI... Below is the c# Code I am using in the Script Task to download the files.
   public void Main()
   {
    try
    {
      // TODO: Add your code here
      bool fireAgain = true;
      Dts.Events.FireInformation(0, "Download File", "Start downloading " + Dts.Variables["DownloadURLforOCDCore"].Value.ToString(), string.Empty, 0, ref fireAgain);
      // Create a webclient to download a file
      WebClient mySSISWebClient = new WebClient();
      // to save the file (and replace the existing file)
      mySSISWebClient.DownloadFile(Dts.Variables["DownloadURL"].Value.ToString(), Dts.Connections["PathToDownload"].ConnectionString);
      // Logging end of download
      Dts.Events.FireInformation(0, "Download File", "Finished downloading " + Dts.Connections["PathToDownload"].ConnectionString, string.Empty, 0, ref fireAgain);
      // Quit Script Task succesful
      Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (Exception Ex)
    {
      Dts.Events.FireError(0, "ERROR", Ex.Message, null, 0);
      Dts.TaskResult = (int)ScriptResults.Failure;
    }
   }

Viewing 11 posts - 1 through 10 (of 10 total)

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

kashyap4007 SSC Eights! Points: 859 More actions · Answer 1

kashyap4007

SSC Eights!

Points: 859

December 17, 2018 at 8:03 am

#2016511

Any help is really appreciated.

sgmunson SSC Guru Points: 110639 More actions · Answer 2

kashyap4007 - Sunday, December 16, 2018 11:12 AM
I have an SSIS Package which uses a script task to download some zip files daily from websites.
The Package is running fine when I run from Visual Studio.
The Package is also running fine when I deploy it and schedule the Job on SQL Server Agent.
But the real issue is when the Package is executing from the SQL Server agent, It is running fine only from few hours and then it is failing with the below Error Message.

To make the Job run successfully, All I Need to do is RDP to the Server where the Jobs are running, Open the IE Browser once and close it.
That's it. Again the Job is running fine for few more hours( I scheduled it to run every hour for testing) and stops again with the same above error message.
I am using a Proxy Account to run from SQL Server Agent.
To be more specific:
Below is the error I am getting Initially.

When I RDP to the server and then run the job, the error I am getting is:

But when I RDP to the server and open the IE once then the Job is running fine for next few runs.
Can someone help me with this please.
FYI... Below is the c# Code I am using in the Script Task to download the files.
   public void Main()
   {
    try
    {
      // TODO: Add your code here
      bool fireAgain = true;
      Dts.Events.FireInformation(0, "Download File", "Start downloading " + Dts.Variables["DownloadURLforOCDCore"].Value.ToString(), string.Empty, 0, ref fireAgain);
      // Create a webclient to download a file
      WebClient mySSISWebClient = new WebClient();
      // to save the file (and replace the existing file)
      mySSISWebClient.DownloadFile(Dts.Variables["DownloadURL"].Value.ToString(), Dts.Connections["PathToDownload"].ConnectionString);
      // Logging end of download
      Dts.Events.FireInformation(0, "Download File", "Finished downloading " + Dts.Connections["PathToDownload"].ConnectionString, string.Empty, 0, ref fireAgain);
      // Quit Script Task succesful
      Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (Exception Ex)
    {
      Dts.Events.FireError(0, "ERROR", Ex.Message, null, 0);
      Dts.TaskResult = (int)ScriptResults.Failure;
    }
   }

How about destroying your object variable (by setting it to Nothing) at the end of your code, so that any IE instance that was instantiated to perform the task gets closed?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Lowell SSC Guru Points: 323504 More actions · Answer 3

the using definition is your friend here.
using that will automatically destroy your object for you once they go out of scope:
change those two lines for your object to this:

// Create a webclient to download a file using (System.Net.WebClient mySSISWebClient = new System.Net.WebClient()) { // to save the file (and replace the existing file) mySSISWebClient.DownloadFile(Dts.Variables["DownloadURL"].Value.ToString(), Dts.Connections["PathToDownload"].ConnectionString); }

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kashyap4007 SSC Eights! Points: 859 More actions · Answer 4

Thank you sgmunson and Lowell.
I tried the above-using statement but still, it is failing with the same error.

kashyap4007 SSC Eights! Points: 859 More actions · Answer 5

I even tried to open a browser and close it every time but still, I don't see why this issue is happening.
Any help is greatly appreciated.

FYI...

     System.Diagnostics.Process.Start("www.google.com");
      // Create a webclient to download a file
      using (System.Net.WebClient mySSISWebClient = new System.Net.WebClient())
      {
       // to save the file (and replace the existing file)
       mySSISWebClient.Credentials = new System.Net.NetworkCredential(Dts.Variables["ServerUserName"].Value.ToString(), Dts.Variables["ServerPassword"].Value.ToString());
       mySSISWebClient.DownloadFile(Dts.Variables["ServerURL"].Value.ToString(), Dts.Connections["myProductFile"].ConnectionString);
      }
      System.Diagnostics.Process[] procs = System.Diagnostics.Process.GetProcessesByName("IEXPLORE");
      foreach (System.Diagnostics.Process proc in procs)
      {
       if (proc.MainWindowTitle.IndexOf("google") > -1)
        proc.Kill();
      }

sgmunson SSC Guru Points: 110639 More actions · Answer 6

Does that object have any kind of "close" method? You might need to do that to disconnect from the remote site. Not doing so risks controls on the remote site stopping you from having more than x number of simultaneous open connections.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

kashyap4007 SSC Eights! Points: 859 More actions · Answer 7

Few more details regarding my Error:In Catch Loop If I use:
DTS.Events.FireError(0,"ERROR",EX.Message,null,0)
I am getting the error saying unable to connect to remote server.

In Catch loop if I use:
DTS.Events.FireError(0,"ERROR",EX.InnerException.TOString(),null,0):
Then I am gettting the below error.

Any help is appreciated.
Thank you.

latkinson SSCommitted Points: 1590 More actions · Answer 8

If you haven't already, you might try and use Task Manager to see what processes are running. It would let you know, maybe, if your job is leaving unclosed IE sessions out there to clog things up. At any rate, explicitly setting your object to nothing in your code never hurts and sometimes helps.

I have no experience with this particular .Net class, so anything I say about it is suspect, but for what its worth, the Microsoft Docs page suggests that the better class to use is the System.Net.Http.HttpClient class.

By the way, what version of SSIS/SQL Server/.Net/IE are you using on the job server? Could this be an issue that a patch or minor update might resolve?

Luther

sgmunson SSC Guru Points: 110639 More actions · Answer 9

kashyap4007 - Wednesday, December 26, 2018 9:01 AM
Few more details regarding my Error:In Catch Loop If I use:
DTS.Events.FireError(0,"ERROR",EX.Message,null,0)
I am getting the error saying unable to connect to remote server.
In Catch loop if I use:
DTS.Events.FireError(0,"ERROR",EX.InnerException.TOString(),null,0):
Then I am gettting the below error.
Any help is appreciated.
Thank you.

There really isn't anything new here. If you don't close your connection each time you establish one, my gut says the remote site might well "cut you off" after what it considers too many concurrent sessions being open. This is basic protocol. You have to close your connection. Disconnect, so to speak... Failure to do so puts you at risk of whatever site security methods exist on the remote site, such as things like limits on the number of concurrent sessions from a given IP address. You've not yet shared that you have done anything in your code that explicitly closes the connection. Just destroying your objects clearly isn't enough.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

kashyap4007 SSC Eights! Points: 859 More actions · Answer 10

Firstly Thank you all for your valuable suggestions.
Below is how I was able to resolve this issue.
The Networking team added the server to our AD Group.
Due to security restrictions and also resource use, Inactive sessions on that server are logged off after 3 hours. We have a GPO where they can place the servers to prevent session logoff.

Thank you.