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 0Message 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.
/REPORTING EWIError, Warning, Information. Ohterwise, yes, as Nigel has specified - something is amiss with the SharePoint connection manager. If this is a clustered instance, did you fail over to a new node?