Failure Executing SSIS package from C# on two separate servers

Posted on 2009-02-22
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...
Question by:zivf
    LVL 30

    Expert Comment

    Do you have SSIS runtime installed on the working IIS machine, and no SSIS runtime installed on the non working IIS machine?
    LVL 1

    Author Comment

    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??
    LVL 1

    Author Comment

    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.
    LVL 30

    Accepted Solution

    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.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now