Tuesday, January 22, 2013 10:48 AM
i create package.In package the data flow task is execute success \ failure the information is send to the mail using a Send Mail Task . When i run this package from SSIS Solution it is working fine but when it is deployed to SQL server and try to run from SQL agent job it is not working. it give the following errorExecuted as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 4:09:59 PM Package execution on IS Server failed. Execution ID: 120092, Execution Status:4. To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report Started: 4:09:59 PM Finished: 4:10:27 PM Elapsed: 28.641 seconds. The package execution failed. The step failed.
How to execute the package?
Tuesday, January 22, 2013 11:50 AM
First make sure the SSIS package is created by the same version of SQL... for example, SQL 2008 will work on SQL 2008 but not 2005 ditto, 2012 will work on 2012 but not 2008... this is due to improvements introduced into the update packages that are frequently not backward compatible.
Second, check the version of the project's debug mode (Project | Properties | Debugging) If you deploy to a 32 bit, then set this default to False.
Tuesday, January 22, 2013 2:04 PM
Are you running the package through SQL agent in 32 bit mode? You can try to uncheck the button and run again.
Tuesday, January 22, 2013 10:32 PMModerator
Based on what you have posted I would suggest checking to insure that the NT Service\SQLSERVERAGENT has permissions to both the data source and destination.
When executing a package in SQL Server Data Tools, or Business Intelligence Development Studio, the security context for the connections are set to the user that is debugging the package, unless of course SQL security log in is provided. When the job is run in SQL Server Agent the security context of the agent is being used, in this case NT Service\SQLSERVERAGENT, this means that this service account must have access to the source to be able to authenticate.
Hope this helps.
David Dye http://sqlsafety.blogspot.com/
- Marked As Answer by Eileen ZhaoMicrosoft Contingent Staff, Moderator Monday, January 28, 2013 5:09 AM