SSIS Package won't execute when called.

views:

301

answers:

+1 Q:

SSIS Package won't execute when called.

Hi,

I have an 2005 SSIS package that I'm calling in a service created in VS 2005. The package will not run. The purpose of the package is to parse a file and put data into a "Load Table".

The package runs perfectly on its own, but will not run at all when executed programatically - when I'm stepping through the code. The Event Viewer indicates that the package has started but then it indicates that it has failed. I don't get any more information than that.

It's not throwing an exception. It's just returning "Failure". I've tried executing against different databases - Same result. The file it's parsing is valid becuase it runs fine when run on it's own.

The only other thing that I can think of is that I'm having some problem with user permissions, but I have no idea on how to go about looking into that issue. Does anyone have any ideas?

+5 A:

Sounds like a permissions issue. Make sure the process it is running as has the same permissions as the account which you are using to run it interactively.

Chris Ballance 2009-10-08 21:10:44

Good answer. More succinct than my own.

David Stratton 2009-10-08 21:13:06

Actually I didn't even read your answer when I posted mine. Yours is also good.

Chris Ballance 2009-10-08 21:14:11

Yeah, I saw you beat me to it, but since I could only vote you up oce, I only posted one of the reasons I voted you up. I normally vote up the people who give essentially the same answer I did but beat me to the punch.

David Stratton 2009-10-08 21:20:18

+3 A:

Without more information it's hard to tell, but this sounds like a permissions issue.

When it's running from code, does the person or user account the code is running under have the appropriate permissions?

For example, if you run it manually, you're most likely using your own credentials. As the developer, I'd assume you have admin rights, so you can perform the task.

However, when run from a program you need to know what user account the program runs under. Is it Asp.Net? The default user is Network Service. Is it a Scheduled Task running under the default Local System account? You'd need to change the account it runs under or grant permissions on the DB appropriately.

David Stratton 2009-10-08 21:12:32

+1 thorough explanation of the permissions issue

Chris Ballance 2009-10-08 21:16:45

Regarding user permissions debugging I really have no idea what I'm doing. So I took a look at the user accounts. My own account, SQLDebugger account and ASPNET account on my machine and made them all part of the administrators group. I was part of a "User Debuggers" group which probably included pretty close to administrative rights. So I'm looking trying to get that back up and running now because I had to log off in order to make those changes. I guess we'll see what happens.

Kevin 2009-10-08 21:38:58

Now that I changed the user permissions. I don't even get event viewer errors. Informational or otherwise and it still doesn't work. How do I check the permissions on the network service user?

Kevin 2009-10-08 22:01:29

+2 A:

When you loaded it from Studio to Integration Services, what Package Protection Level did you use? I've had the best luck with the last in the list: Rely on Server Storage and roles for access control.

thursdaysgeek 2009-10-08 21:15:41

+1. That's a good thought, too.

David Stratton 2009-10-08 21:21:08

Kevin 2009-10-08 21:36:26

Oh and what do you mean by Package Protection Level? I have had the misfortune of doing process fixes instead of new development here. And so I am unsure of what exactly you mean there as I don't have prior SSIS experience.

Kevin 2009-10-08 21:38:24

Are you loading the package from Visual Studio (where you can see and edit the steps) to SQL Server Integration Services (where it can be run as a job on the server)? Setting the package protection level is an option when you are importing a package.

thursdaysgeek 2009-10-08 22:05:06

I can run the package separately in Visual Studio. All the tasks turn green and it completes fine. I have made sure that I am passing in all the same values as when I run it by itself. Though for some reason, it doesn't even get past the data pump / load of file when calling it from the service. The path I'm providing to the file is correct. I'm not sure what else it could be other than a permissions issue.

Kevin 2009-10-08 22:15:27

Why not import the package to Integration Services? Isn't that what it is there for? Then make a job to run the package.

thursdaysgeek 2009-10-09 23:29:31

+1 A:

Does your package have error logging set up? It could help you to see what the problem is.

Also, does the account for the service running the package have the correct rights to the directory where the file to be picked up is stored not just correct rights in SQL Server? We've had that problem before.

HLGEM 2009-10-09 18:27:04

Logs are good !

Sam 2009-10-15 17:56:28

Have you attached Events to the execution of the package? Are you calling the package by code? Which Method are you using?

Please check Loading and Running a Remote Package...

Then when debugging, add a break point at the Console.Write Line where gets info of the error.

Hope it helps,

Arturo

Arturo Caballero 2009-10-09 20:48:05

ansaurus

tags:

views:

answers:

SSIS Package won't execute when called.

related questions