How to fix ‘Object reference not set to an instance of an object’ error in C# Web Service SSIS - Impersonation

Question

I’m building a web service application that executes an SSIS package, but when I try to consume the service (published to IIS on localhost as Web Deploy - File System) I get this response (locally and from the outside):

System.NullReferenceException: Object reference not set to an instance of an object. at WebServiceApplication.CallSP.RunSSIS() in C:\Users\developers\source\repos\ajfmo\WebServiceApplication\WebServiceApplication\CallSP.asmx.cs: line 23

Which refers to this Line:

string connectionString = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;

I've read all the other questions so in this case is not a duplicate.

This is my web.config setting for the connection.

<connectionStrings>
    <clear />
    <add name="DBConnection"
         connectionString="Data Source=localhost;Integrated Security=SSPI;Initial Catalog=master;"
         providerName="System.Data.SqlClient"/>
</connectionStrings>

But when I'm debugging from VS it runs without any issue, Is it related to the IIS setup?.

Already tried (Using a Web Service or Remote Component to Run a Remote Package Programmatically) and got compilation errors - also don't understand the parameter that I need to pass.

This is my code right now.

[WebMethod]
    public string RunSSIS()
    {
        var connectionString = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
        using (var connection = new SqlConnection(connectionString))
        {
            try
            {
                // Create the Integration Services object
                IntegrationServices integrationServices = new IntegrationServices(connection);
                // Get the Integration Services catalog
                Catalog catalog = integrationServices.Catalogs["SSISDB"];
                // Get the folder
                CatalogFolder folder = catalog.Folders["SF2SQL"];
                // Get the project
                ProjectInfo project = folder.Projects["SF2SQL"];
                // Get the package
                PackageInfo package = project.Packages["Package.dtsx"];
                // Run the package
                long executionIdentifier = package.Execute(false, null);
                // Checking completion status
                ExecutionOperation executionOperation = catalog.Executions[executionIdentifier];
                while (!executionOperation.Completed)
                {
                    System.Threading.Thread.Sleep(5000);
                    System.Diagnostics.Debug.WriteLine("Running");
                    executionOperation.Refresh();
                }
                if (executionOperation.Status == Operation.ServerOperationStatus.Success)
                {
                    System.Diagnostics.Debug.WriteLine("Success");
                    return "Success";
                }
                else if (executionOperation.Status == Operation.ServerOperationStatus.Failed)
                {
                    System.Diagnostics.Debug.WriteLine("Failed");
                    return "Failed";
                }
                else
                {
                    System.Diagnostics.Debug.WriteLine("Something else…");
                    return "Something else…";
                }
            }
            catch (SqlException e)
            {
                System.Diagnostics.Debug.WriteLine("SqlException: " + e.Message);
                return "Ooopss";
            }
            finally
            {
                connection.Close();
            }
        }
    }

I had use breakpoints and debugged in those lines, the result in the variable is Null, but if I hardcode the value or pass the string directly to the SqlConnection instance it also gives me System.NullReferenceException: Object reference not set to an instance of an object.

Please help.

Try hard-coding the connection string, and if that works you can simplify the question, as it's then got nothing to do with SQL Server or SSIS. And BTW you're doing it right. The other way you wondered hosts the SSIS run-time locally, which is not what you want. This runs using the SSIS Integration Catalog. — David Browne - Microsoft, Aug 23 '19 at 21:45
@DavidBrowne-Microsoft Thanks! I already did as you suggest (mentioned in the last paragraph) I hardcoded the Connection String in the SqlConnection instance and get the Null value in that (SqlConnection) object. — AjFmO, Aug 23 '19 at 22:14
@AdrianFlores Your connection string seems incorrect when you are hosting to your IIS. Try removing the localhost and adding the path of the server IP with relevant database information. I always test my database connections on SSMS first before deploying my application. — Rahul Sharma, Aug 24 '19 at 08:18
@RahulSharma that was my assumption, issue with the connection string, looking at [this](https://stackoverflow.com/questions/25809987/correct-connection-string-for-accessing-remote-database-server#answer-25810281) now I guess that I need to find a way to connect with integrated authentication using impersonation or just use this other [method](https://learn.microsoft.com/en-us/sql/integration-services/run-manage-packages-programmatically/loading-and-running-a-remote-package-programmatically?view=sql-server-2017) — AjFmO, Aug 24 '19 at 22:41
@DavidBrowne-Microsoft would like to know your opinion on this, Thank you all — AjFmO, Aug 24 '19 at 22:41
Having wrong in your connection string won't cause a NullReferenceException. Only failing to find any connection string in your config file might. That's why you should hard-code the connection string instead of retrieving it from your config file to troubleshoot. — David Browne - Microsoft, Aug 25 '19 at 16:37
maybe you can try use 127.0.0.1, or you SQL server is express, and also try to use sa account to connect with SQL. — BeiBei ZHU, Aug 26 '19 at 02:24

score 0 · Accepted Answer · answered Oct 07 '19 at 21:33

I just got it solved, indeed this was an issue of Impersonation while debugging the app it was running in the current session context, I'm still not sure why it was not retrieving or executing those lines since I did some test with simpler queries and worked fine.

Thanks to this answer How do you do Impersonation in .NET? I was able to solve the problem using Impersonation via web.config

<system.web>
    <authentication mode="Windows"/>
    <identity impersonate="true" userName="xxxxx" password="xxxxx"/>
</system.web>

Consider to set up Authentication on IIS Manager, refer to Implement Impersonation in ASP.NET

How to fix ‘Object reference not set to an instance of an object’ error in C# Web Service SSIS - Impersonation

1 Answers1