Running DTS Package from Stored Procedure

  • Hi all,

    I need to run a DTS Package (importing data from Access) from a stored procedure.

    I'm not really familiar with MS SQL Server, so i got no idea about the syntax (or even if it's possible to run DTS packages in that way).

    thx, Andi

  • More than one way to skin a cat..

    You could use sp_OA.... stored procedures to instantiate and execute your DTS package. You could also execute dtsrun by using xp_cmdshell from within your stored procedure. Another way is to schedule your DTS package as a job and execute the job from your stored procedure.

    Cheers.


    Joseph

  • Use xp_cmdshell...it's much easier than using the sp_OA procs...

    ciao ciao


    ciao ciao

  • The Stored Procedure below is what I used to run a DTS package.

    --------------------------------------------

    IF OBJECT_ID('dbo.up_RUN_DTSPACKAGE') IS NOT NULL

    BEGIN

    DROP PROCEDURE dbo.up_RUN_DTSPACKAGE

    IF OBJECT_ID('dbo.up_RUN_DTSPACKAGE') IS NOT NULL

    PRINT '<<< FAILED DROPPING PROCEDURE dbo.up_RUN_DTSPACKAGE >>>'

    ELSE

    PRINT '<<< DROPPED PROCEDURE dbo.up_RUN_DTSPACKAGE >>>'

    END

    go

    CREATE PROC dbo.up_RUN_DTSPACKAGE

    (

    @sPackage varchar(255),

    @ServerInstanceName varchar(255) ='(local)',

    @ImportJobID int = 0,

    @FileNamePath varchar(255) = NULL,

    @errorcode int output,

    @outstr varchar(255) output,

    @outdescription varchar(255) output,

    @UploadCtr int output

    )

    as

    /******************************************************************************

    * Proc Name: up_RUN_DTSPACKAGE

    * File Name: up_RUN_DTSPACKAGE.sql

    * Created: 09/27/2001, SalehW

    * Purpose: Execue a DTS pacake by calling a SP.

    * Modified: 02/05/2002, SalehW, Return the number of rows transferred via UploadCtr

    * captured from the DTS job.

    * 01/31/2002, SalehW, @ServerInstanceName added to SP to accommodate

    * a different name instant.

    * 10/05/2001, SalehW, CollinsK Set the Connection name to a file name

    * with full path via imput paramter @FileNamePath.

    * 10/02/2001,Salehw Added Global variable gvImportID

    * Desc: This procedure can be call by users with the appropriate permission

    * to start the execution of DTS packages.

    * In the DTS package a Global variable with the name gvImportJobID.

    * The value of the gvImportJobID is set to equal to

    * Parameters:

    * INPUT : @sPackage - Name of the DTS package to be executed

    * OUTPUT: @errorcode - error code retuned from the OLE-Automation exteneded

    * stored procedure calls

    * @outstr - descreption of the error code

    * @outdescription - Detailed descreption

    * Note: No need to change the database name in this SP.

    *

    * Test:

    DECLARE @Start_Time datetime, @RtnErr int

    SELECT @Start_Time = GETDATE()

    DECLARE @sPackage varchar(64),

    @ServerInstanceName varchar(255),

    @ImportJobID int,

    @FileNamePath varchar(255),

    @errorcode int,

    @outstr varchar (255),

    @outdescription varchar(255),

    @UploadCtr int

    SELECT @sPackage='zSQL2000_Import_PriceAdminQuoteUpload_FromCSV'

    SELECT @ImportJobID = 802

    SELECT @ServerInstanceName = CONVERT(char(20), SERVERPROPERTY('servername'))

    SELECT @FileNamePath = '\\CORSCMV01\SCMData\PriceAdminQuote\f_salehw_01312002094911.csv'

    EXEC up_RUN_DTSPACKAGE @sPackage, @ServerInstanceName, @ImportJobID,@FileNamePath, @errorcode output, @outstr output, @outdescription output, @UploadCtr

    SELECT @sPackage, @errorcode, @outstr, @outdescription, @UploadCtr

    ------------------------------

    SELECT round(datediff(mi,@Start_Time,GETDATE()) / 1440,0) As 'DD',

    round((datediff(mi,@Start_Time,GETDATE()) % 1440) / 60, 2) AS 'HH',

    round(((datediff(mi,@Start_Time,GETDATE()) % 1440) % 60), 2) AS 'MM',

    datediff(ss,@Start_Time,GETDATE()) AS 'SS',

    datediff(ms,@Start_Time,GETDATE()) AS 'MS'

    ******************************************************************************/

    set nocount on

    declare

    @pkgID int,

    @hr int,

    @STR varchar(255),

    @description varchar(255),

    @property int

    SELECT

    @pkgID = 0,

    @hr = 0,

    @STR = '',

    @description = '',

    @property = 0

    ---------------------------------------

    --Creates an instance of the DTS.Package/OLE object

    exec @errorcode = sp_OACreate 'DTS.Package',

    @pkgID output

    --Obtains OLE Automation error information

    exec sp_OAGetErrorInfo @pkgID,

    @STR OUT,

    @description OUT

    if @errorcode <> 0 GOTO Exit_status

    ---------------------------------------

    --Load the Package from LoadFromSQLServer

    exec @errorcode = sp_OAMethod @pkgID,

    'LoadFromSQLServer',

    null,

    @ServerName = @ServerInstanceName,

    -- @ServerUserName= '',

    -- @ServerPassword= '',

    @Flags = 256,

    @PackageName = @sPackage

    --Obtains OLE Automation error information

    exec sp_OAGetErrorInfo @pkgID,

    @STR OUT,

    @description OUT

    if @errorcode <> 0 GOTO Exit_status

    ---------------------------------------

    /*

    --Get the proparty gvImportJobID

    EXEC @errorcode = sp_OAGetProperty @pkgID, 'GlobalVariables ("gvImportJobID").Value', @property OUT

    IF @errorcode <> 0

    BEGIN

    exec sp_OAGetErrorInfo @pkgID,

    @STR OUT,

    @description OUT

    if @errorcode <> 0 GOTO Exit_status

    END

    select @property AS 'gvJobImportID'

    */

    ---------------------------------------

    exec @errorcode=sp_OASetProperty @pkgID, 'GlobalVariables ("gvUploadCtr").Value', 0

    --Obtains OLE Automation error information

    exec sp_OAGetErrorInfo @pkgID,

    @STR OUT,

    @description OUT

    if @errorcode <> 0 GOTO Exit_status

    ---------------------------------------

    --Set the property gvImportJobID = 1000

    IF @ImportJobID > 0

    BEGIN

    exec @errorcode=sp_OASetProperty @pkgID, 'GlobalVariables ("gvImportJobID").Value', @ImportJobID

    --Obtains OLE Automation error information

    exec sp_OAGetErrorInfo @pkgID,

    @STR OUT,

    @description OUT

    if @errorcode <> 0 GOTO Exit_status

    /* --DEBUG lines

    --Get the proparty gvImportJobID

    EXEC @errorcode = sp_OAGetProperty @pkgID, 'GlobalVariables ("gvImportJobID").Value', @property OUT

    exec sp_OAGetErrorInfo @pkgID,

    @STR OUT,

    @description OUT

    select @property AS 'gvJobImportID'

    if @errorcode <> 0 GOTO Exit_status

    */

    END

    ---------------------------------------

    --Set the Connection 1 property set to the file path and name passed to the SP in @FileNamePath

    IF @FileNamePath IS NOT NULL

    BEGIN

    exec @errorcode=sp_OASetProperty @pkgID, 'Connections ("Connection 1").Datasource', @FileNamePath

    --Obtains OLE Automation error information

    exec sp_OAGetErrorInfo @pkgID,

    @STR OUT,

    @description OUT

    if @errorcode <> 0 GOTO Exit_status

    ---------------------------------------

    /* --DEBUG lines

    EXEC @errorcode = sp_OAGetProperty @pkgID, 'Connections ("Connection 1").Datasource' ,@description OUT

    select @description AS 'Connection name'

    exec sp_OAGetErrorInfo @pkgID,

    @STR OUT,

    @description OUT

    if @errorcode <> 0 GOTO Exit_status

    */

    END

    ---------------------------------------

    ---------------------------------------

    --Execute the Package

    exec @errorcode = sp_OAMethod @pkgID, 'Execute'

    --Obtains OLE Automation error information

    exec sp_OAGetErrorInfo @pkgID,

    @STR OUT,

    @description OUT

    if @errorcode <> 0 GOTO Exit_status

    ---------------------------------------

    --Return the number of rows transferred via the DTS package.

    EXEC @errorcode = sp_OAGetProperty @pkgID, 'GlobalVariables ("gvUploadCtr").Value', @property OUT

    exec sp_OAGetErrorInfo @pkgID,

    @STR OUT,

    @description OUT

    select @UploadCtr = @property

    if @errorcode <> 0 GOTO Exit_status

    ---------------------------------------

    --Delete the Package

    exec @errorcode = sp_OADestroy @pkgID

    --Obtains OLE Automation error information

    exec sp_OAGetErrorInfo @pkgID,

    @STR OUT,

    @description OUT

    ---------------------------------------

    ---------------------------------------

    ---------------------------------------

    Exit_status:

    if @errorcode <> 0

    begin

    select @outstr = 'error: ' + @STR

    select @outdescription = @description

    end

    else

    begin

    select @outstr = 'Completed' --'Success'

    select @outdescription = @sPackage + 'Load completed successfully'

    end

    go

    IF OBJECT_ID('dbo.up_RUN_DTSPACKAGE') IS NOT NULL

    PRINT '<<< CREATED PROCEDURE dbo.up_RUN_DTSPACKAGE >>>'

    ELSE

    PRINT '<<< FAILED CREATING PROCEDURE dbo.up_RUN_DTSPACKAGE >>>'

    go

  • IF scheduling the pack is not possible I like triggering DTS from Stored Procs.

    -- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<DECLARE @objPackage int

    DECLARE @PackageName varchar(128)

    DECLARE @rc int

    DECLARE @ServerName varchar(128)

    DECLARE @DatabaseName varchar(128)

    SET@PackageName = 'DTS_Example'

    SET@ServerName = @@ServerName

    exec sp_OACreate 'DTS.Package', @objPackage output

    exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null, @ServerName = @ServerName, @Flags = 256, @PackageName = @PackageName

    exec @rc = sp_OASetProperty @objPackage,'GlobalVariables("strFileString").value', @FileName

    exec @rc = sp_OASetProperty @objPackage,'GlobalVariables("InfoID").value', @infoID

    exec @rc = sp_OASetProperty @objPackage,'GlobalVariables("GroupID").value', @GroupID

    exec @rc = sp_OAMethod @objPackage, 'Execute'

    exec @rc = sp_OADestroy @objPackage

    -- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

    *Note @FileName,@infoID,@GroupID are local variable inside the Stored Proc

    Hope that helps

Viewing 5 posts - 1 through 4 (of 4 total)

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