asked on

Can run SSIS package alone but when ran through jobs it fails

I have an SQL server 2005 and SQL Express as well as a SQL 2012 server. I built a package that will export data from an SQL Express (2005) database into a SQL 2005 server. This package works great when I run the package alone but when I try to run it through Jobs it fails with the following error.

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{FDC3723D-1588-4BA3-92D4-42C430735D7D}
and APPID
{83B33982-693D-4824-B42E-7196AE61BB05}
to the user NT SERVICE\SQLSERVERAGENT SID (S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430) from address LocalHost (Using LRPC) running in the application container Unavailable SID (Unavailable). This security permission can be modified using the Component Services administrative tool.

I have tried fixing it through Componenet Services however I dont know where to look to fix this issue. I know this is a permissions issue but where to fix it?

ASKER CERTIFIED SOLUTION

Deepak Chauhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tim

ASKER

I have tried that but does not work. I tried adding the SQLSERVERAGENT as a user to that section but it says it does not exist. I added the Domain Administrator account and assigned all permissions but still it will not work. When I look at the Job it shows to run as Domain Administrator with Windows Authentication but still it fails with the same error. Is there a problem with the SSIS package? What am I missing?

Deepak Chauhan

Copy the service name account name
'NT SERVICE\SQLSERVERAGENT' and paste in the find box when you add this user.

This account will be available on this system.

No issue with ssis package this is because SQL agent service is running through NT SERVICE\SQLSERVERAGENT account.

Or you can add this account in Windos local administrator group for test. But i would not recommend this.

Vitor Montalvão

Did you see Microsoft's KB about the error?

Tim

ASKER

I have tried all the above and still the package will not run. It keeps telling me I don't have permission. After adding Domain Administrator user and SQLSERVERAGENT user to Launch and Activation Permission I still get an error however it is no longer posted in the events it shows in Job History as permission is denied. What user is causing this issue? Below is the message I see.

07/02/2015 10:02:04,AIS_DATA_Import,Error,1,SQLSERVER,AIS_DATA_Import,AIS_Data_Extract,,Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 11.0.5058.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved.
Started: 10:02:04 AM Could not load package "\MSDB\AIS_Data" because of error 0xC00160AE.
Description: Connecting to the Integration Services service on the computer "SQLSERVER" failed with the following error: "Access is denied." By default<c/> only administrators have access to the Integration Services service. On Windows Vista and later<c/> the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service. Source: Started: 10:02:04 AM Finished: 10:02:04 AM Elapsed: 0.015 seconds. The package could not be loaded. The step failed.,00:00:00,0,0,,,,0

If I knew they right user to add to the service I would but it is not telling me which user cannot run the package.

Deepak Chauhan

Ok you can try to add the domain user and NT Service\SQLSERVERAGENT in MSDB database user and assign the role ssis_admin

Tim

ASKER

OK, somehow some way I fixed this issue. I rebuilt the package using SQL Database credentials instead of Windows Authentication and it worked. I guess this issue is fixed but still am confused as to why it worked but it did so I am not going to waste anymore time on this issue. Thank you for all your help.