locked
Asp.net SSIS IIS issue when running package from webapplication RRS feed

  • Question

  • User-1146903841 posted

    Hi

     I want to run a SSIS package, that reads from a flatfile and writes to a SQL2005 DB table, from a webpage created in VS2005.

    Basically I keep running into the same error, no matter what settings I use in IIS or the web.config (impersonation on or off) for the specific webpage.

    The package loads, get's some variables passed and executes. No DTS errors are triggered. I added an eventlistener to pick up events of the package itself.

    Outcome:

    "Microsoft.SqlServer.Dts.Runtime.PackageConnection manager "<STRIKE>*******</STRIKE>"SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

    An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login failed for user ''. The user is not associated with a trusted SQL Server connection.".

    "

    The SSIS package connection manager to SQL2005 is setup to use windows authentication which of course runs fine when ran directly from VS2005 or with dtsexec because it passes my security credentials which have the appropiate rights.

    The webpage uses forms authentication and impersonates properly when told to do so in the web.config. (checked)

    Somehow no security credentials seem to get passed to or through the ssis package when it's ran.  

    I've seen many posts on the subject but can't really find a solution to run an SSIS package from a webpage.

    Is there any way of running an ssis package from a webpage hosted on a webserver running IIS (5.1)?

    At the moment I'm not limited to any setup. I just want to have it working.

     

    Code to load and execute the package:

    Try

    pkgLocation = _

    "*******\package.dtsx"

    pkg = app.LoadPackage(pkgLocation, Nothing)

    pkg.Variables("User::varFileName").Value = ****

    pkg.Variables("User::CID").Value = **********

    pkgResults = pkg.Execute(Nothing, Nothing, EventListener, Nothing, Nothing)

    Catch ex As DtsException

    End Try

    The Eventlistener that captures the failure of the logon attempt of the connection manager inside the package:

    Public Overrides Function OnError(ByVal source As Microsoft.SqlServer.Dts.Runtime.DtsObject, _

    ByVal errorCode As Integer, ByVal subComponent As String, ByVal description As String, _

    ByVal helpFile As String, ByVal helpContext As Integer, _

    ByVal idofInterfaceWithError As String) As Boolean

    Dim message As String = source.ToString & subComponent & description

    Return False

    End Function

    Wednesday, September 26, 2007 6:01 AM

All replies

  • User-1146903841 posted

    Issue resolved by adding an application pool in IIS, setting up it's Identity (tab) to Configurable, adding an account that has sufficient privileges to run dtexec properly. (ASP worker process is ran by this account if I'm correct, which in turn starts dtexec)

    The account used should also be part of the system's IIS_WPG group to be able to start the asp worker process.)

    Found the solution eventually in a post here after some trying etc.

     

     

    Thursday, September 27, 2007 5:35 AM
  • User1060307877 posted

    Thank you Brammieman, it solved my issue.

    Thursday, August 24, 2017 4:52 PM