The strange part of all is that no checkpoint is getting specified during the package designing stage
After the package has been deployed onto the server the following user stored procedure is used to create the job
This proc passes the parameter CHECKPOINTING to the job which is set to OFF
CREATE proc [dbo].[usp_ADM_xxxxx]
@etl_without_prefix varchar(128),
@email_address varchar(128),
@auto_start bit = 0,
@auto_start_cleanup bit = 0,
@synch bit = 0,
@ids_connection bit = 0,
@heamis_connection bit = 0,
@prod bit = 0
as
begin
declare @job_name varchar(128)
set @job_name='jobCL_'+@etl_without_prefix
declare @etl_name varchar(128)
set @etl_name='etlCL_'+@etl_without_prefix
declare @command varchar(256)
declare @idsConfig varchar(128)
select @idsConfig =
case
when @ids_connection = 0 then ''
when @prod = 0 then ' /CONFIGFILE "\\[…]\ids.dtsconfig"'
else ' /CONFIGFILE "\\[...]\ids.dtsconfig"'
end
declare @heamisConfig varchar(128)
select @heamisConfig =
case
when @heamis_connection = 0 then ''
when @prod = 0 then ' /CONFIGFILE "\\[..]\xxxxxD5.dtsConfig"'
else ' /CONFIGFILE "\\[...]\xxxxxP8.dtsConfig"'
end
set @command = '/SQL "'+@etl_name+'" /SERVER yourServer ' +
@idsConfig +
@heamisConfig +
' /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E'
declare @success_msg varchar(2048)
set @success_msg = 'sp_send_dbmail
@profile_name = ''hebi'',
@Recipients= '''+@email_address+';'',
@Copy_Recipients= default,
@Subject= ''xxxxx ['+@job_name+'] succeeded'',
@Body= ''xxxxx ['+@job_name+'] succeeded'''
declare @failure_msg varchar(2048)
set @failure_msg = 'sp_send_dbmail
@profile_name = ''xxbi'',
@Recipients= '''+@email_address+';'',
@Copy_Recipients= default,
@Subject= ''xxxxx ['+@job_name+'] failed'',
@Body= ''xxxxx ['+@job_name+'] failed'''
declare @user varchar(128)
set @user = SUSER_NAME()
if exists
(
select *
from msdb.dbo.sysjobs
where name = @job_name
)
exec msdb.dbo.sp_delete_job @job_name = @job_name
exec msdb.dbo.sp_add_job
@job_name = @job_name,
@enabled = 1,
@description = 'Automatically created by usp_xxxxxx',
@category_name=N'Development (see Owner)',
@owner_login_name = @user
exec msdb.dbo.sp_add_jobserver
@job_name = @job_name,
@server_name = '(LOCAL)'
exec msdb.dbo.sp_add_jobstep
@job_name=@job_name,
@step_id=1,
@step_name='Notify on failure',
@subsystem='TSQL', --CMDEXEC=DTS, Dts=SSIS, TSQL=script
@command=@failure_msg, --SSIS name, T-SQL script, or command line DTS start
--example SSIS exec
--@command=N'/SQL "etlCL_CheckByMailFollowup" /SERVER xxxxx /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E'
@database_name='msdb',
@on_success_action=2, --1=success, 2=failure, 3=go to next, 4=go to on_success_step_id
@on_success_step_id=default,
@on_fail_action=2, --1=success, 2=failure, 3=go to next, 4=go to on_fail_step_id
@on_fail_step_id=default
exec msdb.dbo.sp_add_jobstep
@job_name=@job_name,
@step_id=1,
@step_name='Notify on success',
@subsystem='TSQL', --CMDEXEC=DTS, Dts=SSIS, TSQL=script
@command=@success_msg, --SSIS name, T-SQL script, or command line DTS start
--example SSIS exec
--@command=N'/SQL "etlCL_CheckByMailFollowup" /SERVER xxxxx /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E'
@database_name='msdb',
@on_success_action=1, --1=success, 2=failure, 3=go to next, 4=go to on_success_step_id
@on_success_step_id=default,
@on_fail_action=3, --1=success, 2=failure, 3=go to next, 4=go to on_fail_step_id
@on_fail_step_id=default
exec msdb.dbo.sp_add_jobstep
@job_name=@job_name,
@step_id=1,
@step_name=@etl_name,
@subsystem='DTS', --CMDEXEC=DTS, Dts=SSIS, TSQL=script
@command=@command, --SSIS name, T-SQL script, or command line DTS start
--example SSIS exec
--@command=N'/SQL "etlCL_CheckByMailFollowup" /SERVER xxxxx /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E'
@database_name=default,
@on_success_action=3, --1=success, 2=failure, 3=go to next, 4=go to on_success_step_id
@on_success_step_id=default,
@on_fail_action=4, --1=success, 2=failure, 3=go to next, 4=go to on_fail_step_id
@on_fail_step_id=3
if(@auto_start = 1)
begin
if(@synch = 1)
begin
declare @result int
exec @result = msdb.dbo.sp_start_job_synch;2
@jobname = @job_name
print @result
if(@auto_start_cleanup = 1)
exec msdb.dbo.sp_delete_job @job_name = @job_name
end
else
exec msdb.dbo.sp_start_job @job_name = @job_name
end
end
Looking up the job's properties the Command Line tab on the General page shows the following entry:
/SQL "jobName" /SERVER ldwzd4969 /CONFIGFILE "\\xxxxxx\e$\sql_share\ssis\config_files\ids.dtsconfig" /CONFIGFILE "\\xxxxx\e$\SQL_SHARE\SSIS\CONFIG_FILES\xxxxxD5.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING ON /RESTART FORCE /LOGGER "{59B2C6A5-663F-4C20-8863-C83F9B72E2EB}";"LOG__1948_Early_Payment_Default_Total_Booked.txt" /REPORTING E
Here are the configuration files the stored proc references:
xxxxxD5.dtsConfig
GeneratedFromPackageName="xxxxx_IDS"
GeneratedFromPackageID="{995734AA5F-B34E-439D-BE87-4FC846534FC9}"
GeneratedDate="5/6/2009 9:14:38 AM"
Path="\Package.Connections[xxxxx].Properties[ServerName]"
ids.dtsconfig
GeneratedFromPackageName="xxxxx_Data"
GeneratedFromPackageID="{45A93147-DE76-488C-A901-4D227A161AD5}"
GeneratedDate="7/24/2008 10:10:40 AM"
Path="\Package.Connections[IDS].Properties[Password]"
Question: Since the config files reference another package can it be that the value for CHECKPOINTING is getting through the referenced package?
DTSConfigurationFileInfo
GeneratedBy="xxxxx\cxxxorbp"
GeneratedFromPackageName="xxxxx_Data"
GeneratedFromPackageID="{45A93147-DE76-488C-A901-4D227A161AD5}"
GeneratedDate="7/24/2008 10:10:40 AM"