SQL Server – Integration Services – Error – “Could not load package because of error 0x80070057. The path is not valid.”

 

Background

Our Business Intelligence person came to me with what turned out to be another good error last night.

Error

Error Image

jobHistory-20170125-0721AM

 

Error Textual

Could not load package because of error 0x80070057.  The path is not valid.

The path could not be loaded.

 

TroubleShooting

SQL Server Profiler

Launched the pre-eminent SQL Server troubleshooting tool for lazy Admins, SQL Server Profiler.

Configuration

Configured it to include:

  1. Events
    • User Error Message
  2. Attributes
    • DatabaseName
    • Error

 

Events

 

SSP-20170124-1105PM (cleaned-up)

 

Findings

  1. Execute permission was denied on the object ‘sp_ssis_getpackage’

 

Remediation

As the job’s step is using a proxy account, we added the proxy account to the db_ssisoperator role ( in the msdb database ).

 
set nocount on;
go
 
use [msdb]
go
 
declare @rolename sysname
declare @username sysname
 
set @rolename = 'db_ssisoperator'
set @username = 'domain\SSISProxy'
 
/*
    Create user, if it does not exist
*/
if suser_id(@username) is null
begin
 
    create user [domain\SSISProxy]
        from login [domain\SSISProxy];
 
end
 
/*
 
    IS_ROLEMEMBER
        <a href="https://msdn.microsoft.com/en-us/library/ee677633.aspx">https://msdn.microsoft.com/en-us/library/ee677633.aspx</a>
 
    sp_addrolemember
        <a href="https://msdn.microsoft.com/en-us/library/ms187750.aspx">https://msdn.microsoft.com/en-us/library/ms187750.aspx</a>
 
*/
if ( IS_ROLEMEMBER ( @rolename, @username)  = 0)
begin
 
    print 'Grant ' + @username + ' access to role ' + @rolename
 
    exec sp_addrolemember
              @rolename   = @rolename
            , @membername = @username
            ;
 
end
 
go

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s