0

I have an SSIS package which runs fine in debug mode from Visual Studio, however if It runs as a SQL Server job it hangs at step 2 which takes about twenty minutes in Visual Studio. The only thing I can think it could be is the SQL Server job itself.

The shutdown interval is set to 15 seconds in the SQL Server Agent. Could that cause the job to suspend?

Any help appreciated.

Christopher Jack
  • 97
  • 2
  • 12
  • 3
    You'll have to supply more information. What does step 2 do? What does it mean when you say it "hangs"? – SchmitzIT Dec 02 '20 at 09:13
  • Step two pulls out the customer sales and merges it with another table and adds a couple of columns. The step itself takes about twenty minutes. When I run the job checker SQL, it shows as 'Suspended' – Christopher Jack Dec 02 '20 at 09:15
  • *"The step itself takes about twenty minutes."* in Visual Studio? – Thom A Dec 02 '20 at 09:20
  • The error itself is Failed to execute IS server package because of error 0x80131904. Server: I, Package path: \x, Environment reference Id: 15. – Christopher Jack Dec 02 '20 at 09:20
  • 1
    According to a google, error 0x80131904 is a timeout error. – Thom A Dec 02 '20 at 09:23
  • When I google it, its coming back as credentials? Would the shutdown interval set at 15 seconds cuase the job to suspend? – Christopher Jack Dec 02 '20 at 09:26
  • *"When I google it, its coming back as credentials?"* What results? All of these points to timeout: [1](https://stackoverflow.com/q/24782662/2029983). [2](https://stackoverflow.com/q/53314640/2029983). [3](https://stackoverflow.com/q/35682439/2029983), [4](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/46c36455-01c2-422b-bf7f-47f950d6ec51/sql-server-2012-job-intermittently-unable-to-kickoff-or-start-ssis-2012-package-the-operation?forum=sqlintegrationservices). – Thom A Dec 02 '20 at 09:30
  • Thanks Larnu, looking at them it could be a number of issues, ill try and recreate the job and see if that helps. – Christopher Jack Dec 02 '20 at 09:34
  • Which user did you provide as execution user in your job? Same credentials as in VS? – Tyron78 Dec 02 '20 at 10:02
  • /Administrator on the job and server details on the SSIS – Christopher Jack Dec 02 '20 at 10:07
  • 1
    This might be nothing, but I googled the error as well, and see a reference to firewalls. (https://community.spiceworks.com/topic/2094881-sql-error-0x80131904) and/or permissions. I'm thinking that since things seem to work from VS, but not the server, that might be a factor. Have you verified that the serer has permission to access your destination? – SchmitzIT Dec 02 '20 at 12:12
  • 1
    Also see https://stackoverflow.com/questions/24782662/sql-server-agent-ssis-package-error-0x80131904-timeout-expired – SchmitzIT Dec 02 '20 at 12:12
  • 1
    Oh, and a minor request. When you supply additional information, please add that to the question. That way whenever someone finds this question, they don't have to weed through comments to get the complete picture. It makes the process easier for anyone trying to help (or trying to find help for their issue). – SchmitzIT Dec 02 '20 at 12:17

1 Answers1

0

Thanks to everyone who helped. I created a new Job on the SQL Server and the package seems to be running fine.

Christopher Jack
  • 97
  • 2
  • 12