none
dts task use SP RRS feed

  • Question

  • I have a DTS package with a data transformation task (data pump). I’d like to source the data with the results of a stored procedure that takes parameters, but DTS won’t preview the result set and can’t define the columns in the data transformation task.

    Has anyone gotten this to work?

    Caveat: The stored procedure uses two temp tables (and cleans them up, of course)
    Saturday, October 20, 2012 8:23 AM

Answers

  • Should be working on SQL Server 2000

    CREATE proc uuu
    as

    declare @packagename varchar(255) --package name, gets most recent version
    declare @userpwd varchar(255) --login pwd
    declare @intsecurity bit --use non-zero to indicate integrated security
    declare @pkgPwd varchar(255) --package password
    declare @hr int
    declare @object int

    set @packagename='uri_test'
    set @userpwd=null
    set @pkgPwd=''
    set @intsecurity=0

    --create a package object 
    EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT 
    if @hr <> 0
    Begin
    EXEC sp_displayoaerrorinfo @object --, @hr
    RETURN
    end
     

    declare @svr varchar(15)
    declare @login varchar(15)
    select @login = SUSER_NAME()
    select @svr = @@servername
    declare @flag int
    select @flag = 256


    EXEC @hr = sp_OAMethod @object, 
    'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login,
     @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd

    exec @hr = sp_OAsetproperty
                           @object,
                          'GlobalVariables ("var1").Value', 4  -----set value GV

        exec @hr = sp_OAsetproperty
                           @object,
                          'GlobalVariables 
    ("FileLocation").Value', 'c:\MyFile.txt'  -----set location GV


    EXEC @hr = sp_OAMethod @object, 'Execute'
    IF @hr <> 0
    BEGIN
       print 'Execute failed'
       EXEC sp_displayoaerrorinfo @object --, @hr
       RETURN
    END


    EXEC @hr = sp_OADestroy @object
    IF @hr <> 0
    BEGIN
        PRINT '***  Destroy Package failed'
        EXEC sp_displayoaerrorinfo @object, @hr
        RETURN
    END
    GO


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    • Marked as answer by Antonic217 Sunday, October 28, 2012 9:13 AM
    Sunday, October 21, 2012 9:50 AM

All replies

  • SP as SOR for SSIS

    The URL above shows how the SP's recordset metadata can be read by the ssis package.


    Regards, Dinesh

    Saturday, October 20, 2012 10:09 PM
    • Use table variables instead of  temporary table inside stored procedure (Instead of "crate table #temptbl ( <columns>....)" use "declare @temptbl table ( <columns>....)" )
    • At the end of the stored procedure select with exact column names instead of select *
    • Write "SET NOCOUNT ON" at the beginning of OLEDB Data Source sql command.

    Let us TRY this |


    My Blog :: http://quest4gen.blogspot.com/

    Sunday, October 21, 2012 3:47 AM
  • Another option is to use "SET FMTONLY OFF".

    It is solving this problem, but at the high cost - stored procedure will be executed by SSIS multiple times instead of just one.
    If there is no side effects of the SP, it is only performance penalty. But if SP have some side effects, multiple executions can be disruptive.

    Also, this feature will be removed in a future version of Microsoft SQL Server.
    Sunday, October 21, 2012 4:22 AM
  • I assume that you were not using SQL server 2012. But I still hope this link will give you some help.

    http://www.msbicoe.com/post/2011/10/15/The-Use-of-Denali-With-Result-Sets-Feature-in-SSIS-Data-Flow-Task.aspx


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCSE: Data Platform | MCITP: BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!

    Sunday, October 21, 2012 8:25 AM
  • Should be working on SQL Server 2000

    CREATE proc uuu
    as

    declare @packagename varchar(255) --package name, gets most recent version
    declare @userpwd varchar(255) --login pwd
    declare @intsecurity bit --use non-zero to indicate integrated security
    declare @pkgPwd varchar(255) --package password
    declare @hr int
    declare @object int

    set @packagename='uri_test'
    set @userpwd=null
    set @pkgPwd=''
    set @intsecurity=0

    --create a package object 
    EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT 
    if @hr <> 0
    Begin
    EXEC sp_displayoaerrorinfo @object --, @hr
    RETURN
    end
     

    declare @svr varchar(15)
    declare @login varchar(15)
    select @login = SUSER_NAME()
    select @svr = @@servername
    declare @flag int
    select @flag = 256


    EXEC @hr = sp_OAMethod @object, 
    'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login,
     @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd

    exec @hr = sp_OAsetproperty
                           @object,
                          'GlobalVariables ("var1").Value', 4  -----set value GV

        exec @hr = sp_OAsetproperty
                           @object,
                          'GlobalVariables 
    ("FileLocation").Value', 'c:\MyFile.txt'  -----set location GV


    EXEC @hr = sp_OAMethod @object, 'Execute'
    IF @hr <> 0
    BEGIN
       print 'Execute failed'
       EXEC sp_displayoaerrorinfo @object --, @hr
       RETURN
    END


    EXEC @hr = sp_OADestroy @object
    IF @hr <> 0
    BEGIN
        PRINT '***  Destroy Package failed'
        EXEC sp_displayoaerrorinfo @object, @hr
        RETURN
    END
    GO


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    • Marked as answer by Antonic217 Sunday, October 28, 2012 9:13 AM
    Sunday, October 21, 2012 9:50 AM