October 18, 2009 at 11:57 pm
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)
October 19, 2009 at 7:11 am
Try this link and see if it helps you
http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/dc9fa982-bf27-4a68-a86c-12b4d11a598a
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply