SQL Server Performance

How to call a SSSIS package from a stored procedure

Discussion in 'SQL Server 2008 Integration Services' started by Thanda, Aug 15, 2009.

  1. Thanda New Member

    Hi All,
    I'm trying to migrate a dts package to MS SSIS 2008, the package is called dynamically from a stored procedure, which passes all the variables needed by the package (i.e, servername, importfilename and variables need within the package). In the DTS package, the package properties are configured within an ActiveX script(Function Main()
    Dim package
    Dim importTextFile
    set package=DTSGlobalVariables.Parent
    set importTextFile=package.Connections("Connection1")
    importTextFile.DataSource=DTSGlobalVariables("Import_Filename")
    set importSQLServer=package.Connections("Connection2")
    importSQLServer.DataSource=DTSGlobalVariables("ServerName")
    importSQLServer.Catalog=DTSGlobalVariables("DatabaseName")
    Main = DTSTaskExecResult_Success
    End Function
    )
    I have tried to recreate the package in SSIS, firstly, there's an error on the activeX task, 2ndly i am not sure how i will call this package from a stored procedure(this is the code i used when calling the DTS package from a stored procedure:(
    declare @objPackage int
    declare @rc int
    declare @ServerName varchar(128)
    declare @DatabaseName varchar(128)
    declare @FileName varchar(128)
    set nocount on
    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("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_C'
    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
    ).
    Please help me set this package in SSIS 2008, i'm loosing my mind here.
  2. MohammedU New Member

Share This Page