Error while running SSIS Package job in SSMS

Question

Error while running SSIS Package job in SSMS

Hi! A SSIS package named "My_Package" has been scheduled as a job in SSMS to be executed daily at 5:00 AM daily.

SQL Server Version : Microsoft SQL Server 2014 Enterprise Edition

The job is scripted as follows:

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE     name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'My_Package', 
    @enabled=1, 
    @notify_level_eventlog=0, 
    @notify_level_email=0, 
    @notify_level_netsend=0, 
    @notify_level_page=0, 
    @delete_level=0, 
    @description=N'Run IS package My_Package

--DOMAIN\sqlserviceaccount', 
    @category_name=N'[Uncategorized (Local)]', 
    @owner_login_name=N'DOMAIN\sqlserviceaccount', @job_id = @jobId         OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run Package', 
    @step_id=1, 
    @cmdexec_success_code=0, 
    @on_success_action=1, 
    @on_success_step_id=0, 
    @on_fail_action=4, 
    @on_fail_step_id=2, 
    @retry_attempts=0, 
    @retry_interval=0, 
    @os_run_priority=0, @subsystem=N'SSIS', 
    @command=N'/FILE "\"D:\Reports\IS_logs\My_Package.dtsx\"" /CHECKPOINTING OFF /REPORTING E', 
    @database_name=N'master', 
    @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Failure E-mail', 
    @step_id=2, 
    @cmdexec_success_code=0, 
    @on_success_action=2, 
    @on_success_step_id=0, 
    @on_fail_action=2, 
    @on_fail_step_id=0, 
    @retry_attempts=0, 
    @retry_interval=0, 
    @os_run_priority=0, @subsystem=N'TSQL', 
    @command=N'declare @subjectLine varchar(128)
declare @messageBody varchar(4048)

declare @fileName varchar(30)

set @fileName = ''My_Package'' + cast(year(getDate()) as varchar(4))

if (month(getDate()) < 10)
begin
set @fileName = @fileName + ''0''
end
set @fileName = @fileName + cast(month(getDate()) as varchar(2))

if (day(getDate()) < 10)
begin
set @fileName = @fileName + ''0'' 
end
set @fileName = @fileName + cast(day(getDate()) as varchar(2)) + ''.log''

set @subjectLine = ''AMS_Clients_Package job failed on '' + @@SERVERNAME
set @messageBody = '' '' + @fileName

execute msdb..sp_send_dbmail
@PROFILE_NAME = ''mailprofile'',
@RECIPIENTS = ''[email protected]'',
@SUBJECT = @subjectLine,
@BODY = ''Check the job history for error message''', 
    @database_name=N'master', 
    @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'5:00AM Schedule', 
    @enabled=1, 
    @freq_type=4, 
    @freq_interval=1, 
    @freq_subday_type=1, 
    @freq_subday_interval=0, 
    @freq_relative_interval=0, 
    @freq_recurrence_factor=0, 
    @active_start_date=20090602, 
    @active_end_date=99991231, 
    @active_start_time=50000, 
    @active_end_time=235959, 
    @schedule_uid=N'fa715710-cb0c-43a0-ad3d-dbcf9c4f5285'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

The Job was working fine till 11th February, 2015 and then started failing daily with the error:

Step Name Run Package Duration 00:00:01 Sql Severity 0 Sql Message ID 0 Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message Executed as user: DOMAIN\sqlserviceaccount. Microsoft (R) SQL Server Execute Package Utility Version 12.0.2000.8 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved.
Started: 5:00:00 AM
Error: 2015-02-15 05:00:01.29
Code: 0xC0014005
Source: My_Package
Description: The connection type "SPCRED" specified for connection manager "SharePoint Credential" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
End Error
Error: 2015-02-15 05:00:01.29
Code: 0xC0010018
Source: My_Package
Description: Error loading value " End Error
Could not load package "D:\Reports\IS_logs\My_Package.dtsx" because of error 0xC0010014.
Description: The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.
Source: My_Package
Started: 5:00:00 AM
Finished: 5:00:01 AM
Elapsed: 0.36 seconds.
The package could not be loaded.
The step failed.

I am a SQLDBA with less knowledge on SSIS. Please suggest.

Answer 1

The package itself is not failing, it is failing to Load. Either your package or it's configuration must have changed.

Firstly, the error that is preventing your job from running is "The connection type "SPCRED" specified for connection manager "SharePoint Credential" is not recognized as a valid connection manager type." The SharePoint connector is not a native SSIS component. Were there any server changes that would require your third party SharePoint component to be re-installed, upgraded, re-licensed or otherwise re-configured?

If that is all OK then looking at the way you are running the job and the error statement "CPackage::LoadFromXML fails" suggests that your connection parameters are being sourced from a SSIS XML .config file. So you should check the config file to check that the connection strings are OK and have not been changed.

If none of that fixes it you will need to open the package in a development environment and debug it from there. Debugging SSIS packages is beyond the scope of this discussion without much more detail of the package in question.