Schedule SSIS job in SQL Agent fails

Question

Schedule SSIS job in SQL Agent fails

Dana Bibeault

Hall of Fame

Points: 3670
More actions
March 6, 2007 at 8:10 am

#153465

Hi, I am trying to schedule a SSIS package in SQL agent 2005. I can run the package manually using Business Intelligents Studio but when I try to schedule it the following error orrurs.
I set it up using windows authentication. I have administrator rights.
SQL Server Scheduled Job 'TestImport' (0x5EB86F3C24723D41B5C5779C026CCFCC) - Status: Failed - Invoked on: 2007-03-06 09:55:13 - Message: The job failed. The Job was invoked by User QDSSVR1\bibeaultd. The last step to run was step 1 (testimport1).

Is this another security issue? Should I set up using system administrator?

Help!
Newbie

Viewing 15 posts - 1 through 15 (of 24 total)

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

xanthos SSCrazy Points: 2510 More actions · Answer 1

The account the sql server agent is running under should have all permissions necessary to run the IS package. Check all your connections and check whether the agent-account has permission.

Dana Bibeault Hall of Fame Points: 3670 More actions · Answer 2

Thanks, How do you check to see if SQL agent can run a SSIS package? One more question, does the standard install install SSIS on the server? How can I tell if all the peices are in place?

thanks,

wyliec2 SSC-Addicted Points: 438 More actions · Answer 3

I am having a similar problem - SSIS package executes fine in BIDS but fails when scheduled as SQL Server Agent job. Different error...

Windows Server 2003/SQL Server 2005 64-bit.

Package Protection - DontSaveSensitive (prior to this error I was getting a password encryption error so changed to this protection level with new error result)

Error Message Text:

Executed as user: SVHPSQL\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:14:31 AM Error: 2007-03-06 10:14:32.33 Code: 0x00000000 Source: D_Member Month Description: This task does not support native Win64 environment. Please run the package in 32-bit WOW environment instead. End Error Error: 2007-03-06 10:14:32.35 Code: 0xC0024107 Source: D_Member Month Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:14:31 AM Finished: 10:14:32 AM Elapsed: 0.359 seconds. The package execution failed. The step failed.

SQL Adventurar SSC Eights! Points: 973 More actions · Answer 4

Hi All

To check that SSIS is running you can have a look under your services and look for SSIS and if it is running I would asume that all was installed.

The account that starts the SQL server agent does not neccesarily need to be an admin on the machine but it would help to avoid unnessasary complications. You could also add the user SQL agent starts up with to the "SQLServer2005DTSUser" user group and check if the security issue is resolved.

Kindest regards

Jacques

Stefan Ghose SSC Rookie Points: 41 More actions · Answer 5

Hi All,

I used this example to set up my Proxy Account and it has been working fine...

http://www.codeproject.com/useritems/Schedule__Run__SSIS__DTS.asp

Kind Regards

Stefan Ghose

laker_42 SSCarpal Tunnel Points: 4785 More actions · Answer 6

wyliec2,

You need to call out the 32 bit dtexec as opposed to the 64 bit. It is located here if you installed to the default location:

"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe"

Are you doing anything with ODBC in your SSIS package? If so, odbc is not supported on the 64 bit platform. That is the biggest issue we ran into with 64 bit.

Hopefully this helps,

John

Budiman-402052 SSC Journeyman Points: 99 More actions · Answer 7

Hi,

I also faced similar problem before. I added a credential for running Sql Agent in Database server. since that time, my Sql agent has been working well. I hope it will help you

regards,

Budiman

MBE SSC Enthusiast Points: 131 More actions · Answer 8

I'm new to this and I'm having the same problem. Only some jobs are failing but act like success. How do I add a credential? We too are running on 64 bit. Thanks!

S. Kusen SSChampion Points: 10936 More actions · Answer 9

All,

The standard security on an SSIS package is "EncryptSensistiveWithUserKey" which only allows the user that created it to be able to run in as long as there is sensitive information (passwords) stored within the package.

A workaround that I am using is that I changed the package security to "EncryptAllWithPassword" which protects the entire package with a password. When you set the package as a job step, go into the properties of that job step, click on the "Command Line" tab and you should be prompted for the password. After you enter the password, the /DECRYPT switch should show up in the command line and the job should then be able to run under a different user.

Users won't be able to see the password because they will need to enter it before viewing the command line for the job step.

Let me know if that helps.

Steve

SQLBill SSC Guru Points: 51440 More actions · Answer 10

Stephen is correct about the certificate (password) issue. Except that Microsoft requires passwords AND connection strings to be encrypted. The problem arises when:

1. PersonA creates a package (it is now using PersonA's credentials) and it is being run by someone else (SA, PersonB, etc). The credentials don't match.

2. PersonA creates the package on ServerA and moves it to ServerB. The credentials no longer match.

Two solutions:

1. change the package to EncryptAllWithPassword. But then you have to supply the password in the run string.

2. Deploy the package to SQL Server.

In BIDS, open the solution that contains the project. RIght Click on the project and click PROPERTIES. In the Property Pages dialog box, click Deployment Utility. Set CreateDeploymentUtililty to TRUE. Click OK. In Solution Explorer, right click the project, then click BUILD.

This will create a Deployment folder. Look for .SSISDeploymentManifest. Double click on that. Go through the steps. The first step will ask where it is to be saved. Choose SQL Server option. Then choose the option (in the next window) to Rely on Server Storage for Encryption.

For more details, check out Deployment Utility in the SSIS section of BOL.

-SQLBill

S. Kusen SSChampion Points: 10936 More actions · Answer 11

Good information. Thanks for that. I am still ironing out some of the ways to use SSIS in SQL. I was able to get my packages up and running within the file system using the EncryptAllWithPassword, so that's why I had suggested that. The deployment utility looks like a good alternative to my method.

Dana Bibeault Hall of Fame Points: 3670 More actions · Answer 12

The change Package Security option helped. Works fine now.

Thank you for your time.

Campden BRI Hall of Fame Points: 3300 More actions · Answer 13

I've got a similar problem. I had several jobs scheduled to copy databases from a live instance to a development instance both running on the same server. On installing SP2, the jobs now fail with the error below. The packages referenced by the jobs are executed successfully using Visual Studio.

Executed as user: Domain\SQLService. Microsoft (R) SQL Server Execute

Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft

2007-04-10 18:57:06.18 Code: 0x00000000 Source:

SERVER11_LIVE_SERVER11_DEV_Transfer Objects Task Description: Source

SQL Server instance was not specified. End Error Error: 2007-04-10

18:57:06.18 Code: 0xC0024107 Source:

SERVER11_LIVE_SERVER11_DEV_Transfer Objects Task Description: There

were errors during task validation. End Error DTExec: The package

execution returned DTSER_FAILURE (1). Started: 18:57:05 Finished:

18:57:06 Elapsed: 0.297 seconds. The package execution failed. The step

failed.

Any ideas on resolving the problem would be appreciated.

Thanks

A. Ade-Hall

g-457622 SSC Rookie Points: 36 More actions · Answer 14

Hi 2 all,

I have almost the same problems , I created an SSIS package including a simple ftp task where downloads sme jpg files localy. I setup also an XML configuration file with the connection etc. The package in the BI runs perfect when I install it in the dev server and excecute it is fine as well .

Now I am trying to put it as a job, I create from the management studio a new login , I set up new credentials realted to the new login , also I checck the sql server agent runs under this account. Then I created a proxy related with the credentials.

Then I set a new job and I am trying to run it it fails show me the following error:

"Executed as user: SQL\sql_service. The package ecexution failed. The step failed"

Any clue of what may have been wrong ?

Thank you in advanced for the help