SQL Job Fails running SSIS package?

[bluecjh]

sql 2005

I'm trying to schedule the import of some data from
an excel file to a sql table.

using dtexecui my package runs fine,
but when i run the package as a job it fails with the
unhelpful error -

The package execution failed. The step failed

??

this is my first attempt with ssis (my package runs fine in ssis too)

any ideas... many thanks

BlueCJH

 

[mrdenny]

Connect to the SSIS service in the Management GUI. Right click on the package and click view log. It will give you some more useful info.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[bluecjh]

ok i opened up SQL Server Management Studio
and chose integration services.

expanded MSDN folder and selected my package
but when i right click there is no 'view log' option

am i in the wrong place?

thanks.

BlueCJH

 

[mrdenny]

Apparently I sent you to the wrong place.

I'm drawing a total blank on where the log viewer is for the package.

Give me a few, I'll remember it.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

I think first you need to enable package logging. Look up "logs [Integration Services], enabling" in BOL. It allows you to put the log anywhere.

There's tons of information there, so I'm not going to cut-n-paste it here (it'd make the post too big).

However, if you check the job logs under SQL Server Agent -> Error Logs it may help you out.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

AHA!

Connect to Integration Services, like Denny said, and right click the Server name. View Logs is the third thing down.

However, mine is empty and asking me to load a log. I guess this if for Text Logs, etc. I configured a package log to log to SQL Server but don't see it there. When I clicked HELP, this is the page that came up:

The Log File Viewer can display log files from a variety of components. The logs available depend on how the Log File Viewer is opened. When the Log File Viewer is open, use the Select logs pane to select the logs you wish to display.

To view logs related to general SQL Server activity
In Object Explorer, expand Management, expand SQL Server Logs, and then double-click Current <date/time>, displays the SQL Server, SQL Agent, and Windows NT logs.

To view logs related to Jobs
In Object Explorer, expand SQL Server Agent, right-click Jobs, and then click View History, displays the Job History, and the SQL Agent logs.

To view logs related to Maintenance Plans
In Object Explorer, expand Management, right-click Maintenance Plans, and then click View History, displays the Maintenance Plans, Job History, and the SQL Agent logs.

Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[bluecjh]

will investigate this..

BlueCJH

 

[bluecjh]

OK I'm getting nowhere.

This package runs fine as a package with loads of log information, but it's irrelevant as it has always run well as a package, but the job always fails. I have read about lowering the encryption level, passwording etc..

even if i tick the option on deployment of the package to
'rely on server storage for encryption' it still fails?

I can't find a way to increase the level of information the job history shows other than to get back "package execution failed"
any ideas, thanks?

BlueCJH

 

[bluecjh]

i have logged the package to a text file. flagged all logging
options but the output is not helpful? there are no
errors or any messages relating to package failure.?

BlueCJH

 

[bluecjh]

Could this be a permissions issue, the package extracts
data from a spreadsheet which resides on the network
not on the Server? If SQL doesn't have the rights...
The package runs ok by me, but the job fails.

The log files don't suggest this but i can't think of anything else.?



BlueCJH

 

[mrdenny]

Do you have a configuration file in the package? If you specificied a config file when building the package and didn't remove it prior to deploying it to the server it will expect the server to have the file in the same path.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[bluecjh]

No configuration file in the package.

BlueCJH

 

[Catadmin]

Okay, try this.

What sensitivity is the package saved with? Sensitive with User Key, Don't Save Sensitive, etc. Look in the properties of the overall package.

It may be that you have to save it as Don't Save Sensitive for the job to properly run it.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[bluecjh]

Will investigate... thnks

BlueCJH

 

[mrdenny]

What happens when you log into the server with the account that will be running the package and run it from the command line (dtexec) with the same switches that the SQL Agent job will be using?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[PeetS]

This is normally to do with permissions on the folders where the packages are deployed to. The following steps has resolved the same issue for me.

1. Create a new windows account on the server. This account will execute the packages

2. Open The SQL Management Studio. Database Engine

3. Expand Security and expand logins.

4. Right click Logins and select new Login

5. Type in the new windows login that you have created in the “Login Name” box.

6. Click on “User Mappings” on the left of the page.

7. Tick the MSDB database.

8. Select SQLAgenOperatorRole, SQLAgentReaderRole, SQLAgentUserRole from the bottom “Database Role Membership” box

9. Click OK.

10. Now Click on Security ? Credentials and select New Credential

11. Types in you’re Windows User Login that you have created with password and name.

12. Click OK

13. Expand SQL server Agent.

14. Expand Proxies.

15. Right Click on SSIS Package Execution and select new proxy.

16. Type in Proxy name.

17. Select the credential you just created.

18. Tick the SQL Server Integration Services Box.

19. Select “Principals” on the left hand side of the page.

20. Click Add.

21. Select MSDB Role and tick SQLAgenOperatorRole, SQLAgentReaderRole, SQLAgentUserRole.

22. Click OK.

23. Now got to the directory where you are deploying you’re packages.

24. Right Click and select Properties on the folder.

25. Click on Security Tab.

26. Add you’re new user account here and give it Full Control.

27. Now, when you create a job step. Select the Proxy name where it says. “Run as”.

28. That will do the trick.

Hope this helps.

 

[cehall2]

I am also experiencing the same issue with a SQL server agent job failing when importing data from excel into a db.

The weird thing is that there is another job loading data from excel into a different db which runs successfully. Both jobs have been setup in the same way both with protection level "EncryptSensitiveWithUserKey".

The job has been recreated and it still doesn't work.

The last posting will not work in my situation as the package is stored internally in the db and not physically on the disk. When logging onto the server using the service account the package runs successfully when run manually.

There are 6 other packages which run successfully as scheduled jobs using the same credentials, so it doesn't appear to be a permissions based problem.

Any other ideas?

Thanks,
Emma

 

[cehall2]

Since submitting my earlier posting the issue I had has now been resolved.

Turning on the server logging was a definite help as it identified that the issue was due to the variable in the package using a mapped drive path rather than UNC path.

I found MS KB article nbr 918760 to be very helpful in resolving the issue:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;918760