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 9, 2009 at 3:52 pm
duplicate post.
please continue discussion here .
September 9, 2009 at 3:59 pm
I have to admit, that this is indeed a duplicate post and apologize, if any policies, unwritten or written, have been ignored.
But since the cause of the problem might be of either administrative OR SSIS related nature, the author wanted to spread the subject across different forums.
Regards,
Mike
September 10, 2009 at 5:39 am
Hi Mike...
I need some more info...I suppose ur using checkpoint feature
1. You must have developed the pacakge on your development system and deployed on server. So the path of the checkpoint file is it refreshed according to the server folder and location?
Pass your checkpoint settings and have used checkpoint path in your config file..?
September 10, 2009 at 6:54 am
satish.t (9/10/2009)
Hi Mike...I need some more info...I suppose ur using checkpoint feature
1. You must have developed the pacakge on your development system and deployed on server. So the path of the checkpoint file is it refreshed according to the server folder and location?
Pass your checkpoint settings and have used checkpoint path in your config file..?
Did you read Lutz/lmu92's post?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 10, 2009 at 8:39 am
Hi Satish,
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"
September 10, 2009 at 8:40 am
Hi Phil,
How can I get to the post you recommend reading?
Thanks,
Mike
September 10, 2009 at 10:15 am
I feel like I'm fighting a losing battle now, but the post I suggested was three above my original post and it mentioned that this was a duplicate thread! 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 11, 2009 at 11:21 am
OK,
Now I have learned why posting the same question in different groups is not recommended
Just causes confusion ...
And of course, who can read has an advantage.
Thanks Phil
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply