debugsqlMinor
SQL server agent SSIS error
Viewed 0 times
errorsqlssisagentserver
Problem
I get the following error when I execute my package as a SQL server agent job.
It is an SSIS 2008 package running on a SQL Server 2008 instance. My package security is DontSaveSensitive.
I don't even know how to begin fixing this error.
Where should I check first?
It is an SSIS 2008 package running on a SQL Server 2008 instance. My package security is DontSaveSensitive.
I don't even know how to begin fixing this error.
Where should I check first?
Date a value of time
Log Job History (MyJob)
Step ID 1
Server PCTSQL004
Job Name MyJob
Step Name Job_1
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: CS\DmcSysManager. The process could not be created for step 1 of job 0x63BB5A86DB23F947866D2A806BE4CC6B (reason: A required privilege is not held by the client). The step failed.Solution
It is an SSIS 2008 package running on a SQL Server 2008 instance. My package security is DontSaveSensitive.
You have to save the package with
Then schedule it using SQL Agent Job as below :
Now the user account that you use should have permissions on the database server instance.
You can check the permissions using below T-SQL :
You have to save the package with
EncryptAllWithPassword using a password. Then schedule it using SQL Agent Job as below :
"DriveLetter\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe" /FILE "SSIS_Package_Location\bin\Package.dtsx" /DECRYPT "PasswordStrong007" /CONFIGFILE "Location_to_Config_file_If_any\Config.dtsConfig" /CHECKPOINTING OFF /REPORTING ENow the user account that you use should have permissions on the database server instance.
You can check the permissions using below T-SQL :
SELECT [Login Type] = CASE sp.type
WHEN 'u'
THEN 'WINDOWS Login'
WHEN 's'
THEN 'SQL Login'
WHEN 'g'
THEN 'GRP'
END
,convert(CHAR(45), sp.NAME) AS srvLogin
,convert(CHAR(45), sp2.NAME) AS srvRole
,convert(CHAR(25), dbp.NAME) AS dbUser
,convert(CHAR(25), dbp2.NAME) AS dbRole
FROM sys.server_principals AS sp
JOIN sys.database_principals AS dbp ON sp.sid = dbp.sid
JOIN sys.database_role_members AS dbrm ON dbp.principal_Id = dbrm.member_principal_Id
JOIN sys.database_principals AS dbp2 ON dbrm.role_principal_id = dbp2.principal_id
LEFT JOIN sys.server_role_members AS srm ON sp.principal_id = srm.member_principal_id
LEFT JOIN sys.server_principals AS sp2 ON srm.role_principal_id = sp2.principal_idCode Snippets
"DriveLetter\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe" /FILE "SSIS_Package_Location\bin\Package.dtsx" /DECRYPT "PasswordStrong007" /CONFIGFILE "Location_to_Config_file_If_any\Config.dtsConfig" /CHECKPOINTING OFF /REPORTING ESELECT [Login Type] = CASE sp.type
WHEN 'u'
THEN 'WINDOWS Login'
WHEN 's'
THEN 'SQL Login'
WHEN 'g'
THEN 'GRP'
END
,convert(CHAR(45), sp.NAME) AS srvLogin
,convert(CHAR(45), sp2.NAME) AS srvRole
,convert(CHAR(25), dbp.NAME) AS dbUser
,convert(CHAR(25), dbp2.NAME) AS dbRole
FROM sys.server_principals AS sp
JOIN sys.database_principals AS dbp ON sp.sid = dbp.sid
JOIN sys.database_role_members AS dbrm ON dbp.principal_Id = dbrm.member_principal_Id
JOIN sys.database_principals AS dbp2 ON dbrm.role_principal_id = dbp2.principal_id
LEFT JOIN sys.server_role_members AS srm ON sp.principal_id = srm.member_principal_id
LEFT JOIN sys.server_principals AS sp2 ON srm.role_principal_id = sp2.principal_idContext
StackExchange Database Administrators Q#44900, answer score: 2
Revisions (0)
No revisions yet.