September 25, 2008 at 2:12 am
Guys, if you now this i need your help please.
I have a package on the ERP server that inserts some values from the MIS server and inserts them on an ERP Table.
The package is just an execute sql task. No parameters, nothing else.
When i execute this from visual studio all is ok, but no matter what i do when i execute it through agent i get the follwoing error.
how can i resolve it? what is going on? I can not find a solution, so please if you know respond to me...
Thank you
09/25/2008 10:40:09,EDET LOAD TO ERP,Error,0,PC-SRV-ERP_HR\ERP,EDET LOAD TO ERP,(Job outcome),,The job failed. The Job was invoked by [User domain\user]. The last step to run was step 1 (EDET LOAD TO ERP).,00:00:02,0,0,,,,0
....... Error: 2008-09-25 10:40:10.87 Code: 0xC002F210 Source: Execute SQL Task Execute SQL Task Description: Executing the query
"INSERT INTO TRANSACTEDET (TRANSACTEDET UPD) ... [the code is in here] The package execution fa... The step failed.,00:00:01,0,0,,,,0
September 25, 2008 at 3:53 am
If you click on the plus sign on your job, look at the error message for the actual step, this should provide you with more info.
Also consider using package logging, which will give you a lot more description of what the actual error is.
~PD
September 25, 2008 at 9:34 am
I'm doing roughly the same thing at the moment, has your agent job got permissions to access all of the DB's that you do through Visual Studio, if not you'll need to set them, in fact, check to see if Agent is running under a local system account or a network account as you may need to change it over.
September 26, 2008 at 12:47 am
Lloyd thank you for answering.
Can you please be more specific. I have built other packages (insert from excel to tables and that stuff) and they all work when scheduled through SQL 2005. This specific package was built (creator) from another person of the domain group. I have changed this in the properties of the package however. My problem is, that no matter what i do, and no matter what option i set to data (encrypt sensitive with user key, dont save anything, etc) or regardless if on my data sources are sa, or windows authentication, the packages do execute from visual studio and commandline, but not through sql server agent. With the same package creator (VS 2005), and with the same owner (sql 2005) of the package, other packages run, but this one does not.
I reckon it is a user right thing as you say, but the owner of the package is a sys admin just like the sqlserver agent.
I have run out of ideas... can you read the message that i have posted and try to figure it out? Why is so difficult to schedule a package? ^#$$#% Microsoft!!!
So please help me a bit more if you can, and tell me what exactly you suggest...
I would be gratefull if you could provide me some more info...
Kind Regards,
Dionisis
September 26, 2008 at 1:07 am
Guys, this is what the log output says
Started: 10:06:21 §£
Error: 2008-09-26 10:06:22.48
Code: 0xC0202009
Source: ERP_Import Connection manager "PC-SRV-ERP_HR\ERP.TRANSIT.mis"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'INTERFRANCHISE\PC-SRV-MIS$'.".
End Error
Error: 2008-09-26 10:06:22.48
Code: 0xC020801C
Source: ERP Sales OLE DB Source [1]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "PC-SRV-ERP_HR\ERP.TRANSIT.mis" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
End Error
Error: 2008-09-26 10:06:22.48
Code: 0xC0047017
Source: ERP Sales DTS.Pipeline
Description: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
End Error
Error: 2008-09-26 10:06:22.48
Code: 0xC004700C
Source: ERP Sales DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2008-09-26 10:06:22.48
Code: 0xC0024107
Source: ERP Sales
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 10:06:21 §£
Finished: 10:06:22 §£
Elapsed: 0.563 seconds
Any comments? 🙂
September 26, 2008 at 2:05 am
Basically the error is saying that you don't have login rights for the PC-SRV-ERP_HR\ERP.TRANSIT.mis which I'm guessing would be an mis db on a PC-SRV-ERP_HR\ERP.TRANSIT instance.
I'm assuming that the Agent Account is running under the INTERFRANCHISE\PC-SRV-MIS$ login which is the one that needs rights to it, just give it permission to the DB in question and add the relevant rights, Select/update/insert.
Are all the other packages running under the agent to the same?
September 26, 2008 at 3:57 am
Ok, please let me explain if you can help me a bit more...
The owner of the job (Job Properties in management studio when you open the package) is interfranchise\dfal (my self). I am (interfranchise\dfal) also the creator of the package (in package properties). Ok, now you say that you are assuming (and you are probably right) that the Agent Account is running under the INTERFRANCHISE\PC-SRV-MIS$ login. On my management studio under the security--> logins i see this user you say and also I see the user PC-SRV-MIS\SQLServer2005SQLAgentUser$PC-SRV-MIS$ECDL_MIS.
Firstly, how can i find out if the user you say is the one that executes the job (apart from seeing it on the log). When i go to his properties all i see is that he is public and sysadmin. The same stand for the second user i mentioned. Secondly, these are not domain account name users, right? so how can i recreate them on the ERP_HR Server, in order to give them access to the Transit DB?
I am saying this because when i go to security logins of the ERP server to add him inorder to add him afterwards to the transit db the server says can not find the user. So how can i add him to the ERP_HR server in order to do what you say? (that is to give him access to the transit db, on ERP_HR server?)
Please any respond i would appreciate very much.. i am kind of lost...
April 6, 2011 at 11:40 am
Not sure if you figured this out or not... But I don't see a response with the most obvious answer.
This is a simple mistake I make all too often
I suspect that you created the object without specifying which account you wanted it created under.
For example, in my databases we use a generic "dbo" account for all our objects. So our objects would be
[DatabaseName].dbo.[objectname]
I am guessing that you simply forgot to specify which account you wanted the object TRANSACTEDET created under.
i.e. you probably said something like
Create Table TRANSACTEDET blah blah blah.
When you should have used
Create Table dbo.TRANSACTEDET
So, I would assume that your account is the only account that has access to the table. So when you run the package locally through VS, the package inherits your permissions and can access the object; but Agent is unable to do so since it does not have access.
Recreate the table with the correct permissions and you should be golden.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply