Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to schedule SSIS package Expand / Collapse
Author
Message
Posted Sunday, October 18, 2009 11:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 13, 2010 1:55 AM
Points: 5, Visits: 35
Hello guys,

I have created a simple SSIS package which gets data from Oracle and saves the output to file.
But I couldn't schedule this SSIS package thru SQL Agent job.

Can you help in how to manage it.

This is what i have done:
Created such user:
USE [msdb]
GO
CREATE USER [DOMAIN\account] FOR LOGIN [DOMAIN\account]
GO
USE [msdb]
GO
EXEC sp_addrolemember N'SQLAgentOperatorRole', N'DOMAIN\account'
GO
USE [msdb]
GO
EXEC sp_addrolemember N'SQLAgentReaderRole', N'DOMAIN\account'
GO
USE [msdb]
GO
EXEC sp_addrolemember N'SQLAgentUserRole', N'DOMAIN\account'
GO
USE [master]
GO
EXEC sp_addsrvrolemember @loginame = N'DOMAIN\account', @rolename = N'sysadmin'
GO

Created credential and proxy:
USE [master]
GO
CREATE CREDENTIAL [account-CREC] WITH IDENTITY = 'DOMAIN\account',
secret = 'WindowLoginPassword'
GO
USE [msdb]
GO
EXEC sp_add_proxy @proxy_name='MyProxy', @credential_name='account-CREC'
GO
EXEC sp_grant_login_to_proxy @login_name='DOMAIN\account', @proxy_name='MyProxy'
GO
EXEC sp_grant_proxy_to_subsystem @proxy_name='MyProxy', @subsystem_name='SSIS'
GO

on server level added to such groups:
Administrators
SQLServer2005DTSUser$SERVERNAME
SQLServer2005MSSQLUser$SERVERNAME$INSTANCE
SQLServer2005SQLAgentUser$SERVERNAME$INSTANCE

at local policy added to:
Adjust memory-quotas for process
Log on as a batch job

while running the job get the follwoing error:
Message
Executed as user: DOMAIN\account. The process could not be created for step 1
of job 0xBA6BB55729F5314F8FADB8ECF7E16D2F
(reason: A required privilege is not held by the client)

Post #804909
Posted Monday, October 19, 2009 7:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, November 24, 2014 6:56 AM
Points: 2,699, Visits: 1,144
Try this link and see if it helps you

http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/dc9fa982-bf27-4a68-a86c-12b4d11a598a


--------------------------------------------------------------------------------------
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 #805005
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse