Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Failure Executing SSIS package from C# on two separate servers

Posted on 2009-02-22
4
Medium Priority
?
884 Views
Last Modified: 2013-11-30
I have a ssis package on SQL Server machine.
I wrote a C# webservice that loads the package and then executes it.
when I installed the webservice on the IIS of a different server the execution returns DTSExecResult.Failure.

I tried creating a new Application Pool and set its Identity to Configurable, and gave it a domain user which is Administrator on the SQL Server machine and has access to the package.
Still no luck....

What kind of user & permissions do I need to define?

I would appreciate any help...
Thanks
0
Comment
Question by:zivf
  • 2
  • 2
4 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 23706900
Do you have SSIS runtime installed on the working IIS machine, and no SSIS runtime installed on the non working IIS machine?
0
 
LVL 1

Author Comment

by:zivf
ID: 23708990
Hi,
The answer is both Yes and No.
The server where the package is saved has SSIS runtime installed.

As to the other server (IIS server) - at first it was a server with no ssis runtime installed.
So I tried from a different server which does have ssis installed, but still it couldn't execute.

When I try running it from the IIS server, I also get a "login failed" in the Event viewer (Application event) of the Sql machine (where the package is installed).
The source is MSSQLSERVER and it says "Login failed for user Domain\UserName".

What does all this mean??
0
 
LVL 1

Author Comment

by:zivf
ID: 23709684
Hi,
Some more info on the errors I get:
1. When I configure the Application Pool Identity to: local system, I get on Machine B the following error (event viewer- application):
    Product: SQL Server
    ID: 18456
    Source: MSSQLSERVER
    Version: 10.0
    Component: SQLEngine
    Symbolic Name: LOGON_FAILED
    Message: Login failed for user "domain\ServerName$"

    I found a description of this error in the microsoft Support Center. It talks about problem with authentication.
    I still didn't finish investigating it.

    (on machine A I don't get any error in the event viewer)

2. When I configure the Application Pool Identity to: Configurable, and set an Administrator user "Domain\UserName" (administrator on both A & B machines) I don't get on Machine B any error in the event viewer, but I do get one on Machine A.
    On Machine A I get the error:
    Source: SQLISPackage
    ID: 12291
    User: Domain\UserName
    Description: Package "packageName" failed.

    I tried looking for it in the Microsoft Support Center, but they have no info on the subject.
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 750 total points
ID: 23716688
Actually your first objective should be to alter your package to include logging, and ensure that it logs to a local drive that exists on all machines (i.e. C drive). Then you can look at the logs for more detailed info.
The two errors you have above are different errors - the first one is because your IIS service account can't log into SQL Server. The second one is because your package won't run, and for more information on why your package won't run you need to enable logging.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

810 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