How to create a temporary table in SSIS control flow task and then use it in data flow task?

Question

I have a control flow where I create a temp database and table with a T-SQL Command. When I add a dataflow I would like to query the table but I can't because the table doesn't exist to grab information from. When I try I get errors about logging in because the database doesn't exist (yet). I have set the connection manager's property DelayValidation to True.

If I create the database and table manually and then add the dataflow with query and drop the database, it sticks but it doesn't seem like a clean solution.

If there is a better way to create a temporary staging database and query it in dataflows please let me know.

Phototherapy

I would probably use a variable instead, after all SSIS is meant to work in memory. – Rubellite

Answer 1

Solution:

Set the property RetainSameConnection on the Connection Manager to True so that temporary table created in one Control Flow task can be retained in another task.

Here is a sample SSIS package written in SSIS 2008 R2 that illustrates using temporary tables.

Walkthrough:

Create a stored procedure that will create a temporary table named ##tmpStateProvince and populate with few records. The sample SSIS package will first call the stored procedure and then will fetch the temporary table data to populate the records into another database table. The sample package will use the database named Sora Use the below create stored procedure script.

USE Sora;
GO

CREATE PROCEDURE dbo.PopulateTempTable
AS
BEGIN
    
    SET NOCOUNT ON;

    IF OBJECT_ID('TempDB..##tmpStateProvince') IS NOT NULL
        DROP TABLE ##tmpStateProvince;

    CREATE TABLE ##tmpStateProvince
    (
            CountryCode     nvarchar(3)         NOT NULL
        ,   StateCode       nvarchar(3)         NOT NULL
        ,   Name            nvarchar(30)        NOT NULL
    );

    INSERT INTO ##tmpStateProvince 
        (CountryCode, StateCode, Name)
    VALUES
        ('CA', 'AB', 'Alberta'),
        ('US', 'CA', 'California'),
        ('DE', 'HH', 'Hamburg'),
        ('FR', '86', 'Vienne'),
        ('AU', 'SA', 'South Australia'),
        ('VI', 'VI', 'Virgin Islands');
END
GO

Create a table named dbo.StateProvince that will be used as the destination table to populate the records from temporary table. Use the below create table script to create the destination table.

USE Sora;
GO

CREATE TABLE dbo.StateProvince
(
        StateProvinceID int IDENTITY(1,1)   NOT NULL
    ,   CountryCode     nvarchar(3)         NOT NULL
    ,   StateCode       nvarchar(3)         NOT NULL
    ,   Name            nvarchar(30)        NOT NULL
    CONSTRAINT [PK_StateProvinceID] PRIMARY KEY CLUSTERED
        ([StateProvinceID] ASC)
) ON [PRIMARY];
GO

Create an SSIS package using Business Intelligence Development Studio (BIDS). Right-click on the Connection Managers tab at the bottom of the package and click New OLE DB Connection... to create a new connection to access SQL Server 2008 R2 database.

Click New... on Configure OLE DB Connection Manager.

Perform the following actions on the Connection Manager dialog.

• Select Native OLE DB\SQL Server Native Client 10.0 from Provider since the package will connect to SQL Server 2008 R2 database
• Enter the Server name, like MACHINENAME\INSTANCE
• Select Use Windows Authentication from Log on to the server section or whichever you prefer.
• Select the database from Select or enter a database name, the sample uses the database name Sora.
• Click Test Connection
• Click OK on the Test connection succeeded message.
• Click OK on Connection Manager

The newly created data connection will appear on Configure OLE DB Connection Manager. Click OK.

OLE DB connection manager KIWI\SQLSERVER2008R2.Sora will appear under the Connection Manager tab at the bottom of the package. Right-click the connection manager and click Properties

Set the property RetainSameConnection on the connection KIWI\SQLSERVER2008R2.Sora to the value True.

Right-click anywhere inside the package and then click Variables to view the variables pane. Create the following variables.

• A new variable named PopulateTempTable of data type String in the package scope SO_5631010 and set the variable with the value EXEC dbo.PopulateTempTable.

• A new variable named FetchTempData of data type String in the package scope SO_5631010 and set the variable with the value SELECT CountryCode, StateCode, Name FROM ##tmpStateProvince

Drag and drop an Execute SQL Task on to the Control Flow tab. Double-click the Execute SQL Task to view the Execute SQL Task Editor.

On the General page of the Execute SQL Task Editor, perform the following actions.

• Set the Name to Create and populate temp table
• Set the Connection Type to OLE DB
• Set the Connection to KIWI\SQLSERVER2008R2.Sora
• Select Variable from SQLSourceType
• Select User::PopulateTempTable from SourceVariable
• Click OK

Drag and drop a Data Flow Task onto the Control Flow tab. Rename the Data Flow Task as Transfer temp data to database table. Connect the green arrow from the Execute SQL Task to the Data Flow Task.

Double-click the Data Flow Task to switch to Data Flow tab. Drag and drop an OLE DB Source onto the Data Flow tab. Double-click OLE DB Source to view the OLE DB Source Editor.

On the Connection Manager page of the OLE DB Source Editor, perform the following actions.

• Select KIWI\SQLSERVER2008R2.Sora from OLE DB Connection Manager
• Select SQL command from variable from Data access mode
• Select User::FetchTempData from Variable name
• Click Columns page

Clicking Columns page on OLE DB Source Editor will display the following error because the table ##tmpStateProvince specified in the source command variable does not exist and SSIS is unable to read the column definition.

To fix the error, execute the statement EXEC dbo.PopulateTempTable using SQL Server Management Studio (SSMS) on the database Sora so that the stored procedure will create the temporary table. After executing the stored procedure, click Columns page on OLE DB Source Editor, you will see the column information. Click OK.

Drag and drop OLE DB Destination onto the Data Flow tab. Connect the green arrow from OLE DB Source to OLE DB Destination. Double-click OLE DB Destination to open OLE DB Destination Editor.

On the Connection Manager page of the OLE DB Destination Editor, perform the following actions.

• Select KIWI\SQLSERVER2008R2.Sora from OLE DB Connection Manager
• Select Table or view - fast load from Data access mode
• Select [dbo].[StateProvince] from Name of the table or the view
• Click Mappings page

Click Mappings page on the OLE DB Destination Editor would automatically map the columns if the input and output column names are same. Click OK. Column StateProvinceID does not have a matching input column and it is defined as an IDENTITY column in database. Hence, no mapping is required.

Data Flow tab should look something like this after configuring all the components.

Click the OLE DB Source on Data Flow tab and press F4 to view Properties. Set the property ValidateExternalMetadata to False so that SSIS would not try to check for the existence of the temporary table during validation phase of the package execution.

Execute the query select * from dbo.StateProvince in the SQL Server Management Studio (SSMS) to find the number of rows in the table. It should be empty before executing the package.

Execute the package. Control Flow shows successful execution.

In Data Flow tab, you will notice that the package successfully processed 6 rows. The stored procedure created early in this posted inserted 6 rows into the temporary table.

Execute the query select * from dbo.StateProvince in the SQL Server Management Studio (SSMS) to find the 6 rows successfully inserted into the table. The data should match with rows founds in the stored procedure.

The above example illustrated how to create and use temporary table within a package.

Influenza

If I don't have control of the original query - is there a way to redirect the data to, say, an in-memory cache, transform it with SQL and then redirect it elsewhere (e.g. flat file)? At this point, it seems I need to create a staging table which I need to truncate each time I use it. – Skatole

One of the most detailed and accurate answers I have seen – Hammon

Answer 2

I'm late to this party but I'd like to add one bit to user756519's thorough, excellent answer. I don't believe the "RetainSameConnection on the Connection Manager" property is relevant in this instance based on my recent experience. In my case, the relevant point was their advice to set "ValidateExternalMetadata" to False.

I'm using a temp table to facilitate copying data from one database (and server) to another, hence the reason "RetainSameConnection" was not relevant in my particular case. And I don't believe it is important to accomplish what is happening in this example either, as thorough as it is.

Leyden

I just up-voted the other answer because I had missed the Retain Same Connection property. I had my create table in a separate task but the task that uses the temp tables couldn't read them until I changed the RETAIN property. Why the GLOBAL TEMP tables couldn't be used, I don't know. – Awad

@HannoverFist Now I understand your remark after testing it myself. Without the "RetainSameConnection" to True on the Connection Manager that you take for the temporary table, there is no way to keep the temporary table alive outside the Data Flow Task. – Cayla

This answer might not need to be about the same thing as the other answer. If this answer is about copying data to a temp table on the one server and then selecting that temp table into a table on the other server, that is, in the role of the persisted memory as a bridge between the servers, then it might be right. To make this clear, you cannot select into from one to another server, but you might work around that with a temp table if you stay in the same Data Flow Task. Inside the DFT, the table is not dropped at once so that you would not need to set "RetainSameConnection" to True. – Cayla

Answer 3

About

1. This answer affords screenshots of SSIS in Microsoft Visual Studio 2017.

2. While the other answer here asks you to run a stored procedure, this answer here connects to the right "tempdb" database and does not need a stored procedure. This may be nice for those who want to avoid spamming the production server with stored procedures.

This answer shows that you can work with temporary tables inside the Control Flow and make them destinations in the DFT, something which you can also find at Use Temp Table in SSIS?, as I found out later, but that guide forgets to show the Data Flow Task side of it. If you change to the DFT pane after creating the temporary table in the Control Flow pane, you can make a new connection to the "tempdb" database and find the still living temporary tables that you made in the Control Flow.

tempdb system database as the only database for temporary tables

The answer to the question, if I take every word of it, is: there is no answer. You ask for a temporary table that is saved in the new database that comes to life on the run. But temporary tables are saved - always - on the server level in the system database "tempdb", see tempdb database - Microsoft - Learn - SQL - SQL Server. You cannot put a temporary table in your own database. Strangely, I found only one remark on Stack Overflow that stressed this, see the one under Check if a temporary table exists and delete if it exists before creating a temporary table.

Therefore, there is half of a question left, you ask what could be done instead. Answer: Do not make a new database for the temporary table. Instead take the server as it is and make the temporary table in the "tempdb" system database. There is no other way. If you then take this connection in SSIS, you can also deal with the temporary table as if it was a normal table. You do not need a stored procedure to set this up. You can just choose the temporary table from the "tempdb" database and take that as the "OLE DB" object in SSIS.

Steps in screenshots

Make a tempdb connection

Main trick: change "RetainSameConnection" to True

As the other answer rightly said, this is the only way to keep the temporary table alive between the Control Flow steps.

Control Flow

Make and fill a temporary table

Control Flow 3: Write TempDB ##tmpTest

Begin with the last Control Flow step 3, the Data Flow Task as the core of the sequence container that writes to the TempDB temporary table. By beginning with this, you will get to know the data types that you need for the table in Control Flow step 2.

OLE DB Source Editor

SELECT 'Hello' AS Test

OLE DB destination object

CREATE TABLE ##tmpTest(
 [Test] [varchar](5) NULL
)

Copy the code that you get at a click on "New".

OLE DB Destination Editor

Control Flow 2: Make TempDB ##tmpTest

Create Table with the copied code:

OLE DB Destination Editor

Control Flow 1: Drop TempDB ##tmpTest

This is not needed since the temporary table gets lost as soon as the Control Flow stops, see How to see temp table created by code in sql server?. But you are on the safe side if you fear that someone (or you) might work on that same temporary table in SSMS so that it might not be empty when the container starts.

IF OBJECT_ID(N'tempdb.dbo.##tmpTest') IS NOT NULL
        DROP TABLE dbo.##tmpTest;

Or on a 2016 server, you can run DROP TABLE IF EXISTS ##tmpTest;

See Check if a temporary table exists and delete if it exists before creating a temporary table, and mind in that same link:

Instead of dropping and re-creating the temp table you can truncate and reuse it.

Also see How to see temp table created by code in sql server?.

Run and check

If the table cannot be reached, you will see:

If it works, you see:

Keep it green in debug mode and you will see the temporary table still alive:

Now get out of the debug mode:

And the temporary table will be gone if you refresh:

And the table is filled:

You can go on with this table as if it was not temporary and feed another query or table with it. Since it has two hashtags in front, "##", it is global, and you can work with it in the whole package until the package has stopped running. Once the package stops and if you are not in debug mode, the temporary table is dropped.

Cayla