Execute Package works fine, Deploying Package in Job Step Gives error

Question

To all,

I have a very simple SSIS package that uses an OLE DB source and OLE DB destination to copy some tables to a remote server.

When I run the Execute Package utility and set the database Password=***** in the connection string the Package runs fine.

However, when I deploy the package in a Job Step and set the Password=***** it fails with the following error:

Message
Executed as user: PA0SPDEV01\Administrator. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.1600.22 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  1:00:02 AM  Error: 2009-10-14 01:00:04.73     Code: 0xC0202009     Source: Create ANI DataMart GetDIDANIIntercept [28]     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E37.  End Error  Error: 2009-10-14 01:00:04.73     Code: 0xC02020E8     Source: Create ANI DataMart GetDIDANIIntercept [28]     Description: Opening a rowset for "[dbo].[DIDANIIntercept]" failed. Check that the object exists in the database.  End Error  Error: 2009-10-14 01:00:04.73     Code: 0xC004706B     Source: Create ANI DataMart SSIS.Pipeline     Description: "component "GetDIDANIIntercept" (28)" failed validation and returned validation status "VS_ISBROKEN".  End Error  Error: 2009-10-14 01:00:04.73     Code: 0xC004700C     Source: Create ANI DataMart SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2009-10-14 01:00:04.73     Code: 0xC0024107     Source: Create ANI DataMart      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  1:00:02 AM  Finished: 1:00:04 AM  Elapsed:  2.156 seconds.  The package execution failed.  The step failed.

I was able to set the Password=**** in the connection string and get it to work one time. But now I cannot get the Package to run from the SQL Job. Each of the OLE DB connections has its own SQL server user for authentication to the source and destination databases. This is the Password I set in the Execute Package utility to get it to run.

Can someone help me determine the root cause of the issue based on the error?

I'm not familiar enough with these error codes to determine what I'm doing wrong.

Thanks,
Eric

Answer 1

change the data provide and check it

Answer 2

Bonvivant,

What do you mean?

The data provider works in the Exec Utility.

I'm not sure I know what you mean 'change the data provide and check it'

Thanks,
Eric

Answer 3

If I were you I would run a Profiler trace (if you can) on the server that "GetDIDANIIntercept" is suppsoed to bea ccessing and make sure that it is accessing it.

---

http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet

Answer 4

Jamie,

Excellent suggestion. I started to do that. I will follow up with the results of that trace.

Eric

Answer 5

Jamie,

The DS is a production, and I mean PRODUCTION, db box.

What are my options if I don't see any traffic coming in. I set Validation  to false hoping that would skip the pre-validation step. That didn't change anything.

What are my options if I do see traffic coming in?

Is anyone able to tell me what the error codes are in the Package debug messages shown above?

If I could see the root cause in the error message i could address the issue that is presenting the issue.

This is the most simple SSIS Package. If I run it in the Execute Utility, and fill in the Passwords manually it works. When I try to schedule the Package as a Job Step I get the error above.

Thanks for the help,
Eric

Answer 6

What account are you using to execute the agent job? Do you need to set UserName in the connection string?

Answer 7

Cho,

Yes, when it works in ExecUtil I have to set the password in the connection string.

When I schedule the job I go to the step I set the password, but either it isn't remembering it or something. All I know is that I get the above error when the job executes. It is the Job Step that is failing.

Any help would be greatly appreciated. If you have any troubleshooting steps I'd be willing to perform these.

Thanks very much,
Eric

Answer 8

Eric,

  Looking at your error, looks like you're having double-hop (user delegation) issue but I cannot seem to tell exactly where your problem is...





Hope that helps you in someway....



Rok

Answer 9

Do you mean that even though I set the Password=****** in the job step I would still encounter authentication issues? Don't these embedded connection string uname/pwd provide enough authentication information to consumate the marriage?

Answer 10

AFAIK, This is an issue with authentication. Check whether you have done the following,
1. Create a credentials and associate a user who has complete access
2. Create a proxy and associate the credentails created in step 1 to it.
3. In the job and in the Run As field select the proxy created.
4. Execute the job.

Try this and post back your response.

---

Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.

Answer 11

there are many ways to troubleshoot this.

1. open the package in SSIS --> enter the username and password again and test all the connections --> right click on the package and click on properties--> security --> enter package password and at the protection level (select encrypt sensitive with password)

--> build the package and schedule the package in sql server agent --> enter the password while scheduling the package in agent ( this will keep the sensitive information encrypt with the password in your package)

---

Please mark the post as answered to help others to choose the best. chandra sekhar pathivada | www.calsql.com (SQL Server Community Website)