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

i have control flow create temp database , table in t-sql command. when add dataflow query table can't because table doesn't exist grab information from. when try errors logging in because database doesn't exist (yet). have delay validation true.

if create database , table manually add dataflow query , drop database sticks doesn't seem clean solution.

if there better way create temporary staging database , query in dataflows please let me know.

solution:

set property retainsameconnection on connection manager true temporary table created in 1 control flow task can retained in task.

here sample ssis package written in ssis 2008 r2 illustrates using temporary tables.

walkthrough:

create stored procedure create temporary table named ##tmpstateprovince , populate few records. sample ssis package first call stored procedure , fetch temporary table data populate records database table. sample package use database named sora use below create stored procedure script.

use sora; go  create procedure dbo.populatetemptable begin      set nocount on;      if object_id('tempdb..##tmpstateprovince') not null         drop table ##tmpstateprovince;      create table ##tmpstateprovince     (             countrycode     nvarchar(3)         not null         ,   statecode       nvarchar(3)         not null         ,   name            nvarchar(30)        not null     );      insert ##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 table named dbo.stateprovince used destination table populate records temporary table. use below create table script create 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 ssis package using business intelligence development studio (bids). right-click on connection managers tab @ bottom of package , click new ole db connection... create new connection access sql server 2008 r2 database.

click new... on configure ole db connection manager.

perform following actions on connection manager dialog.

• select native ole db\sql server native client 10.0 provider since package connect sql server 2008 r2 database
• enter server name, machinename\instance
• select use windows authentication log on server section or whichever prefer.
• select database select or enter database name, sample uses database name sora.
• click test connection
• click ok on test connection succeeded message.
• click ok on connection manager

the newly created data connection appear on configure ole db connection manager. click ok.

ole db connection manager kiwi\sqlserver2008r2.sora appear under connection manager tab @ bottom of package. right-click connection manager , click properties

set property retainsameconnection on connection kiwi\sqlserver2008r2.sora value true.

right-click anywhere inside package , click variables view variables pane. create following variables.

• a new variable named populatetemptable of data type string in package scope so_5631010 , set variable value exec dbo.populatetemptable.

• a new variable named fetchtempdata of data type string in package scope so_5631010 , set variable value select countrycode, statecode, name ##tmpstateprovince

drag , drop execute sql task on control flow tab. double-click execute sql task view execute sql task editor.

on general page of execute sql task editor, perform following actions.

• set name create , populate temp table
• set connection type ole db
• set connection kiwi\sqlserver2008r2.sora
• select variable sqlsourcetype
• select user::populatetemptable sourcevariable
• click ok

drag , drop data flow task onto control flow tab. rename data flow task transfer temp data database table. connect green arrow execute sql task data flow task.

double-click data flow task switch data flow tab. drag , drop ole db source onto data flow tab. double-click ole db source view ole db source editor.

on connection manager page of ole db source editor, perform following actions.

• select kiwi\sqlserver2008r2.sora ole db connection manager
• select sql command variable data access mode
• select user::fetchtempdata variable name
• click columns page

clicking columns page on ole db source editor display following error because table ##tmpstateprovince specified in source command variable not exist , ssis unable read column definition.

to fix error, execute statement exec dbo.populatetemptable using sql server management studio (ssms) on database sora stored procedure create temporary table. after executing stored procedure, click columns page on ole db source editor, see column information. click ok.

drag , drop ole db destination onto data flow tab. connect green arrow ole db source ole db destination. double-click ole db destination open ole db destination editor.

on connection manager page of ole db destination editor, perform following actions.

• select kiwi\sqlserver2008r2.sora ole db connection manager
• select table or view - fast load data access mode
• select [dbo].[stateprovince] name of table or view
• click mappings page

click mappings page on ole db destination editor automatically map columns if input , output column names same. click ok. column stateprovinceid not have matching input column , defined identity column in database. hence, no mapping required.

data flow tab should after configuring components.

click ole db source on data flow tab , press f4 view properties. set property validateexternalmetadata false ssis not try check existence of temporary table during validation phase of package execution.

execute query select * dbo.stateprovince in sql server management studio (ssms) find number of rows in table. should empty before executing package.

execute package. control flow shows successful execution.

in data flow tab, notice package processed 6 rows. stored procedure created in posted inserted 6 rows temporary table.

execute query select * dbo.stateprovince in sql server management studio (ssms) find 6 rows inserted table. data should match rows founds in stored procedure.

the above example illustrated how create , use temporary table within package.