ASP.NET failing to load SSIS package: Value does not fall within the expected range

Question

I have a ASP.NET Website which will reference a number of SSIS packages which influence an SQL database.

The first of these packages will clear all rows in a table which match an ASP NET session variable 'ID'.

The site seems to be pulling from the same SQL database okay, it just seems to be the SSIS packages which are having issues.

When I select load a package using the following syntax I get the following error:

    Package deletePkg = app.LoadPackage(deletePackageLocation, null);

Value does not fall within the expected range

I have quadruple checked the file path is correct.

The connection manager for this particular package seems happy when testing a connection, and is using the same configuration used multiple other times in the website. For whatever reason text and SQL logging do not seem to be working despite working in the SSIS development environment. (ID = ?)

After looking at numerous other discussions it has become clear that this is a pretty vague error message that can refer to any number of issues. None of which I have found have solutions fitting for my situation.

Try running a profiler trace when the package errors to see what query is being passed from the package to the database. — Tab Alleman
Did you choose the correct Data Type for the parameter in your SSIS ExecuteSQL Task? — Tim Eeckhaut
My next guess would be null values in the data, or a difference in column types (null/notnull/length) between the prod en dev databases. — Tim Eeckhaut
Could you add logging to the package and report back the OnError and OnWarning events? It would be helpful to understand which component in the package is returning this error. — Mark Wojciechowicz
Yes, I am wondering if SSIS service account does not have permission to the package. Is the package local to the server or stored on a file share? That you can debug your application and execute the package successfully and only get this error when you run it on the server, it seems to point to a permissions issue. Or there is something with the value of the deletePackageLocation variable i.e. pointing to a location that does not have the package on the server. But you said you checked that already. — Mark Wojciechowicz

CAJE CAJE · Accepted Answer · 2015-01-15T15:47:47

The solution was an unexpected issue.

deletePackageLocation was a class variable that was set on Page_load.

Although the variable was successfully set in a if(!IsPostBack) clause it was clearing before package loading. Once I defined the variable immediately before package loading it worked.

Very unusual solution, but it was what was preventing package loading at the end of the day.

ASP.NET failing to load SSIS package: Value does not fall within the expected range

1 Answers