Executing a SSIS package from a Stored Procedure

  • Hi Philip, Please assist

    I need to call a SSIS package from a sp which is triggered from a ASP.Net app. THe SSIS has variables which need to be passed from the app via the stored procedure to the package. do you know i can do this. I had this working on SQL 2000 see my code below:

    SELECT @ServerName = @@servername,

    @DatabaseName = DB_NAME()

    SELECT @Import_Enabled,

    @Import_Filename,

    @Import_Package_Name,

    @ServerName,

    @DatabaseName

    exec sp_OACreate 'DTS.Package', @objPackage output

    select 'obj', @objPackage

    exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null,

    @ServerName = @ServerName, @Flags = 256, @PackageName = @Import_Package_Name

    if @rc 0

    BEGIN

    select 'Package Create'

    exec sp_displaypkgerrors

    EXEC @hr = sp_OAGetErrorInfo @objPackage, @source OUT, @description OUT

    select @source, @description

    set @output=@rc

    RETURN

    END

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

    if @rc 0

    BEGIN

    select 'srvname'

    set @output=@rc

    RETURN

    END

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

    if @rc 0

    BEGIN

    select 'Loyalty_Mandatory'

    set @output=@rc

    RETURN

    END

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

    if @rc 0

    BEGIN

    select 'ID_Bulk_Import_Batch'

    set @output=@rc

    RETURN

    END

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

    if @rc 0

    BEGIN

    select 'Received_Date'

    set @output=@rc

    RETURN

    END

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

    if @rc 0

    BEGIN

    select 'Created_By'

    set @output=@rc

    RETURN

    END

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

    if @rc 0

    BEGIN

    select 'dbname'

    set @output=@rc

    RETURN

    END

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

    if @rc 0

    BEGIN

    select 'Filename'

    set @output=@rc

    RETURN

    END

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

    if @rc 0

    BEGIN

    select 'ID_Seg'

    set @output=@rc

    RETURN

    END

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

    if @rc 0

    BEGIN

    select 'ID_C'

    set @output=@rc

    RETURN

    END

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

    if @rc 0

    BEGIN

    select 'ID_P'

    set @output=@rc

    RETURN

    END

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

    if @rc 0

    BEGIN

    select 'ID_RT'

    set @output=@rc

    RETURN

    END

    exec @rc = sp_OAMethod @objPackage, 'Execute'

    if @rc 0

    BEGIN

    set @output=@rc

    RETURN

    END

    exec @rc = sp_OADestroy @objPackage

    if @rc 0

    BEGIN

    set @output=@rc

    RETURN

    END

    SET @output=0

    Your help will be highly appreciated

  • Hi All, Please assist

    I need to call an SSIS package from a sp which is triggered from a ASP.Net app. THe SSIS has variables which need to be passed from the app via the stored procedure to the package. do you know i can do this. I had this working on SQL 2000 see my code below:

    SELECT @ServerName = @@servername,

    @DatabaseName = DB_NAME()

    SELECT @Import_Enabled,

    @Import_Filename,

    @Import_Package_Name,

    @ServerName,

    @DatabaseName

    exec sp_OACreate 'DTS.Package', @objPackage output

    select 'obj', @objPackage

    exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null,

    @ServerName = @ServerName, @Flags = 256, @PackageName = @Import_Package_Name

    if @rc 0

    BEGIN

    select 'Package Create'

    exec sp_displaypkgerrors

    EXEC @hr = sp_OAGetErrorInfo @objPackage, @source OUT, @description OUT

    select @source, @description

    set @output=@rc

    RETURN

    END

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

    if @rc 0

    BEGIN

    select 'srvname'

    set @output=@rc

    RETURN

    END

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

    if @rc 0

    BEGIN

    select 'Loyalty_Mandatory'

    set @output=@rc

    RETURN

    END

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

    if @rc 0

    BEGIN

    select 'ID_Bulk_Import_Batch'

    set @output=@rc

    RETURN

    END

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

    if @rc 0

    BEGIN

    select 'Received_Date'

    set @output=@rc

    RETURN

    END

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

    if @rc 0

    BEGIN

    select 'Created_By'

    set @output=@rc

    RETURN

    END

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

    if @rc 0

    BEGIN

    select 'dbname'

    set @output=@rc

    RETURN

    END

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

    if @rc 0

    BEGIN

    select 'Filename'

    set @output=@rc

    RETURN

    END

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

    if @rc 0

    BEGIN

    select 'ID_Seg'

    set @output=@rc

    RETURN

    END

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

    if @rc 0

    BEGIN

    select 'ID_C'

    set @output=@rc

    RETURN

    END

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

    if @rc 0

    BEGIN

    select 'ID_P'

    set @output=@rc

    RETURN

    END

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

    if @rc 0

    BEGIN

    select 'ID_RT'

    set @output=@rc

    RETURN

    END

    exec @rc = sp_OAMethod @objPackage, 'Execute'

    if @rc 0

    BEGIN

    set @output=@rc

    RETURN

    END

    exec @rc = sp_OADestroy @objPackage

    if @rc 0

    BEGIN

    set @output=@rc

    RETURN

    END

    SET @output=0

    Your help will be highly appreciated

  • HI I'm using this simple code and it works fine for me. Been using it for more than 2 years and never had a problem.

    Good Luck.-

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE PROCEDURE [dbo].[SP_RunDTSX]

    @DTSX as char(50)

    AS

    DECLARE @returncode INT

    if @DTSX='ANY OPTION VALUE THAT CAN BE USED AS A PARAMETER TO RUN ANY DTSX'

    BEGIN

    EXEC @returncode = master..xp_cmdshell 'dtexec /Dts \MSDB\FOLDERNAME_WHERE_DTSX_IS_STORED\DTSX_NAME_TO_RUN'

    END

  • Hi Hugo,

    Thanks for your response, The sp_cmdshell seem to be the best way to do this but the problem is tht the policy in our company wont let us use it for security reason, do you have an alternative??

    I'm trying to set up the package configuration(SQL server one) which create a table on a database with all the configuration, so i'll try and update the table before calling the job which will execute the ssis package, not sure if this is going to work.

    If you have other suggestion please let me know.

  • Hi, I'm experiencing the same problem, can you please share your solution?

    Thanks!

Viewing 5 posts - 16 through 19 (of 19 total)

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