February 9, 2011 at 12:27 pm
and one more thing
in this case if i go to run packed again then i think it will throw error .. " ##tuser is already exsit"
February 9, 2011 at 12:32 pm
The one sure way to avoid that is to use an IF EXISTS statement before creating your temp table. But you've already got a version of that. Did you remove it from your new code?
The version you're using is iffy. I've never gotten it to work properly. Here's the version I use:
IF (SELECT Object_ID('tempdb..##TUser')) IS NOT NULL DROP TABLE dbo.##TUser;
EDIT: Can you give us the entire run log of the SSIS package so we can put this into context, please?
February 9, 2011 at 12:35 pm
IF EXISTS (SELECT TOP 1 *
FROM Tempdb.dbo.sysobjects (nolock)
WHERE name LIKE ''##TUser%'')
DROP TABLE ##TUser
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 )
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
select * from ##tuser
February 9, 2011 at 12:45 pm
i am runnig create , insert, select as single oledb darasourse cmd.
Actually i tried same without this also ...
That time i was able to run SSIS packed only once..
When i go to run second time it throw error that object is already exist ...
That’s why i include this.
My whole query is running fine in MS SQL. i am finding trouble in SSIS only ...
February 9, 2011 at 11:42 pm
IF EXISTS (SELECT TOP 1 *
FROM Tempdb.dbo.sysobjects (nolock)
WHERE name LIKE ''##TUser%'')
DROP TABLE ##TUser
Is there a reason why there are two times single quotes around TUser% in the LIKE clause?
If the code you posted is executed in an OLE DB Source in a package, than the global temp table should be destroyed when the package stops running.
So it makes no sense that the package fails when you try to run it twice.
Can you post the exact error messages? (just copy-paste them from BIDS to here)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 10, 2011 at 12:08 am
That quotes are not of any use , I forget to remove from post. Sorry 🙂
The full error msg as follow
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Invalid object name '##tuser'.".
Error at Data Flow Task [OLE DB Source [1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC020204A (Microsoft.SqlServer.DTSPipelineWrap)
February 10, 2011 at 12:16 am
I am using PUBLIC role ........ (but I don’t think it should effect on creating temp table )
February 10, 2011 at 12:38 am
Allright, I've tried it in a test package and I could get it to work.
Do the following:
1. Seperate the create and insert step from the select step. Place the creation of the temp table and the insertion of the data in a seperate Execute SQL Task, before the dataflow. Create a precedence constraint between the Execute SQL Task and the dataflow.
2. Set the connection manager property RetainSameConnection to TRUE, if you haven't done it already. (This would cause the Invalid Object Error)
3. Set the DelayValidation property of the dataflow to TRUE. Set the ValidateExternalMetadata property of the OLE DB Source to FALSE.
4. In the OLE DB Source, use the SELECT statement.
And please, layout your code (it is much easier to read) and be consistent in your code. The column names in the CREATE TABLE statement are different than the ones you use in the INSERT statement.
Example of your code nicely formatted (I also changed the IF clause to a more readable one):
IF(OBJECT_ID('tempdb..##tuser') IS NOT NULL)
BEGIN
DROP TABLE ##tuser;
END
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
);
INSERT INTO ##tuser
SELECT [instance_name]= @@SERVERNAME
,[db_name]= DB_NAME()
,[name]
,[owner_name]= SCHEMA_NAME(SCHEMA_ID)
,[type]= [type_desc]
,[create_date]
,[modify_date]
,[sysdate]= GETDATE()
FROM sys.objects;
SELECT [instance_name], [db_name], [owner_name], [type], [create_date], [modify_date], [sysdate]
FROM ##tuser;
edit: apparently, the code formatter of this forum messes up the tabs around instance_name, but you get the point 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 10, 2011 at 1:01 am
now its working ....
thanx dude... 😀
February 10, 2011 at 1:15 am
prashantjain07 (2/10/2011)
now its working ....thanx dude... 😀
No problem, glad to help.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply