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:
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  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:
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!!