Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 SSIS Import Problem

Author  Topic 

Darren996
Starting Member

5 Posts

Posted - 2014-09-15 : 17:11:53
I have a simple package that backs up the transaction log. I scheduled it and it runs fine. I script the job and export the package. I import the package ran the script to create the job and it gives me an error about missing sysmaintplan_subplans. I wrote a script to fix that. Now I get an empty error message about the job failing but the transaction log actually backed up. What did I do?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-16 : 07:37:24
I'd have to look at the package and run environment to begin to find the problem
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-16 : 07:38:58
Did you derploy the package and run it from the server? Then you can run the execution report from ssisdb
Go to Top of Page

Darren996
Starting Member

5 Posts

Posted - 2014-09-16 : 09:53:30
I am running it on my development machine. If I schedule the package from the package manager it runs fine. It's when I script the job to run the package that it fails.

This is the error I get:

Date 9/16/2014 9:00:00 AM
Log Job History (FloorPlanServerSQLXLogBackupSetupTest.Subplan_1)

Step ID 1
Server FBBIDEV-WKSTN
Job Name FloorPlanServerSQLXLogBackupSetupTest.Subplan_1
Step Name Subplan_1
Duration 00:00:02
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: NT Service\SQLSERVERAGENT. Started: 9:00:00 AM Finished: 9:00:01 AM Elapsed: 0.874 seconds. The package execution failed. The step failed.

It's kinda hard to google that.

Here's my script:

USE [msdb]
GO

/****** Object: Job [FloorPlanServerSQLXLogBackupSetupTest.Subplan_1] Script Date: 9/16/2014 8:54:52 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 9/16/2014 8:54:52 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'FloorPlanServerSQLXLogBackupSetupTest.Subplan_1',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Database Maintenance',
@owner_login_name=N'DOMAIN\REDACTED', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Subplan_1] Script Date: 9/16/2014 8:54:52 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Subplan_1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@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'SSIS',
@command=N'/Server "$(ESCAPE_NONE(SRVR))" /SQL "Maintenance Plans\FloorPlanServerSQLXLogBackupSetupTest" /set "\Package\Subplan_1.Disable;false"',
@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'FloorPlanServerSQLXLogBackupSetupTest.Subplan_1',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20140915,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'dc1827e1-2b54-4922-8899-31bc7b414103'
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




And the other part


insert into dbo.sysmaintplan_subplans (
plan_id,
subplan_name,
job_id,
schedule_id,
subplan_id,
subplan_description
)
values (
N'E908079A-8AF2-4413-81F5-7F05C7F56275',
N'Subplan_1',
(select top(1) job_id from dbo.sysjobs where name = N'FloorPlanServerSQLXLogBackupSetupTest.Subplan_1'),
(select top(1) schedule_id from dbo.sysschedules where schedule_uid = N'dc1827e1-2b54-4922-8899-31bc7b414103'),
NEWID(),
N''
)
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-16 : 10:02:57
From the Agent job history, can you post the entire entry of the failed execution? Or is that message all that you see there?
Go to Top of Page

Darren996
Starting Member

5 Posts

Posted - 2014-09-16 : 10:09:56
I edited my previous post for the content of the entire message. Nothing shows up in dbo.sysmaintplan_log either. I don't have very much experience on this sort of thing so it could be something stupid.
Go to Top of Page

Darren996
Starting Member

5 Posts

Posted - 2014-09-16 : 12:05:32
Ah! The subplan id is stored in the package and it has to match.

insert into dbo.sysmaintplan_subplans (
plan_id,
subplan_name,
job_id,
schedule_id,
subplan_id,
subplan_description
)
values (
N'E908079A-8AF2-4413-81F5-7F05C7F56275',
N'Subplan_1',
(select top(1) job_id from dbo.sysjobs where name = N'FloorPlanServerSQLXLogBackupSetupTest.Subplan_1'),
(select top(1) schedule_id from dbo.sysschedules where schedule_uid = N'dc1827e1-2b54-4922-8899-31bc7b414103'),
N'A396E8B1-0C6F-41AB-A2FD-3262CA7FC227',
N''
)
Go to Top of Page
   

- Advertisement -