February 9, 2011 at 6:22 am
I want to cerate the temp table (##tuser) on remote sql server using SSIS.
i create oledb datasource under data flow task. create connection to remote sql server.
i am trying like this...
DECLARE @cmd1 VARCHAR(max)
DECLARE @cmd2 VARCHAR(max)
DECLARE @cmd3 VARCHAR(max)
set @cmd1='IF EXISTS (SELECT TOP 1 *
FROM Tempdb.dbo.sysobjects (nolock)
WHERE name LIKE ''##TUser%'')
DROP TABLE ##TUser'
execute (@cmd1)
set @cmd2='CREATE TABLE ##tuser( [instance_name] [nvarchar](128) NULL,db_name [nvarchar] (128) null,[name] [sysname] NULL,[owner_name] [nvarchar](128) NULL,[type] [nvarchar](60) NULL,[create_date] [datetime] NULL,[modify_date] [datetime] NULL,[sysdate] [datetime] NULL )'
execute (@cmd2)
set @cmd3='select * from ##tuser'
execute(@cmd3)
output of this should be in olebd destination.
but i am getting an error related to ##tuser (temp table)
Any idea how to do this......?
February 9, 2011 at 6:56 am
It's in the connections manager property of your package. SSIS packages create and drop multiple connections when running, which kills your temp table before you've had a chance to use it (temp tables "disappear" when all connections/sessions are closed).
In connection manager properties, look for "RetainSameConnection" and set it to "True." That should fix the problem.
February 9, 2011 at 7:26 am
Depending on what you're doing, the use of Global Temp Tables can cause concurrency problems.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 9, 2011 at 7:35 am
I forgot to ask. What is the exact text of the error you're getting?
February 9, 2011 at 10:29 am
That i already test but still having same problam.
February 9, 2011 at 10:34 am
What is the error message? Please post it.
February 9, 2011 at 10:38 am
invaled Object ##tuser
February 9, 2011 at 10:48 am
Between @Cmd2 and @Cmd3, how are you populating the temp table?
Is it a separate task after executing @Cmd2 and before executing @Cmd3? Or are you doing it all in one task and just omit the table population?
February 9, 2011 at 11:01 am
i am doing all task in one shot..
i not mantion the insert step.
set @cmd1='insert into ##tuser select @@servername as instance,db_name() as db_name,name,schema_name(schema_id) as owner_name,type_desc,create_date,modify_date,getdate() as date from sys.objects
then i am tryong to select the data from ##tuser...
creatr , insert and select, all act as a single process.
February 9, 2011 at 11:33 am
Why are you using EXEC to execute your statements. You don't have dynamic SQL, so I don't see the point.
Furthermore, it is possible that the statement within the EXEC is executed under a different scope, so your temp table is likely already destroyed before the next statement begins.
Drop the EXEC statements and just execute the statements like in a normal batch.
And just to be sure, set the RetainSameConnection property of the connection manager to TRUE.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 9, 2011 at 11:35 am
Koen (da-zero) (2/9/2011)
Furthermore, it is possible that the statement within the EXEC is executed under a different scope, so your temp table is likely already destroyed before the next statement begins.
Oh, I didn't even think about that. Good catch, Koen.
February 9, 2011 at 11:51 am
i already RetainSameConnection property as ture...
can you please describe your answer with the help of sample code
February 9, 2011 at 11:56 am
Remove your variable declarations. Remove the "Set @cmd1 = ' " bits and the trailing single quotes after the T-SQL. Remove the Execute statements.
Then run the code.
As Koen said, there's no reason to set your statements into variables and then execute the variables since you're not adding additional information to the statements on the fly.
February 9, 2011 at 12:22 pm
i tryed without @cmd and execute ..
now i am getting error at the time of validation itself...
error msg " invalid object##tuser"
" ##tuser dose not exsit"
February 9, 2011 at 12:24 pm
Post your new code. Include details on which code is in which task and tell us which of the tasks is failing.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply