SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Problem With Checkpoint Settings


SSIS Problem With Checkpoint Settings

Author
Message
Mike-448558
Mike-448558
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 71
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?
Vishal Singh
Vishal Singh
Mr or Mrs. 500
Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)

Group: General Forum Members
Points: 574 Visits: 517
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
Silverfox
Silverfox
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3544 Visits: 1161
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

--------------------------------------------------------------------------------------
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
Mike-448558
Mike-448558
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 71
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"
Dehqon D.
Dehqon D.
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 925
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search