September 9, 2009 at 3:38 pm
What can be the cause for the following scenario?
SSIS package executed by a job fails with the following error
"The package is configured to always restart from a checkpoint, but checkpoint file is not specified"
But the CHECKPOINTUSAGE property of the package is set to "Never", the SAVECHECKPOINTS is set to "False", and no file is specified under CHECKPOINTFILENAME.
These settings are all made within SSIS Designer
The CommandLine tab for the JOB STEP Properties show the following entry
/CHECKPOINTING ON
Anyone an idea for the root of this problem?
September 10, 2009 at 12:55 am
September 10, 2009 at 12:58 am
Mike (9/9/2009)
What can be the cause for the following scenario?SSIS package executed by a job fails with the following error
"The package is configured to always restart from a checkpoint, but checkpoint file is not specified"
But the CHECKPOINTUSAGE property of the package is set to "Never", the SAVECHECKPOINTS is set to "False", and no file is specified under CHECKPOINTFILENAME.
These settings are all made within SSIS Designer
The CommandLine tab for the JOB STEP Properties show the following entry
/CHECKPOINTING ON
Anyone an idea for the root of this problem?
Silly question I suppose, but should /CHECKPOINTING ON, be there if you are not using checkpoints
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 11, 2009 at 11:22 am
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"
January 7, 2013 at 8:24 am
It's too late but still,
I'd suggest you set the CHECKPOINTING to OFF in the job step. It's under the Execution options tab.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply