OLE DB source creates result set in preview but not when package is run

Question

OLE DB source creates result set in preview but not when package is run

neilnewton001

Mr or Mrs. 500

Points: 503
More actions
June 10, 2010 at 4:05 pm

#141449

Hi,
Very new to SSIS. I've written a package that returns a recordset in an oledb source and SHOULD send that resultset to the next step which is a flat file destination.
I am following a web post that shows how to use a temp table in an SSIS package. This involves creating a global temp table (the post said it must be a global temp table-e.g. ##temp) which I found needs two pound signs before it. Also the RetainSameConnection propety of the ole connection manager must be set to true. Finally the DelayValidation propertie in the dataflow task has to be set to true.
The reason for for using a temp table is that I need to keep a copy of the recordset I receive from the OLEDB source so later, after writing to the flat file, I can set a processed flag in the same records I've just written to the file. Simply reselecting records with the same criteria at the end of the package can possibly add new records that have been added while I was writing to the flat file; I need to know exactly which records have already been written to the flat file.
In the oledb source I empty the global temp table, insert data into it and then select the rows just inserted into the temp table as a result set to pass to the flat file destination. If I preview the oledb source, I get data returned. However, when the entire package is run nothing is returned. I end up with an empty csv with no columns. I went through each step making sure the columns and metadata appeared all through the 3 steps of the package. The package succeed but no rows are written.
I'm not sure if:
a. I should be using the temp table with the two pound signs. I tried it with one and couldn't make it work at all.
b. I shouldn't be using stored procedure that returns a table variable.
c. the setting of RetainSameConnection and DelayValidation are interfering with the oledb source when the package is running.
I wrote a package earlier that did work in that I used an oledb source with a simple query and was able to pass the result set succesfully to a flat file destination. That is why this is so frustrating; I'm not experienced with that and I've added a number of new variables to the equation. It's hard to figure out which is the culprit.
I realize this is a really newbie question but any advice would be appreciated.
Thanks,
Fig000

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 1

When I have used temp tables in my SSIS packages, I use a local (one #) temp table. I believe that as long as you set the RetainSameConnection and DelayValidation properties to true, all tasks using the connection manager that first created the temp table should be able to see it. So I don't think that is the issue.

I would say that your problem lies in:

b. I shouldn't be using stored procedure that returns a table variable

I don't think stored procedures are easily used in a Data Flow. See this:

http://consultingblogs.emc.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx

neilnewton001 Mr or Mrs. 500 Points: 503 More actions · Answer 2

Thanks, old hand. I am not using a stored proc; I've seen some posts that discuss it and I thought it might be my answer.

I'm not sure if you were addressing the stored proc issue only or if you had given me some other advice. I think you wrote "your problem lies in" and you didn't elaborate. I am happy to stay with the design I have now. Do you think I need to use one pound sign? I am new to this and the post I followed to create this said that a global temp table is necessary. In doing research, I found that a global temp table has two pound signs in front of it. But I guess you can't believe everything you read :-). Let me know what you think I should try.

Thanks,

FIG000

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Normally, the best option is to use global temp tables, so with two #. (##temp_table)

Where do you create your temp table? In a task before the dataflow or in the source component itself?

Maybe you can place a GO statement between the INSERT of the temp table and the SELECT of the resultset, see if that helps.

(regarding table variables: they are quite interchangeable with temp tables. But you can use the TRUNCATE statement on temp tables and DDL statement, while you can't use those on table variables)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 4

The temp table should work with either type. A #table is visible only to the connection that created it. A ##table is available to all connections as long as it hasn't been dropped. See BOL for the rules on the scope and life of temp tables.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

Ed-86789 (6/11/2010)
The temp table should work with either type. A #table is visible only to the connection that created it. A ##table is available to all connections as long as it hasn't been dropped. See BOL for the rules on the scope and life of temp tables.

My post was not about if local temp tables can or cannot be used by SSIS. I stated that global temp tables are a better option than local ones, as the configuration of your SSIS packages during design time is much easier.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP