Error executing SSIS package from ASP.NET

Question

we are facing an issue when running an SSIS package from ASP.NET when the web site is deployed. We have the package loaded in an Integration Services server in MSDB. When we try the package from SQL Server Management Studio, it runs properly. When I execute the code from my Visual Studio, the package loaded in the server runs properly. The issue comes when the page is deployed to a server: There are no exceptions caught and no errors logged, but the package does not execute. We just see an error in Windows Event Viewer saying: "Package "" failed.". The ASP.NET page runs impersonation of an account with "sysadmin" access to the SQL Server and also the shares where the source Excel files are.

Here is the code calling the SSIS package:

    public void executePkg(string pkgAddr, string pkgServer, int periodID)
    {
        logger.Info(string.Format("Execution of SSIS Package '{0}' in server '{1}' started.", pkgAddr, pkgServer));
        string pidvar = "User::periodid";
        string pkgLocation;
        Package pkg;
        Application app;
        DTSExecResult pkgResults;
        app = new Application();
        pkgLocation = pkgAddr;
        try
        {
            pkg = (Package)app.LoadFromSqlServer(pkgAddr, pkgServer, null, null, null);
            Variables vars = pkg.Variables;
            int varCount = vars.Count;
            bool varExist = vars.Contains(pidvar);
            pkg.VariableDispenser.LockOneForWrite(pidvar, ref vars);
            vars[pidvar].Value = periodID;
            pkg.Variables[pidvar].Value = periodID;
            vars.Unlock();
            pkgResults = pkg.Execute();
        }
        catch (Exception ex)
        {
            logger.Error(ex.Message);
        }
    }

Any help would be appreciated.

Note: Just a small correction, the ASP.NET is no longer impersonating, instead, the AppPool is running with the service account. The behavior is the same with both scenarios.

Diego · Accepted Answer · 2012-06-08 09:32:52Z

1

Are there differences on the architecture of the development boxes and the server?

Be sure to note that if you develop a package in a 32-bit environment and want to run the package in a 64-bit environment, the connection managers need to be 64-bit compliant. Some connection managers such as Excel work in a 32-bit environment only.

answered Jun 8, 2012 at 9:32

Diego

36.5k21 gold badges94 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JStrahl · Accepted Answer · 2012-06-07 21:26:26Z

0

I am not sure of the exact answer to this problem, however, I have a suggestion on something worth checking. Is the SSIS package installed to the SQL Server DB or to the file system, as each would require different ways to access the package.

http://consultingblogs.emc.com/jamiethomson/archive/2006/02/20/ssis_3a00_-deploy-to-file-system-or-sql-server.aspx

Hope this is the solution.

answered Jun 7, 2012 at 21:26

JStrahl

4841 gold badge8 silver badges19 bronze badges

Comments

EL_Beau · Accepted Answer · 2015-06-11 19:15:06Z

it seems like your main problem is lack of error reporting.

First:

Does your SSIS package have error logging enabled? I turn this on, just for OnError Events. This will create a table in the db called sysssislog and write errors (and any other logs you choose) there.

https://technet.microsoft.com/en-us/library/ms138020%28v=sql.105%29.aspx

Worth noting: When I do this, SSIS creates a table called domain\username.sysssislog instead of dbo.sysssislog. If your package does this, you'll just need to drop the incorrectly named table and create dbo.sysssislog so that the package can write its errors there.

Second:

Since you are using the object model to execute your package, you can capture the errors from your package and do something with them. For debugging, I just pop them up in a message window so I can see what the problem is.

This is how I have done it, but I'm not sure that it's well-oiled:

            Dim errReport As String
            Dim errors As DtsErrors = pkg.Errors

            Dim errItem As Boolean = errors.Contains(0)

            errReport = "No Error"
            If (errItem) Then
                Dim firstEItem As DtsError = errors(0)
                Dim ex As New Exception("The file could not be loaded. Please submit a helpdesk ticket.")
                Throw ex
                errReport = "The following error occurred: " + firstEItem.Description
            End If


            MsgBox(errReport, MsgBoxStyle.OkOnly, pkgResults.ToString)

I hope this helps.

Collectives™ on Stack Overflow

Error executing SSIS package from ASP.NET

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related