SSIS Problem With Checkpoint Settings

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » SSIS Problem With Checkpoint Settings

SSIS Problem With Checkpoint Settings

Rate Topic

Display Mode

Topic Options

Author

Message

Mike-448558

Posted Wednesday, September 09, 2009 3:38 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 21, 2012 9:50 AM
Points: 43, Visits: 70

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?

Post #785386

Vishal Singh

Posted Thursday, September 10, 2009 12:55 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 12:30 AM
Points: 353, Visits: 487

hi Mike, never come across with this problem.

You may like to place this question in SQL Server 2005>Development section.?

-Forum Etiquette: How to post Performance Problems

-Forum Etiquette: How to post data/code to get the best help

Post #785541

Silverfox

Posted Thursday, September 10, 2009 12:58 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 6:50 AM
Points: 2,719, Visits: 1,065

Mike (9/9/2009)

Silly question I suppose, but should /CHECKPOINTING ON, be there if you are not using checkpoints

--------------------------------------------------------------------------------------
Recommended Articles on How to help us help you and
solve commonly asked questions

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden
Managing Transaction Logs by Gail Shaw
How to post Performance problems by Gail Shaw
Help, my database is corrupt. Now what? by Gail Shaw

Post #785542

Mike-448558

Posted Friday, September 11, 2009 11:22 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 21, 2012 9:50 AM
Points: 43, Visits: 70

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"

Post #786591

Dehqon D.

Posted Monday, January 07, 2013 8:24 AM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 3:40 AM
Points: 40, Visits: 819

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.

Post #1403648

« Prev Topic | Next Topic »

Permissions