SSIS Package not wanting to fetch metadata of temporary table

by

Using WITH RESULT SETS to explicitly define the metadata will allow SSIS to skip the sp_describe_first_result_set step and use the metadata that you define. The upside is that you can use this to get SSIS to execute SQL that contains a temporary table (for me, that performance helped a lot); the downside is, you have to manually maintain and update this if anything changes.

Query sample (stored procedure:)

    EXEC ('dbo.MyStoredProcedure')
    WITH RESULT SETS
      (
        (
            MyIntegerColumn INT NOT NULL,
            MyTextColumn VARCHAR(50) NULL,
            MyOtherColumn BIT NULL
        )
      )

Query sample (simple SQL:)

EXEC ('
    CREATE TABLE #a 
      (
        MyIntegerColumn INT NOT NULL,
        MyTextColumn VARCHAR(50) NULL,
        MyOtherColumn BIT NULL
      ) 
    INSERT INTO #a 
      (
        MyIntegerColumn,
        MyTextColumn,
        MyOtherColumn
      )
    SELECT 
        1 AS MyIntegerColumn,
        ''x'' AS MyTextColumn,
        0 AS MyOtherColumn

    SELECT MyIntegerColumn, MyTextColumn, MyOtherColumn
    FROM #a')

WITH RESULT SETS
    (
        (
            MyIntegerColumn INT NOT NULL
           ,MyTextColumn VARCHAR(50) NULL
           ,MyOtherColumn BIT NULL
        )
    )

More Related Contents:

  1. Automate Version number Retrieval from .Dtsx files
  2. Query a database based on result of query from another database
  3. SSIS Source Format Implicit Conversion for Datetime
  4. How to Map Input and Output Columns dynamically in SSIS?
  5. How do I create a step in my SQL Server Agent Job which will run my SSIS package?
  6. convert Excel Date Serial Number to Regular Date
  7. Connecting to Oracle Database using Sql Server Integration Services
  8. I dont know sql syntax for my table [closed]
  9. Making a dynamic pivot table but without using the fiddle
  10. Get top 1 row of each group
  11. How to return only the Date from a SQL Server DateTime datatype
  12. Convert Datetime column from UTC to local time in select statement
  13. Why is SQL Server losing a millisecond?
  14. Find a string by searching all tables in SQL Server
  15. How do I split a delimited string so I can access individual items?
  16. Common Table Expression, why semicolon?
  17. T-SQL split string based on delimiter
  18. Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?
  19. Why use Select Top 100 Percent?
  20. How do I find duplicates across multiple columns?
  21. Calculate business hours between two dates
  22. SQL Server silently truncates varchar’s in stored procedures
  23. How to export image field to file?
  24. SQL Server 2005 ROW_NUMBER() without ORDER BY
  25. Move SQL data from one table to another
  26. Stored procedure that exports data into csv files only exports to one file
  27. Get Start and End Date from Week Number SQL Server
  28. Nested select statement in SQL Server
  29. Group consecutive rows of same value using time spans
  30. SQL add filter only if a variable is not null

Leave a Comment