none
usage of Temp tables in SSIS 2012 RRS feed

  • Question

  • Hello,

    We have many SSIS packages (2008 R2) which imports data to a temp table and process it from there. 

    We are upgrading to SQL server 2012 and facing the issue with temp table as working table and our ssis packages fail in 2012. While investigating found that SQL Server 2012 deprecates FMTONLY option and instead uses sp_describe_first_result_set , which does not support using of temp tables as import table.  SSIS works fine in our workstations but not in the DEV box.  With SQL 2012, I can execute from my workstation, which has (11.0.2100.60) where as DEV server has SQL Server version 11.0.3000.0 

    Also when I ran profile with that of the DEV box, it gives two different statements 

    from workstation (11.0.2100.60)

     CREATE TABLE #temp (
    Id varchar(255) NULL,
    Name varchar(255) NULL )
    
    go
    declare @p1 int
    set @p1=NULL
    declare @p3 int
    set @p3=229378
    declare @p4 int
    set @p4=294916
    declare @p5 int
    set @p5=NULL
    exec sp_cursoropen @p1 output,N'select * from #temp',@p3 output,@p4 output,@p5 output
    select @p1, @p3, @p4, @p5
    go
    


    it works fine

    But with the DEV server (version 11.0.3000.0), it executes the below sql and it fails to get the meta data

     CREATE TABLE #temp (
    Id varchar(255) NULL,
    Name varchar(255) NULL )
    
    exec [sys].sp_describe_first_result_set N'select * from [dbo].[#temp]'

    On checking the assembly difference between the versions, I could only see Microsoft.SqlServer.ManagedDTS.dll being 11.0.3000.0, which I replace by 11.0.2100.60 version. but still getting the same result. 

    The other different I found is with ,Net framework libraries.

    Could you advise whats the assembly causing this issue between our workstation and DEV server  i.e 11.0.2100.60 and 11.0.3000.0 

    Many thanks

    • Moved by Sofiya Li Monday, March 31, 2014 6:34 AM the right forums
    Friday, March 28, 2014 12:34 PM

Answers

  • Have found a workaround when using the temp tables for loading the data from feed.

    In the OLE Db/ADO Destination of the Data flow task, change the AccessMode to SQL Command instead of OpenRowSet and specify the  SQL Command to "select * from #temp" 

    If you set the accessmode to openrowset /with variable, you have to mention the table/view name , in that case SSIS 2012 , uses sp_describe_first_result_set to get the meta data assuming it as a table. 

    when you select SQL Command, it is expecting it to be SP, so it still uses the old way of retrieving the data using a cursor/ set fmtonly. 

    • Marked as answer by vonbalaji Tuesday, April 1, 2014 2:24 PM
    Tuesday, April 1, 2014 2:15 PM

All replies

  • Hi vonbalaji,

    Where do you get the above scripts? It should be useful if you post the error message.

    Regards,


    Regards, Leo

    Monday, March 31, 2014 1:21 PM
  • Scripts are taken from profiler.

    The error message is 

    The metadata could not be determined because statement 'Select * from #branchscan' uses a temp table. 

    I could see the work around saying use of table variable and global temp tables.  We are having around 100+ ssis packages which uses temp table for loading the data from a flat file and respective SP to process the data from the temp table.  above error is thrown during the pre-execute phase of the OLE db Destination, when trying to get the meta data of the table. 

    At this stage, it would be difficult for us to change the logic to global temp or TVP

    Thanks



    Monday, March 31, 2014 4:44 PM
  • Have found a workaround when using the temp tables for loading the data from feed.

    In the OLE Db/ADO Destination of the Data flow task, change the AccessMode to SQL Command instead of OpenRowSet and specify the  SQL Command to "select * from #temp" 

    If you set the accessmode to openrowset /with variable, you have to mention the table/view name , in that case SSIS 2012 , uses sp_describe_first_result_set to get the meta data assuming it as a table. 

    when you select SQL Command, it is expecting it to be SP, so it still uses the old way of retrieving the data using a cursor/ set fmtonly. 

    • Marked as answer by vonbalaji Tuesday, April 1, 2014 2:24 PM
    Tuesday, April 1, 2014 2:15 PM