Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    10

    Unhappy Unanswered: Calling a DTS Package from an ASP

    I have created a DTS Package which contains a simple Actixe X Script.
    When I run the package from the DB Server, it executes smoothly.

    I have created an ASP Page to kick off the Package, but when I try to run it thru the ASP, It fails.
    After searching on the internet, I am almost certain that it is due to conflicting username/password (but maybe I am wrong)

    Can someone tell me the steps to go about setting the correct username passwords- on my ASP, on the DTS PAckage, ON the SQL Server Agent ..and anywhere else...to be able to call my DTS PAckage successfully?

    Here are the details->
    ASP on the webser :
    <%@Language=VBScript %>
    <!--METADATA TYPE="dtspkg.dll" NAME="Microsoft DTSPackage Object Library" UUID="{DB18F95E-6AF8-11D5-8F50-0090278A2F8E}" version="2.0"-->

    <% ' 207
    Option Explicit
    Response.Buffer = False
    %>
    <html>
    <head>
    <title>SQLDTS.com: ExecASP</title>
    </head>
    <body>

    <%
    Const DTSSQLStgFlag_Default = 0
    Const DTSStepExecResult_Failure = 1

    Dim oPkg, oStep, sErr, bSuccess

    Dim sServer, iSecurity, sUID, sPWD, sPkgName, sPkgPWD
    ' Load the Package
    Set oPkg = Server.CreateObject("DTS.Package")

    oPkg.LoadFromSQLServer "serverName","username for server","password for server",DTSSQLStgFlag_Default,"PackagePassword","" ,"","Test"
    ' Execute the Package
    oPkg.Execute

    bSuccess = True

    ' Report Step status
    For Each oStep In oPkg.Steps
    sErr = sErr & "<p> Step [" & oStep.Name & "] "
    If oStep.ExecutionResult = DTSStepExecResult_Failure Then
    sErr = sErr & " failed<br>"
    bSuccess = False
    Else
    sErr = sErr & " succeeded<br>"
    End If
    sErr = sErr & "Task """ & _
    oPkg.Tasks(oStep.TaskName).Description & """</p>"
    Next

    If bSuccess Then
    sErr = sErr & "<p>Package [" & oPkg.Name & "] succeeded</p>"
    Else
    sErr = sErr & "<p>Package [" & oPkg.Name & "] failed</p>"
    End If


    ' Clean Up
    oPkg.UnInitialize
    Set oStep = Nothing
    Set oPkg = Nothing

    Response.Write sErr
    Response.Write "<p>Done</p>"

    %>
    </body>
    </html>


    I am confused between username/Pswd for DB server, Sql Server Agent, DTS Package.

    Any help will be greatly appreciated.

    Thanks!!

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Okie,.. you are getting a problem in the execution because you are running it in the context of your IIS user I suspect...

    Rather then doing what you have said above, I have found that the best way to do this sort of thing is to actually create a task in the sql server agent to run the dts package... you can then do a simple ado/stored proc call to start the job running...

    You can then set the job to run in what ever user context is needed....

    If you need to do it the way you have meantioned then you need to ensure that your IIS user has the permissions needed.

  3. #3
    Join Date
    Mar 2004
    Posts
    10
    I am sorry. Being new to the Web environment, I am not sure by what you mean.

    Do you mean I should create a job which will call my DTS Package on the Database server?
    If yes, can you tell me how to do this. Because when i create a new job, it asks me what type of job it is- TSQL, Replication Snapshot etc..
    What should I enter in the steps to call the DTS?

    Thanks again!


    Originally posted by rokslide
    Okie,.. you are getting a problem in the execution because you are running it in the context of your IIS user I suspect...

    Rather then doing what you have said above, I have found that the best way to do this sort of thing is to actually create a task in the sql server agent to run the dts package... you can then do a simple ado/stored proc call to start the job running...

    You can then set the job to run in what ever user context is needed....

    If you need to do it the way you have meantioned then you need to ensure that your IIS user has the permissions needed.

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    The easiest way to understand how the jobs to run a dts package work is to right click on a dts package and click "create schedule" (I think that is what it is called).

    Go through the wizard and have a look at the package it creates. Basically it uses the DTSRun utility program. Have a look in the BOL and it will list all the parameters...

  5. #5
    Join Date
    Mar 2004
    Posts
    10
    What my requirement is :
    Not to schedule the package to run at a spcified time, but to make it run through a web interface (Say an ASP).
    If I schedule the DTS Package, I cannot do the above.



    Another option would be to create a stored proc, and call the DTS package in the SP. And call the SP in my ASP.

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    You create the job without a schedule. I was just saying you can use the "schedule package" function to have a look at how the set up of the job works.

    You can then call your job when you want it to start. You don't need to have the job automatically execute on a schedule, it's just an example.

  7. #7
    Join Date
    Mar 2004
    Posts
    10
    Ok here are the details and what I understand till now.
    Let me know if I am going at a tangent.

    I create a DTS Package on the database server. The owner of the package is showing as my NT login(say Domain1/User1) as I have created the package from my machine, logging on to the DB server using Remote login.

    The default service startup Account setting of my SQL Server Agent on the DB Machine is say Domain1/User2. At the same time the SQL Server Connection is using Windows Authentication( the connection tab on the SQL Server Agent Properties)

    I create a job in the SQL Server Agent, the owner here is User1 (My NT login). In the step option, what do I write? How do I call the DTS Package there? If you could provide the steps that would be great!

    Also Now I need to call from my ASP, the job. How do I do this?
    Any articles/sample code?


    What users need what permissions & where?
    Who needs sysadmin roles etc? This is most confusing.

    Thanks. Appreciate your help & time.

  8. #8
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    okie the job has one step which is an Operating System Command (CmdExec). The comand you are using will be DTSRun which the BOL say the following about....
    The dtsrun utility executes a package created using Data Transformation Services (DTS). The DTS package can be stored in the Microsoft® SQL Server™ msdb database, a COM-structured storage file, or SQL Server Meta Data Services.

    Syntax
    dtsrun
    [/?] |
    [
    [
    /[~]S server_name[\instance_name]
    { {/[~]U user_name [/[~]P password]} | /E }
    ]
    {
    {/[~]N package_name }
    | {/[~]G package_guid_string}
    | {/[~]V package_version_guid_string}
    }
    [/[~]M package_password]
    [/[~]F filename]
    [/[~]R repository_database_name]
    [/A global_variable_name:typeid=value]
    [/L log_file_name]
    [/W NT_event_log_completion_status]
    [/Z] [/!X] [/!D] [/!Y] [/!C]
    ]
    To call the job and get it running you use the sp_start_job stored proc which the BOL say the following about...
    Instructs SQL Server Agent to execute a job immediately.

    Syntax
    sp_start_job [@job_name =] 'job_name' | [@job_id =] job_id
    [,[@error_flag =] error_flag]
    [,[@server_name =] 'server_name']
    [,[@step_name =] 'step_name']
    [,[@output_flag =] output_flag]
    The user that connects to your asp page via ado needs permissions to execute this stored procedure. The job should execute your dts pacakge in the context of the Sql Server Agent which should have all the permissions you need.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •