Can't run SSIS package in query or in SQL Agent job

I know this question has been asked and answered many times but I can't seem to resolve this issue.  We just setup a new windows 2003 64 bit server with Sql server 2005 64 bit developer edition.  Installed SP2 and CU 7.  SQL Agent and SSIS are both running under the same service account which has been added to the local sql windows groups:  SQLSErver2005DTSUser & SQLServer2005SqlAgentUser.  By default, the SQLServer2005SQLAgentUser is given sysadmin privileges to the local SQL server so the service account has sysadmin privileges.  I think that is all the background on current configuration.

I brought over SSIS packages from our old server.  I modified them to reference the new server and saved them back to the MSDB database.  The protection level on the package is set to use a password.  If I connect to SSIS directly, the package runs successfully.  If I try to execute using XP_Cmdshell and dtexec.

I am getting the following error:

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started:  6:53:58 AM
Could not load package "LoadGraphicsProjectData" because of error 0xC0014062.
Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login failed for user ''. The user is not associated with a trusted SQL Server connection.).  The SQL statement that was issued has failed.
Source:
Started:  6:53:58 AM
Finished: 6:53:59 AM
Elapsed:  0.891 seconds
NULL

I have gone through the different KB articles but haven't found it yet.  I must be missing a setting somewhere but can't remember or figure out where.

Any ideas?
John
master.dbo.xp_cmdshell '"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /Sq LoadGraphicsProjectData /ser dbatest2 /de crypt'

Open in new window

LVL 1
Soccerman55Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Soccerman55Author Commented:
I am embarrassed to post this back but I figured out my issue.  Our systech guys had renamed my server.  I could still connect to SQL Server using the name expected but ssis was running with the newly configured server name.  So that was the missing piece....

JOhn
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.