SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problems trying to run SSIS package from SQL Server Agent job


Problems trying to run SSIS package from SQL Server Agent job

Author
Message
brad.luettke
brad.luettke
Old Hand
Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)

Group: General Forum Members
Points: 323 Visits: 102
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
jim.powers
jim.powers
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7707 Visits: 921
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.
Gordon Radley
Gordon Radley
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1550 Visits: 187
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.



james-541426
james-541426
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 13
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
jackie.jones
jackie.jones
Right there with Babe
Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)

Group: General Forum Members
Points: 750 Visits: 117
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
MRATHBUNDBA
MRATHBUNDBA
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 36
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;



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search