Hi Guys

I am trying to run a SSIS package. I am having some issues in that..

The setup

SQL Server 2012 with SSIS.

A active directory service account(sql_user)  has been created, added to SQL Security as sysadmin, give rights for msdb and SSISDB for roles

db_ssisltduser

db_ssisoperator

public

SQLAgentOperatorRole

SQLAgentReaderRole

SQLAgentUserRole

and for SSISDB

public , ssis_admin

A credential has been created with the same user account who has all the permission above. A proxy has been created using the credential made above.

SSIS package deployed and run using the proxy.

ISSUE :

I am running a very simple SSIS package to refresh the excel file.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop.Excel


<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
	Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

	Enum ScriptResults
		Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
		Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
	End Enum

    
    Public Sub Main()
       

        On Error GoTo ErrorHandler

        Dim bt(0) As Byte

        Dim excel As New Microsoft.Office.Interop.Excel.Application
        Dim wb As Microsoft.Office.Interop.Excel.Workbook


        wb = excel.Workbooks.Open("E:\ExcelFile.xlsb")
        wb.RefreshAll()
        wb.Save()
        wb.Close()
        excel.Quit()
        Runtime.InteropServices.Marshal.ReleaseComObject(excel)

        Dts.TaskResult = ScriptResults.Success
    
        Exit Sub
ErrorHandler:

        Dts.Log("Err,Description  " & Err.Description, 0, bt)

        Dts.Log("Err, Description " & Err.GetException.ToString(), 4, bt)

	Dts.TaskResult = ScriptResults.Failure
    End Sub

End Class

The code runs fine when I open visual studio using same AD service account (sql_user), open the project and run it and excel file get updated . When the same package is deployed to SQL and run under the proxy account (sql_user) it fails.

AD user (sql_user) is normal AD users and is able to run the SSIS package succesfuly from visual studio but not from sql agent job. But if I add the sql_user to the Windows Administrators group.. The job runs successfully and file gets updated. Not sure why it would need admin rights and what it access while running the package from sql job but on the other hand, being a normal windows user, sql_users can run the package using visual studio.

I tried adding errorhandler and upon running I am getting error

" the script returned a failure result"

Anyway I get can more detail?

Any help why I am getting the error in sql and what should I add more to get the detailed error.

FYI

For error hanlder, in SSIS -> Logging, I have added msdb as configuration .. not sure if that's right ..

• Edited by Anonymous902 Tuesday, August 25, 2015 5:31 AM

Perhaps the "normal user" has no access to

E:

and/or to a temp dir where Excel stores its intermediate state (the exact location is Excel version related).

Hi

Thanks for reply

It's the same user (sql_user) running jobs from Visual Studio and SQL Job.. and user has access to all location which is confirmed as user can run the ssis package successfully from visual studio.