How to create a table variable in SSIS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

How to create a table variable in SSIS

Author

Topic

pssheba
Yak Posting Veteran

95 Posts

Posted - 2009-01-07 : 06:04:06

Hi everyone,
I'd like to have some kind of a Temporary table in my project that will be "seen" by all tasks at the project.
I have no notion about working with temporary tables within SSIS projects so I try a Table variable.
From the Variable window i choose "Add variable" and from Data type i want to select a "Table" data type but the list doesnt contain such a datatype.
Any idea how i can go on with my efforts to work with a temoprary table within my project ?
Thanks

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2009-01-07 : 07:30:37

You can use either Temp or Variable table using SQL Task. The simplest solution is a single row set value holder so no looping necessary. Meaning, all you have to do is assign each columns to a variable name.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-07 : 10:13:12

what will you be using temporary table for? you could also consider using recordset destination.

http://msdn.microsoft.com/en-us/library/cc879316.aspx

pssheba
Yak Posting Veteran

95 Posts

Posted - 2009-01-10 : 04:07:45

quote:
Originally posted by rgombina

You can use either Temp or Variable table using SQL Task. The simplest solution is a single row set value holder so no looping necessary. Meaning, all you have to do is assign each columns to a variable name.

Thanks,
I tried to add a variable of "table" type and couldnt find how to do it.

pssheba
Yak Posting Veteran

95 Posts

Posted - 2009-01-10 : 04:09:21

quote:
Originally posted by visakh16

what will you be using temporary table for? you could also consider using recordset destination.

http://msdn.microsoft.com/en-us/library/cc879316.aspx

I'll read the tutorial, hopefully it will bring a remedy to that "temporary table" trouble.
Thanks a lot

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2009-01-12 : 06:25:12

Put this inside SQL Task and the last line (SELECT statement) is just a regular output to extract the values from. So, create a variable called entNum, type INT for "entNum" column and variable Name, type STRING for "Name". Once you have that set then those variables can be used throughout the package.


DECLARE @x_temp TABLE 
(
	rowID INT IDENTITY(0, 1) PRIMARY KEY clustered,
	[entNum] [int] NULL,
	[Name] [nvarchar](100) NULL
)

INSERT INTO @x_temp (entNum, Name)
        VALUES (1,'John Doe');
INSERT INTO @x_temp (entNum, Name)
        VALUES (2,'Jane Doe');
INSERT INTO @x_temp (entNum, Name)
        VALUES (3,'Susan Smith');
INSERT INTO @x_temp (entNum, Name)
        VALUES (4,'Cindy Moore');
INSERT INTO @x_temp (entNum, Name)
        VALUES (5,'Ramon Martinez');

select entNum, Name from @x_temp

sriram_ys
Starting Member

3 Posts

Posted - 2009-12-09 : 01:27:45

Like there is a small trick to get the temporary table working in the ssis. you can find on how to do it here...

http://www.sqllike.com/using-temporary-tables-with-ssis-40.html

Sriram
http://www.sqllike.com

Regards,
Sriram

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources