0

This is our first SSIS package created on xx.xx.xx.77 for pull data from xx.xx.xx.56 server and load on share path (server ip xx.xx.xx.130) as an Excel file. That path is accessible from both servers. SQL Server Agent is configured with NT Service\SQLAgent$instance in xx.xx.xx.77 server. So we got Error1.

Error1:

Msg: 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\SQLAgent$instance SID (S-1-5-80-3789775531-3388834719-430539279-2094781381-3838682281) 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.

Log Type: Windows NT Log Source: System Source: Microsoft-Windows-DistributedCOM Categroy: (0) Event: 10016 User: NT SERVICE\SQLAgent$instance Computer:

After Google search we go with Proxy user. Now we facing issue (Error2) in scheduling that package on xx.xx.xx.77 server using proxy user.

Error2:

The job failed. The Job was invoked by User. The last step to run was step 1 ()

FYI: Although same Proxy user has been created on xx.xx.xx.55 server, same error persist while run job on 77 server.

Reference Links:

  1. Give access to virtual account. But we cant do this due to our privileges is not enough.
  2. Proxy Creation

We refer more SO Q&A and Google results for SSIS package creation. No one helps. Because our scenario includes two servers. So we need helping hand.

Edit 1:

We did this after Error1.

"Launch & Activation Permission" -> click Edit -> Add in the account NT Service\SQLAgent$instance-> Name Not Found error. Ref: below image. Same error for, add "Service Account" in Object Type on below dialogue box.

Name Not Found

Then we change location from Domain.com to Server Name in Location as follows:

Location

Now, NT Service\SQLAgent$instance name is found. Then we check "Local Launch & Local Activation". Ref:

Local Permission

After restart the Service, we got same error. Thats' we go with Proxy User.

Reference link for Deploy the SSIS package.

EDIT 2

We move the SSIS project to another xx.xx.xx.231 server, where SQL Service Agent is configured with Domain user like backupuser@domain.com. Now the job is run successfully on scheduled time.

FYI: Another reason of the error is the destination file is may be opened by another user. Here, file is in shard location. When run the job on xx.xx.xx.231 server, we got same error again. After some cross checking, we found that Excel file is opened by our Team met.

Thanks to All,

Pugal

Pugal
  • 539
  • 5
  • 20
  • The message looks like it is surrounding a COM object referenced by EXCEL. In our shop we don't typically have EXCEL installed on the servers. When we need to import/export files, we use flat files in *.csv format. Are you running this in debug mode from your desktop, or from SQL Agent job, etc. – plditallo Nov 26 '19 at 23:15
  • On `77` server MS Excel has been installed. I have no idea about _debug mode_. So let me check and update soon. And we will try .csv file format. – Pugal Nov 27 '19 at 07:04
  • please once again try after check remote launch and remote activation as well. – Ajeet Verma Nov 27 '19 at 08:29
  • No luck @AjeetVerma – Pugal Nov 27 '19 at 08:40
  • Then sorry bro ,lets wait for someone else responce – Ajeet Verma Nov 27 '19 at 08:41
  • Hmm, Thanks bro. – Pugal Nov 27 '19 at 08:42
  • @plditallo, If I understand correctly, then this will my answer, I run the SSIS package from SQL Agent job in SSMS. And our reports have multiple sheets. So `.csv` file is not useful. – Pugal Nov 27 '19 at 09:17

1 Answers1

0

You might need to provide permission using the below instruction.

  1. Click Start -> Run -> Type -> dcomcnfg, expand Component Services -> Computers -> My Computer -> DCOM Config.
  2. Click View -> Detail -> Now you will get Application Name and Application ID in right side.
  3. Scroll down and find the application ID " " -> Right Click -> Properties and select the Security tab.
  4. Click Customize under "Launch & Activation Permission" -> click Edit -> Add in the account NT AUTHORITY\SYSTEM and set local launch and local activation.
  5. Restart the application Service linked to this Application ID or restart the server and monitor
Ajeet Verma
  • 1,021
  • 1
  • 7
  • 25