October 11, 2011 at 9:27 am
I have a server with multiple instances.
In the Integration Services Server on the machine I have added a package.
On the machine\Instance1 i have a job running under the agent credentials.
The agent account has full SA permissions on the \Instance1 server.
When running a job with a step that calls the ssis package i get this error:
Could not load package "\My_SSIS_Package" because of error 0xC0014062.
Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80040E4D (Login failed for user 'Server\Instance1AgentAccount'.). The SQL statement that was issued has failed.
Can anyone advise what permissions i need to grant the Agent Service Account to get this running?
October 11, 2011 at 9:45 am
My first thought is the pacakge store is not stored on Instance1..
Go out to: C:\Program Files\Microsoft SQL Server\100\DTS\Binn
And look for file: MsDtsSrvr.ini.xml
look for information about folders, in particular:
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>.</ServerName>
</Folder>
What does servername say?
If it is "." then do you have a default instance on this box? If so does that agent account have rights to THAT instance?
CEWII
October 11, 2011 at 10:34 am
Absoluely spot on Elliot, thanks a mil.
Next question. SSIS is a shared component. if i installed a named instance first and then the default, would SSIS be part of the SSIS be part of the named instance?
October 11, 2011 at 1:13 pm
winston Smith (10/11/2011)
Absoluely spot on Elliot, thanks a mil.Next question. SSIS is a shared component. if i installed a named instance first and then the default, would SSIS be part of the SSIS be part of the named instance?
That is a little tricker, there will only be one SSIS server no matter how many instances. I'm not sure you can consider it "part" of either. If you installed the named instance first I *think* it adjusts the config file to point at it, but I can't remember. I know that in a clustered instance it does NOT but stand-alone instances I'm not so sure of. But that config file is the key. On my clustered box I have chosen one instance as the default but have it configured so I can reference both if I need to reference one in particular.
If you have other questions let me know.
CEWII
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply