Executing DTS COM

  • cherii

    SSC Enthusiast

    Points: 109

    Using VB code generated from scriptpkg, I have created an ActiveX DLL which imports a given data source into a SQL 7.0 table. The Threading Model of the VB project is set to "Single Threaded" and the ExecuteOnMainThread properties (one for the Source Connection, and one for the Destination connection) within the DTS scripting is set to TRUE. The Project contains just one Class, and has the following Properties:

    DataBindingBehavior=0-vbNone DataSourceBehavior=0-vbNone

    Instancing=5-MultiUse

    MTSTransactionMode=0-NotAnMTSObject Persistable=0-NotPersistable

    When I create and execute the DLL object from a VB Standard.EXE the package works perfectly. If I first run the dll from VB, the execution of the package from the ASP page succeeds. If I do not first run the package from VB, the execution from the ASP page fails with the following:

    The "Copy Data from [SourceTable] to [SISImport].[dbo]. [DestinationTable] Step" step in the package "ImportTable" failed.

    ErrorCode: -2147217843

    Source: Microsoft OLE DB Provider for SQL Server Description: Login failed for user '<COMPUTERNAME>\IWAM_<COMPUTERNAME>'.

    HelpFile: HelpContext: 0 IDofInterfaceWithError: {0C733A8B-2A1C-11CE-ADE5- 00AA0044773D}

    The ASP page is being run locally, using Personal Web Server. SQL Server 7.0 is running locally as well.

    Any help would be greatly appreciated. Thank you!

  • Andy Warren

    SSC Guru

    Points: 119694

    I suspect this is a DCOMCFNG problem. I've run into a couple similar (but not DTS) situations recently that have me scatching my head. Been doing some reading, but haven't found anything really good. I think the key is in the login failure - its running under a different account then when you run the exe I bet?

    Andy

  • K. Brian Kelley

    SSC Guru

    Points: 114486

    Does the following user:

    <COMPUTERNAME>\IWAM_<COMPUTERNAME>

    Have access to SQL Server?

    If not, that login needs to, and have access to the database with the appropriate permissions. Unless you specify otherwise, when you run under the context of VB, you are running under your account. However, IIS/PWS is going to run under the context of its own service account unless you configure that particular web application to run under a different user context.

    K. Brian Kelley

    bkelley@agfirst.com

    K. Brian Kelley
    @kbriankelley

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720436

    bkelley is correct. When you run this under the ASP page, it runs under the context of the account running personal web server, which is usually iwam_<computername). Set this account up in SQL Server with the proper permissions. You should be able to log into NT/2000 with this account and then check your connection to SQL using windows Authentication.

    Steve Jones

    steve@dkranch.net

  • cherii

    SSC Enthusiast

    Points: 109

    bkelley & Steve,

    Thank you both SO much. I gave user:

    <COMPUTERNAME>\IWAM_<COMPUTERNAME>

    the appropriate permissions to SQL Server, and it worked like a charm!!!

    With great appreciation,

    cherii

  • K. Brian Kelley

    SSC Guru

    Points: 114486

    If you separate your web application and SQL Server onto different systems, you'll most likely need to go to a domain account. In that case, you can configure your web application to run under the context of the domain user account instead of <computer name>\IWAM_<computer name>. The reason this is important is that SQL Server will not be able to validate the "domain" <computer name> unless it it the <computer name> of the SQL Server box itself.

    K. Brian Kelley

    bkelley@agfirst.com

    K. Brian Kelley
    @kbriankelley

  • cherii

    SSC Enthusiast

    Points: 109

    bkelley,

    I may ultimately be placing my application onto a different system from SQL Server, so I really appreciate that extra tip.

    Thanks again!

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply