May 15, 2008 at 4:32 pm
I have found myself with another problem which is using temporary tables from SSIS. My sql command creates and uses some temporary tables but when I try to put these command into my OLE DB Source in SSIS I get an error saying that it can't find my temporary tables do you know if there is a way of using temporary tables in SSIS?
Thanks for any suggestions. :hehe:
May 15, 2008 at 10:16 pm
Try Setting "Retain Same Connection" to true for connection properties in connection manager.
May 16, 2008 at 8:30 am
Thanks for answering,and yeah I tried that and didn't work, I keep getting the same error:
TITLE: Microsoft Visual Studio
------------------------------
Error at DTSTask_DTSDataPumpTask_1 [OLE DB Source [1]]: 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 '#TEMP_PRE'.".
Error at DTSTask_DTSDataPumpTask_1 [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)
Please any kinda help would be appreciated :crying: Thanks!!!!
May 28, 2008 at 9:08 pm
I'm getting the same message while using temp tables - oracle driver SSIS
May 29, 2008 at 1:48 am
Just call me slightly ignorant, but why would you consider usng temp tables in SSIS?
From what I understand (and each growing day it appears to be less) from temp tables, they are unique to the session, so one session wont be able to use the temp tables of another session, even though they are part of tempdb..systables.
Please post a little more on why you are using them and under what circumstances
~PD
May 29, 2008 at 4:59 am
I'd have to agree with the previous post...
One of the benefits to SSIS, and one of the ways to leverage performance is to transform data in a single pass. Sometimes temp or staging tables are unavoidable (the most common probably for large updates), but mostly if you can avoid unnecessary trips back and forth to the database, you should try.
I have seen various examples of this where because a developer has been very familiar with T-SQL and not so with SSIS, they have been a little over zealous on the Execute SQL task, effectively using dataflows in a package to get data into a table then performing all the transform work in various execute SQL tasks.
As I've said, sometimes a temporary dataset is unavoidable, but a lot of the time it isn't.
Kindest Regards,
Frank Bazan
May 29, 2008 at 5:25 am
The only reason I am "staging" data at the moment is because there is a very real audit requirement to have each and every control flow balanced.
It was really thought out and wasnt a decision that was taken lightly by any means.
However, in a normal warehousing environment, the need to eliminate the "stage" of data is becoming more and more obvious.
Think about it, the less disk reads you can do, and the more memory you can throw at a problem, theoretically the faster you should be.
Temporary tables point to that there may be deficiencies in the ETL design.
Hence the question, show why the temp tables are being used so that some feasible alternatives can be suggested.
~PD
June 2, 2008 at 11:23 am
I have very limited knowledge about SSIS but I have run into this situation before and I think that it is a limitation of the OLEDB data source type. I got around it by using the ADO.NET data source type.
March 19, 2009 at 1:40 pm
Try using the SET FMTONLY option in your query. It should work.
March 19, 2009 at 1:42 pm
may give problem in qry, try running it before your execute proc statement "SET FMTONLY ON; EXEC "
May 7, 2009 at 3:53 pm
I find that even when using SET FMTONLY ON; (and NOCOUNT ON) I still have issues with using temp tables in general in SSIS..i can get around it by using Table variables, until i connect to a 2000 box, in which case i cannot do things like:
-------------------------------------------
--Works in 2005, not in 2000, cant use #Dir in SSIS, so im kinda forced to do other tricks in SSIS.
Declare @Dir Table(Results varchar(1000))
Insert Into @Dir
Exec xp_cmdshell 'dir e:\mssql\backups\*.bak /b'
--parse results here...
One thing that may be SOMEWHAT helpful for anyone out there is that you can wrap your code in a stupid If statement like "If 1 = 1 Begin...End"...that combined with SET FMTONLY ON has NEARLY worked for me. I was able to preview the data with that, but once i executed on my package, i only get results for the first of my dynamic datasources...but thats another topic. All in all, im going to probalby end up doing this in VBSCRIPT, i accomplished that in about 1/2 hour 2 weeks ago, still working on the SSIS version lol.
May 8, 2009 at 7:33 am
Instead of temp table if you could use a function that returns a table then i think it will work for you.
July 21, 2009 at 12:49 pm
Hi,
I have had same issues, try using
SET FMTONLY OFF;
EXEC dbo.my_stored_procname
This should work.
Also i found something useful on the net.
http://sqlserverpedia.com/blog/sql-server-bloggers/ssis-stored-procedure-metadata/
Hope this helps.
Thanks,
Amol
Amol Naik
July 21, 2009 at 1:03 pm
July 21, 2009 at 2:46 pm
I've tried using temp tables and you can *sometimes* get it to work, but the problem is that I *always* need the process to work.
When I have had a truly temp table that I KNOW there will ONLY be one process running against I have resorted to creating a normal table in tempdb, I call them persisted temp tables. My ETL package creates, populates, uses, and destroys them. My creation process does an IF EXISTS/DROP check so I know that I have the correct structure every time.
I have to warn you against using temp tables for sheer stability reasons, it doesn't matter if the process works today if you don't have any idea if it will work tomorrow.
CEWII
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply