SSIS: How to create and use Temp tables

Problem

I’m trying to use a temp table in an SSIS package. It seems like everything is working correctly until I try to query the temp table. What am I doing wrong?

Error message if not configured correctly:

Update – Insert Data Flow:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “Unspecified error”. An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “The metadata could not be determined because statement ‘select * from ‘##TmpMcsConfigurationDeviceHistory86B34BFD041A430E84CCACE78DA336A1” uses a temp table.”.

temp table rules:

  1. temp table creation in the execute sql task works well
  2. temp table, table variable or passing parameter in the data flow task – ole db source return error.
    1. save your query in a string variable as expression and
    2. if you need to pass parameter set the parameter value in the string variable expression
    3. for example:”DECLARE @year int;
      SET @year= ” + (DT_WSTR, 20) @[User::vYear]

      + “; SELECT 1 AS TEST;”

What has worked for me:

Using a temporary table variable has worked for me. All other solutions sometimes work and sometimes does not.

Add some meta-data and the “set nocount on” to the stored procedure with a “short circuited if clause” (if 1=0), and a bogus select statement at the top. I’ve tested with trying to leave the “set nocount on” out and it did not work.

first stage:
SET NOCOUNT ON 
SELECT CONVERT(NVARCHAR(20), 0) AS [Data]
 ,CONVERT(smallint, 1) AS [Year]
 ,CONVERT(NVARCHAR(20), 2) AS [Month]
 ,CONVERT(tinyint, 3) AS [FiscalCalendarMonthNumber]
 ,CONVERT(tinyint, 4) AS [DaysCount];
-- Do real work starting here 
CREATE TABLE ##test 
    ( 
      [id] [int] NULL, 
      [Name] [nchar] ( 10 ) NULL, 
      [SirName] [nchar] ( 10 ) NULL 
    ) 
--insert data into ##test

last stage:
select * from ##test

Solution 1:

**

Another option (kind of a hack, but it works and doesn’t require you to change your use of global temp tables) is to use a SET FMTONLY ON command in front of your actual query to send a fake “First result set” to SSIS with your correct column structure. So you can do something like

SET FMTONLY ON;
SELECT CONVERT(NVARCHAR(20), 0) AS [Data]
 ,CONVERT(smallint, 1) AS [Year]
 ,CONVERT(NVARCHAR(20), 2) AS [Month]
 ,CONVERT(tinyint, 3) AS [FiscalCalendarMonthNumber]
 ,CONVERT(tinyint, 4) AS [DaysCount];
SET FMTONLY OFF;

SELECT * 
FROM ##StarStatus --temptable query
;

Solution 2:
EXEC('SELECT 1 AS [Data]
 ,2 AS [Year]
 ,3 AS [Month]
 ,4 [FiscalCalendarMonthNumber]
 ,5 [DaysCount];')
WITH RESULT SETS
(
 (
 [Data] NVARCHAR(20)
 ,[Year] smallint 
 ,[Month] NVARCHAR(20)
 ,FiscalCalendarMonthNumber tinyint
 ,[DaysCount] tinyint
 )
);


SELECT * 
FROM ##StarStatus
;

When SSIS runs sp_describe_first_result_set, it will return the metadata and column names of your FMTONLY command, and won’t complain about not being able to determine the metadata of your temp table because it won’t even try.

Tips: Do not change the Maximum insert commit size. It stops from using the temp table. Keep other settings default if possible.

Creating temp tables in SSIS seems like a straight-forward process using the Execute SQL Task, however there are a couple of properties that must be changed. In this tip, we’ll walk through creating a simple temp table in SSIS.

Creating Sample SSIS Package

First, I will drag an Execute SQL Task into my Design view and rename it Create Temp Table:

Creating temp tables in SSIS

Next, I will right click and edit and choose my connection and SQLStatement:

choose my connection and SQLStatement(The SQL statement used in this example is below)

IF OBJECT_ID('tempdb..##tmpTeams') IS NOT NULL
    DROP TABLE ##tmpTeams
    CREATE TABLE ##tmpTeams
    (
        Team VARCHAR(255),
        Mascot VARCHAR(255),
  State VARCHAR (2)
    )
    INSERT INTO ##tmpTeams VALUES
       ('Auburn', 'Tigers', 'AL'),
       ('Alabama', 'Crimson Tide', 'AL'),
       ('Mississippi', 'Rebels', 'MS'),
       ('Louisiana State', 'Tigers', 'LA'),
       ('Mississippi State', 'Bulldogs', 'MS'),
 ('Arkansas', 'Razorbacks', 'AR'),
 ('Texas A&M', 'Aggies', 'TX'),
 ('Georgia', 'Bulldogs', 'GA'),
 ('Tennessee', 'Volunteers', 'TN'),
 ('Vanderbilt', 'Commodores', 'TN'),
 ('Florida', 'Gators', 'FL'),
 ('South Carolina', 'Gamecocks', 'SC'),
 ('Missouri', 'Tigers', 'MO')

Next, I will drag a Data Flow task into my Design view, rename it Query and drag the precedence constraint between the two:

rename it Query

For the Data Flow task we are going to query the temp table and export the results to a database table. Right click the Data Flow task and choose Edit. Drag a OLE DB Source and a OLE DB Destination task into the Design view.

Right click the Data Flow task

To avoid errors when configuring the OLE DB Source we need to create the temp table first using SSMS. In SSMS run the following statement to create a global temp table:

CREATE TABLE ##tmpTeams
    (
        Team VARCHAR(255),
        Mascot VARCHAR(255),
  State VARCHAR (2)
    )

Once the table has been created, let’s go back into our SSIS package. Right click OLE DB Source and choose Edit. Choose your data source and choose SQL command for the Data access mode dropdown. In the SQL command text we will need to create our SQL statement:

SQL command text

Hit OK on the OLE DB Source window and right click OLE DB Destination and choose Edit. Choose your datasource and choose “Table or View – fast load” from the Data access mode dropdown. I already created a table called AlabamaTeams that I will use to display the data and will use this name in the dropdown for the Name of the table or the view.

Choose your datasource

Once finished configuring the OLE DB Destination hit OK. We are now ready to execute the package. Hit F5 to begin or choose Debug, Start Debugging from the Menu bar:

Start Debugging from the Menu bar

After executing the package, an error will occur on the Query task:

an error will occur on the Query task

If we look at the Progress tab we can see that the reason this error occurs is because after the temp table is created in the “Create Temp Table” task it is deleted. The “Query” task searches for the table but can’t find it.

Create Temp Table

Fixing the Issue

To fix this issue, we will need to change a few properties on the tasks and connection manager. The first property is a connection manager property. If you right click the OLEDB Connection Manager and choose properties you will see a property called RetainSameConnection. This must be set to “True”. RetainSameConnection means that the temp table will not be deleted when the task is completed.

The second property we must change is the Execute SQL Task property. If you right click on each task (Create Temp Table and Query) and choose properties you will see a property called DelayValidation. This must be set to “True”. DelayValidation means that the task will not check if the table exists upon creation.

the Execute SQL Task property

Once we have set both properties to “True”, execute the package again. The package should be successful this time:

The package should be successful

If we switch back over to SSMS and query the table, AlabamaTeams, we should return a result set:

switch back over to SSMS and query the table

**Note: I’m using global temp tables (##tmpTeams) instead of local temp tables (#tmpTeams) because when I create the table using SSMS the first time it uses a different session (SPID) therefore when I try to configure my OLE DB Source it cannot find the temp table and gives the following error:

OLE DB Source

With that said, only global temp tables should be used. Also, make sure when you are creating your global temp tables that the table name does not interfere with any other global temp tables that may be created using other processes.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s