How to create and use Temp tables in SSIS

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 post, 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:

SQL Freelancer SQL Server SSIS Temp Tables
Next, I will right click and edit and choose my connection and SQLStatement:

SQL Freelancer SQL Server SSIS Temp Tables

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:

SQL Freelancer SQL Server SSIS Temp Tables
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.

SQL Freelancer SQL Server SSIS Temp Tables

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 Freelancer SQL Server SSIS Temp Tables

Click here to view the rest of this post.

Report to Capture Table Growth Statistics for SQL Server

There are a few tools that monitor table size growth, but with the use of sp_spaceused and SQL Server Reporting Services (SSRS) you can make custom reports.

Using sp_spaceused

The system stored procedure sp_spaceused displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database in an object is provided or it displays the disk space reserved and used by the whole database.

This procedure, sp_spaceused can be used at the database or table level. For example, sp_spaceused at the database level returns the following:

SQL Freelancer SQL Server SSRS Table Growth
And sp_spaceused at the table level displays:
SQL Freelancer SQL Server SSRS Table Growth

 

Creating the Report

To create our custom report, first we will need to create a table that will store the data from sp_spaceused. For this example, I’ll create a table named TableSizeGrowth.

CREATE TABLE [dbo].[TableSizeGrowth](
[id] [int] IDENTITY(1,1) NOT NULL,
[table_schema] [nvarchar](256) NULL,
[table_name] [nvarchar](256) NULL,
[table_rows] [int] NULL,
[reserved_space] [int] NULL,
[data_space] [int] NULL,
[index_space] [int] NULL,
[unused_space] [int] NULL,
[date] [datetime] NULL
) ON [PRIMARY]

I’m also going to add a default constraint that will automatically update the date column to the current date for each new row.

ALTER TABLE [dbo].[TableSizeGrowth] ADD CONSTRAINT [DF_TableSizeGrowth_date]  
DEFAULT (dateadd(day,(0),datediff(day,(0),getdate()))) FOR [date]
GO

Next, we need to create a stored procedure that moves the data from sp_spaceused to the table we created above. I have put comments throughout the stored procedure to help you figure out what is going on. Also make sure you change the table names to match your tables. For this example, I want to monitor HumanResources.Employee, Production.Product, and Purchasing.Vendor tables.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_TableSizeGrowth] 
AS
BEGIN
 SET NOCOUNT ON

 --DECLARE VARIABLES
 DECLARE
 @max INT,
 @min INT,
 @table_name NVARCHAR(256),
 @table_schema NVARCHAR(256),
 @sql NVARCHAR(4000)

 --DECLARE TABLE VARIABLE
 DECLARE @table TABLE(
 id INT IDENTITY(1,1) PRIMARY KEY,
 table_name NVARCHAR(256),
 table_schema NVARCHAR(256))

 --CREATE TEMP TABLE THAT STORES INFORMATION FROM SP_SPACEUSED
 IF (SELECT OBJECT_ID('tempdb..#results')) IS NOT NULL
 BEGIN
  DROP TABLE #results
 END

 CREATE TABLE #results
 (
  [table_schema] [nvarchar](256) NULL,
  [table_name] [nvarchar](256) NULL,
  [table_rows] [int] NULL,
  [reserved_space] [nvarchar](55) NULL,
  [data_space] [nvarchar](55) NULL,
  [index_space] [nvarchar](55) NULL,
  [unused_space] [nvarchar](55) NULL
 )

 --LOOP THROUGH STATISTICS FOR EACH TABLE
 INSERT @table(table_schema, table_name)
 SELECT  
  table_schema, table_name
 FROM
  information_schema.tables 
 WHERE table_schema + '.' + table_name IN ('HumanResources.Employee','Production.Product', 'Purchasing.Vendor') --INSERT TABLE NAMES TO MONITOR

 SELECT
  @min = 1,
  @max = (SELECT MAX(id) FROM @table)

 WHILE @min = @max
 BEGIN
  SELECT 
   @table_name = table_name,
   @table_schema = table_schema
  FROM
   @table
  WHERE
   id = @min

  --DYNAMIC SQL
  SELECT @sql = 'EXEC sp_spaceused ''[' + @table_schema + '].[' + @table_name + ']'''

  --INSERT RESULTS FROM SP_SPACEUSED TO TEMP TABLE
  INSERT #results(table_name, table_rows, reserved_space, data_space, index_space, unused_space)
  EXEC (@sql)

  --UPDATE SCHEMA NAME
  UPDATE #results
  SET table_schema = @table_schema
  WHERE table_name = @table_name
  SELECT @min = @min + 1
 END

 --REMOVE "KB" FROM RESULTS FOR REPORTING (GRAPH) PURPOSES
 UPDATE #results SET data_space = SUBSTRING(data_space, 1, (LEN(data_space)-3))
 UPDATE #results SET reserved_space = SUBSTRING(reserved_space, 1, (LEN(reserved_space)-3))
 UPDATE #results SET index_space = SUBSTRING(index_space, 1, (LEN(index_space)-3))
 UPDATE #results SET unused_space = SUBSTRING(unused_space, 1, (LEN(unused_space)-3))

 --INSERT RESULTS INTO TABLESIZEGROWTH
 INSERT INTO TableSizeGrowth (table_schema, table_name, table_rows, reserved_space, data_space, index_space, unused_space)
 SELECT * FROM #results

 DROP TABLE #results
END

Now that the stored procedure has been created, let’s go ahead and execute it.

Once the stored procedure finishes we can view the table:

SQL Freelancer SQL Server SSRS Table Growth
Click here to view the rest of this post.