Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Location
    Central Oregon
    Posts
    5

    Question Unanswered: Stored Proc to run a DTS package...???

    Good day all!

    I have a DTS package that consists of 2 tasks, one empties a table(1) to receive data from a transformation task(2)...

    I found 'code' for a stored procedure to run a DTS package.. problem is that it is only running the first task... I need it to run the whole she-bang!

    Anyone know how to do this?

    Here is the stored proc:
    /************************************************** **************************************/
    /* Filename: spExecutePKG.sql */
    /* Description: Execute DTS Packages via sp_OAxxxx */
    /* Created: Darren Green 200006013 */
    /************************************************** **************************************/

    if exists (select * from sysobjects where id = object_id(N'[dbo].[spExecutePKG]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[spExecutePKG]
    GO

    CREATE PROC spExecutePKG
    @Server varchar(255),
    @PkgName varchar(255), -- Package Name (Defaults to most recent version)
    @ServerPWD varchar(255) = Null, -- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
    @IntSecurity bit = 0, -- 0 = SQL Server Security, 1 = Integrated Security
    @PkgPWD varchar(255) = '' -- Package Password
    AS
    SET NOCOUNT ON
    /*
    Return Values
    - 0 Successfull execution of Package
    - 1 OLE Error
    - 9 Failure of Package
    */
    DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000)

    -- Create a Pkg Object
    EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT
    IF @hr <> 0
    BEGIN
    PRINT '*** Create Package object failed'
    EXEC sp_displayoaerrorinfo @oPKG, @hr
    RETURN 1
    END

    -- Evaluate Security and Build LoadFromSQLServer Statement
    IF @IntSecurity = 0
    SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "' + SUSER_SNAME() + '", "' + @ServerPWD + '", 0, "' + @PkgPWD + '", , , "' + @PkgName + '")'
    ELSE
    SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "", "", 256, "' + @PkgPWD + '", , , "' + @PkgName + '")'

    EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL

    IF @hr <> 0
    BEGIN
    PRINT '*** LoadFromSQLServer failed'
    EXEC sp_displayoaerrorinfo @oPKG , @hr
    RETURN 1
    END

    -- Execute Pkg
    EXEC @hr = sp_OAMethod @oPKG, 'Execute'
    IF @hr <> 0
    BEGIN
    PRINT '*** Execute failed'
    EXEC sp_displayoaerrorinfo @oPKG , @hr
    RETURN 1
    END

    -- Check Pkg Errors
    EXEC @ret=spDisplayPkgErrors @oPKG

    -- Unitialize the Pkg
    EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize'
    IF @hr <> 0
    BEGIN
    PRINT '*** UnInitialize failed'
    EXEC sp_displayoaerrorinfo @oPKG , @hr
    RETURN 1
    END

    -- Clean Up
    EXEC @hr = sp_OADestroy @oPKG
    IF @hr <> 0
    BEGIN
    EXEC sp_displayoaerrorinfo @oPKG , @hr
    RETURN 1
    END

    RETURN @ret
    GO

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you run the DTS task manually, does it produce the results that you wanted? I'd suspect that something was mildly corn-fused within the DTS package until I'd proved that it worked Ok.

    -PatP

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    barring a problem with the DTS like what Pat said, is there an error returned. I am a thinkin' there might be a security\permissions issue here. From where are you executing the SP? DTS can be a little squirlly about security context.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74

    Another way of doing it ....

    I assume you mean the SQL Server DTS-job when you refer to DTS-jobs, these abbreviations make me uncertain every time, because my db-experience is limited .... anyway, here goes ....

    If you wanna run a DTS-job from within a SP you could do the following (not neccesarily a good thing, but hey, it works....)

    Rightclick on the job and choos schedule, name the job something you remember (yes, I know, this isn't what you wanna do, but hang on, I'll explain...).
    When this is done, a scheduled job is created. If you examine the properties of this job, you'll see that in the "Steps" section, Enterprise Manager has created a command that it says is a "operating system command" that looks something like this :

    DTSRun /~Z0x3C2C601BAB76E82B9FFAF1EB61FE0D4BD97FEE2AB010B6 B36E87316756FEB77465398A0EBE8EF3E04295901AD8FB73C2 E7B53021F2C2E152BAA82616F0E8F79BC78F943BF3F93A0B9B C615EE6A0B4AD89EFF847823254C35E01B40D4DEBF983686DC E0DE2BBC600F8AECF52E99D026371D5E6D2C0AAD3AE4E77842

    What this means is basically that SQL-Server runs a DOS-command that triggers the DTS-package. Now we all know how our friend xp_cmdshell works, no don't we ...(?) xp_cmdshell is a way for SQL-server to execute DOS commands, which is exactly what we want here. Therefore, your proc could be something like:

    create procedure SomeProcName as

    exec master..xp_cmdshell '<insert your command generated earlier from the job>'


    This has worked for me earlier, so I guess it should work for you as well.
    Just remember that if you are doing any fileaccess or trying to access resources on the network, the access privileges use are those that the account had that the SQL-server service runs on.

    Good luck.

Posting Permissions

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