November 17, 2009 at 1:55 am
Hi Experts,
My requirement is creation of a job so that it needs to run a SSIS package.
Please give me the procedure for creation of a job for executing the SSIS package.(it is very helpful if u provide me the reply with snapshots).
Thank you in advance,
Venu Gopal.K
Software Engineer
INDIA
November 17, 2009 at 2:17 am
refer the attached file
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
November 17, 2009 at 2:18 am
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'{TEST JOB}',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'DOMAIN\USER', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'{TEST JOB}', @server_name = N'SQLSERVER'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'{TEST JOB}', @step_name=N'{TEST STEP}',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'SSIS',
@command=N'/SQL "\LOCATION\SSIS PLAN NAME" /SERVER ENTRPTR /CHECKPOINTING OFF /REPORTING E',
@database_name=N'master',
@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'{TEST JOB}',
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'DOMAIN\USER',
@notify_email_operator_name=N'',
@notify_netsend_operator_name=N'',
@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'{TEST JOB}', @name=N'{TEST SCHEDULE}',
@enabled=1,
@freq_type=8,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20091117,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO
The above creates a test job with test step and test schedule. It shows a job with one step set to run an ssis package.
Alternatively you would create a new job and on the step which you want to run the ssis from, click the 'Type' drop down box and select 'SQL Server Integration Services Package'; then enter the server name and click the '...' to browse the ssis packages available (assuming the package is server based).
If the package is file based, click the 'Package SOurce' drop down and select the required source type (and then the '...' to browse).
HTH
Adam Zacks-------------------------------------------Be Nice, Or Leave
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply