Clever Geek Handbook

How can I create a temporary table in an SSIS control flow task and then use it in a VAL flow task?

I have a control flow where I create a temp database and table in command with T-SQL. When I add a data stream, I would like to query the table, but I cannot, because the table does not exist to capture the information. When I try, I get login errors because the database doesn't exist yet (yet). I have a validation check.

If I create the database and table manually, add a dataflow with a query and drop the database it is sticking with, 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 data streams, please let me know.

+3


source to share


2 answers


If you absolutely MUST have a database that only exists for the fleeting moments your package runs, then do so ... Your first flow control is an Execute SQL statement that creates the database. This is followed by another Execute SQL statement that creates the table. Then make your data flow to the table and your queries. Finally, as the last Control Flow, output the SQL statement using the DROP database command. Done, temporary database.

A caveat about this - as you noticed - is that in order to design a data flow, the database must exist in the first place. There is no way around this. The objects we design for the data flow MUST exist to design it. However, this problem only exists at the design stage. This is temporary. Once the data flow project is complete, you can save the package and reset the database. This will remain until you run the package again. The "latency check" setting ensures that your data stream does not try to check for a database at the top of the package. It will wait for juuust before it is needed after your CREATE database statement. So you're good there.



However, before implementing this design, I have to ask. Why are we using the database as a variable? Why CREATE this just to ALLOW it after a few seconds? I'm going to go to a limb and assume we are doing this because you want to have data in SQL, where can you use the SQL language to query and / or manipulate it selectively? If so, then the SSIS point must perform this type of manipulation or query using the provided data flow transformation tools. Also, you didn't mention this in your question, but what is the final destination of your data? We don't just load it into the database to drop it. So where does it end? Flat file?

+1


source


Solution: Set the RetainSameConnection property in the connection manager to True so that the temporary table created in one control flow task can be saved in another task. Here is an example SSIS package written in SSIS 2008 R2 that illustrates the use of temporary tables. Walkthrough: Create a stored procedure that will create a temporary table named ## tmpStateProvince and populate a few records. The sample SSIS package will first call the stored procedure and then display the temp table data to populate records in another database table. The sample package will use a database named Sora. Use below script stored procedure created.

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 the Connection Managers tab at the bottom of the package and select New OLE DB Connection ... to create a new connection to access the SQL Server 2008 R2 database.

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

Follow these steps in the Connection Manager dialog box.

  • Select OLE DB Native Client \ SQL Server 10.0 from vendor
    because the package will connect to SQL Server 2008 R2 database

  • Enter the server name, for example MACHINENAME \ INSTANCE

    • Select Use Windows Authentication under Login to Server or whichever you prefer.
    • Select the database with Select or enter a database name, sample uses the Sora database name.
    • Click "Test Connection"
    • Click OK on the corresponding Test Connection message.
    • Click "OK" in the connection manager.

The newly configured data connection appears in the Configure OLE DB Connection Manager section. Click OK.

OLE DB Connection Manager KIWI \ SQLSERVER2008R2.Sora will appear in the Connection Manager tab at the bottom of the package. Right click on Connection Manager and select Properties

Set the RetainSameConnection property to connect KIWI \ SQLSERVER2008R2.Sora to True.

Right-click anywhere in the package and select Variables to view the Variables Panel. Create the following variables. New variable named PopulateTempTable of data type String in package scope SO_5631010 and set the variable with EXEC value dbo.PopulateTempTable. New variable named FetchTempData of data type String in package scope SO_5631010 and set variable with value SELECT CountryCode, StateCode, Name FROM ## tmpStateProvince

Drag the Execute SQL call 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, do the following: Specify a name for creating and populating the temporary table Specify the OLE DB connection type Connect to KIWI \ SQLSERVER2008R2.Sora Select variable from SQLSourceType Select user :: PopulateTempTable from SourceVariable Click OK

Drag the data flow task to the Control Flow tab. Rename the Data Flow task as temporary migration data to a database table. Connect the green arrow from Execute SQL Task to the data flow task.



Double-click the Data Flow task to go to the Data Flow tab. Drag the OLE DB source to the Data Flow tab. Double-click the OLE DB source to view the OLE DB source editor. On the Connection Manager page of the original OLE DB editor, do the following: 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

On the Columns page in the OLE DB Source Editor, the following error appears because the ## tmpStateProvince table specified in the original command variable does not exist and SSIS cannot read the column definition.

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

Drag the OLE DB destination to the Data Flow tab. Connect the green arrow from the OLE DB source to the OLE DB destination. Double click OLE DB Destination to open the OLE DB Destination Editor. On the Connection Manager page of the OLE DB Destination Editor, do the following: Select KIWI \ SQLSERVER2008R2.Sora from OLE DB Connection Manager Select Table or View - Fast Load from Data Access Mode Select [dbo]. [StateProvince] from table name or view Click "Mapping Pages"

Click "Display" in the OLE DB Destination Editor, will automatically display the columns if the input and output column names are the same. Click OK. The StateProvinceID column has no corresponding input column and it is defined as an IDENTITY column in the database. Therefore, no mapping is required.

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

Go to the OLE DB Source on Data Flow tab and press F4 to view the properties. Set the ValidateExternalMetadata property to False to prevent SSIS from trying to check for the existence of a temporary table during the validation phase of the package execution.

Run a select * query from dbo.StateProvince in SQL Server Management Studio (SSMS) to find the number of rows in the table. Before executing the package, it must be empty.

Run the package. Control flow indicates successful execution.

In the Data Flow tab, you will notice that the batch successfully processed 6 rows. A stored procedure created early by nesting 6 rows into a temporary table.

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

The above example shows how to create and use a temporary table in a package

+1


source






More articles:


All Articles