Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2008-11-20
1
Medium Priority
?
1,465 Views
Last Modified: 2013-11-10
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

0
Comment
Question by:Soccerman55
1 Comment
 
LVL 1

Accepted Solution

by:
Soccerman55 earned 0 total points
ID: 23003045
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question