May 6, 2008 at 7:09 am
Hi, I was wondering if anybody would be able to help me and realise by looking round that is a reasonably common problem, but it’s starting to drive me mad now!!
I’ve got an SSIS package which takes data from an excel spreadsheet and using a stored procedure updates a table in a database, before moving the spreadsheet to another folder once it finishes. This works fine when in the Business Intelligence Design Studio, but when I try to set it up to run from the SQL Server Agent I get nothing but problems. I’ve had a look around various forums and seen that many people have had this problem and I’ve followed advice that other people have given, but try as I might nothing will work for me.
Currently I’m trying to go down the package configurations route. I’ve enabled the use of package configurations in the development studio and specified the configuration to pick up the destinations, usernames and passwords of everything that I have in the Connection Managers. I then go to the SQL agent and as a step tell it to execute the SSIS package using the package configuration xml file that it has created. Both the SSIS package and the package configuration file are saved and been asked to run from the sql server I will be using. I then go to run the package and it thinks about it for a minute or so before falling over. The history then says the below:
Message
Executed as user: UKWACALOR\sql2k5svc. ....00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:35:31 Progress: 2008-05-06 11:36:32.10 Source: Data Flow Task 1 Validating: 0% complete End Progress Error: 2008-05-06 11:36:32.15 Code: 0xC0202009 Source: WiReCAPSLoad1 Connection manager "DestinationConnectionOLEDB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E4D Description: "Login failed for user 'sa'.". End Error Error: 2008-05-06 11:36:32.15 Code: 0xC020801C Source: Data Flow Task 1 Destination - cstCAPSLoad1 [32] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DestinationConnectionOLEDB" failed with error code 0xC0202009. ... The package execution fa... The step failed.
The DestinationConnectionOLEDB is pointing at a database on the same server as where I have the SSIS package and configuration file stored. I’ve gone into the xml file and noticed that the passwords aren’t saved so I’ve added the passwords manually into the file after everytime it says username=sa but this hasn’t made a difference. I’ve also made sure that the package is saved to dontsaveencrypted.
I’ve gone through the following article:
http://support.microsoft.com/kb/918760
and tried to use things such as credentials but these haven’t helped either.
I’m really not sure why its giving me such hassle, I have the SSIS package on the sql server, I have its package configuration on the same server, I have the databases it wants to access on the same server, I’m using the sa account wherever I can to make sure I have no access / rights issues, so I really have no idea why this won’t work. All I can think of is that the sql2k5svc account that the sql agent job then runs under doesn’t have authority to get to the database or the package configuration file, but I’ve added that account to have full control on the database that it will accessing, but again no joy. How do I change it so that another account can be used to run this, one that I know has all the authority required to access everything it should?? And why would the login for sa be failing when it’s the highest level account??
I’ve read so many different articles on this problem and tried to follow so many different resolutions that I really don’t know how any of this is meant to work anymore!!
Any help would be gratefully received!!
Thanks
Brad
May 6, 2008 at 8:39 am
I personally have not had any experience with changing the specific user a package runs under. My understanding is that the package always executes in the Agent User context. As far as why the sa login is failing, I recall you said you were not saving passwords in the package which is probably the cause. I don't know that I would want to save that information in the package anyhow.
The way I have setup my servers is by using a domain user and adding it with sysadmin privilidges. This may not be the best practice, which we can discuss in another forum. This would guarantee the Agent User context will have the appropriate rights to the data. I know you said you had added database rights but this may be the missing step.
May 6, 2008 at 1:16 pm
SQL Server Agent in SQL Server 2005 runs a lot different then you would expect. I've run into this same issue before and surprising "sa" does not have all the permissions you would think to run an agent job.
If you are running the SQL Server agent service under a domain account, use this account as the "Owner" when saving the Agent job. By runing under the account that SQl Server Agent runs under, it usually has the permissions it needs.
You will probably want to sign on to the server using this domain account where SQL Server Agent is installed and verify that all the network drives and configuration files can be seen by this user account.
July 31, 2008 at 6:52 am
Hi
I've just followed the instructions on this post by Hongwei Li
http://www.codeproject.com/KB/aspnet/Schedule__Run__SSIS__DTS.aspx?display=Print
Using proxies and such. Great fun - I never thought I'd have to learn quite so much stuff just to get a csv imported to sql server.
The explanation is quite full, and the steps are simple to just type in. I made a little change:
Where he has 'devlogin', I put my own real network login.
This does mean, I expect, that when my password expires and has to be changed (as it does here every 30 days), I'll have to remember to change the password in the proxy.
So I'll have to come up with a better fix, as I am a contractor here, and it will stop working when I leave! Not the best for my reputation!
Hey, but it works for now.
Regards
James Towell, Reading, UK
July 23, 2009 at 8:39 am
Just to say thanks for advising about http://www.codeproject.com/KB/aspnet/Schedule__Run__SSIS__DTS.aspx?display=Print
That was really really helpful - instead of using my windows login (in replacement for devlogin) I created a sql account on the box which seems to work fine. Only problem would be if you don't have mixed authentication on the sql box I guess..
Thanks again
January 12, 2011 at 2:27 pm
In SQL 2005 Edit the step and make sure on Data Sources tab that the value of your provider in the connection string of your Destination Connection OLEDB is correct.
Data Source=23423423423423;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=false;
NOT:
Data Source=23423423423423T;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=false;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply