May 28, 2009 at 2:05 am
I have created the SSIS package and deployed to the file system. I want to schedule the package using SQL Agent Jobs. When I try to create the New Job Steb there is no option in the Step Type for SSIS Package
Any help would be appreciated.
May 28, 2009 at 7:07 am
This is the T-SQL for creating a job. You will need to edit for servername, login, filename etc.
You might have just missed the option for running a SSIS package, so have a close look...
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'Sample',
@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'your login here', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'Sample', @server_name = N'SLNDAT12'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'Sample', @step_name=N'Run SSIS from FileSystem',
@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'/FILE "c:\path\path2\package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E',
@database_name=N'master',
@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'Sample',
@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'your login here',
@notify_email_operator_name=N'',
@notify_netsend_operator_name=N'',
@notify_page_operator_name=N''
GO
May 28, 2009 at 7:13 am
To schedule the SSIS Package in SQL server, create a job under SQL Server Agent.
To create a new job, right click on the Jobs and select New Job option and follow the steps below.
1. Provide the name of the job to be created.
2. Select the Steps from the left panel of the window
3. Click the New button.
4. Provide the Step Name
5. Select the Type as SQL Server Integration Services Package
6. Select the Package source as File System
7. To select the package click on […]
8. Select the SSIS package(packagename.dtsx) and click Ok. (Take this package from the Deployed location, ex: C:\...\Microsoft SQLServer\90\DTS\Packages\. )
9. Click Ok in the Job Step window.
10. Select Schedules under the Steps in the New Job window.
11. Enter the Name.
12. Provide the details of occurrences of this job.
13. Click Ok
14. Click Ok in the New Job window.
15. A job is created under the SQL Server Agent.
16. Select the job which was created. Right click on it and then select Start Job option to start the job immediately or it ll run @ the scheduled time.
May 28, 2009 at 9:11 am
All is OK until I get to Step 5 where there is no SSIS Package option in the Drop Down
June 16, 2009 at 10:43 am
I have the same problem on one of my machines. When I go to the Advanced Tab under Steps the section titled SQL Server Integration Services Package tells me "There are no options for this command type". I can log through the SSIS Package itself, but I'd like to be able to log it through the SQL Server Agent.
June 16, 2009 at 4:10 pm
Do you have Integration Services installed on the box that you're trying to schedule it on?
June 17, 2009 at 8:35 am
Yes, Integration Services is installed on the server.
June 17, 2009 at 8:42 am
What edition of SQL Server are you using? Standard, Enterprise, or something else?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 17, 2009 at 10:20 am
On this machine we use SQL Standard v 9.00.2047.00.
Other machines are running v9.00.4035.00 - maybe we need to apply the patch to the machine, but I wouldn't expect something a important as logging to change in a patch.
June 18, 2009 at 7:28 am
Nah. The only reason I was asking about edition is because Workgroup doesn't natively support SSIS, and won't run jobs for it. Was just checking for that possibility.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 22, 2009 at 8:20 am
It is always the obvious that you are blind to! Having come back from holiday I tackled our development box again and found that the Services on the dev db box were set to manual and stopped. The GPO that was being applied enforces 'Manual' setting; not any more though!:-)
Thanks to all who contributed.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply