Wednesday, October 14, 2009

Running SSIS packages from a web page

I need to run a simple aspx page on a SQL server to run an SSIS package so that users could kick off the process without having access to the SQL admin tools.

The following C# class in an asp.net page runs brilliantly on the SQL 2008 server

static void CallSSIS()
{
string pkgLocation = @"E:\VS Projects\\Sandbox.dtsx";
Application ssisApp = new Application();
Package ssisPackage = ssisApp.LoadPackage(pkgLocation, null);
DTSExecResult ssisPackageResults = ssisPackage.Execute();
}


The only major problem I had was publishing the web to a virgin Windows 2008 with IIS7 machine. When I browsed to the page it gave the following error "This configuration section cannot be used at this path".

After hours of head scratching and missleading web articles, it turned out that the ASP.NET is not installed as a ROLE in by default.

Server Manager -> Roles -> (scroll to Web Server (IIS)) -> Add Role Services -> ASP.NET

Volia!

No comments: