Multiple OLE DB Queries With SSIS

I have several queries written against a Teradata database (OLEDB connection) in SSIS. I am using MS SQL 2005. The queries save the results in a volatile table (temporary session  table).  An example of the query structure is listed in the code section.

When I place the queries into an a "execute SQL task", I get an error saying that only a single query can be processed.  However, when I place each query in a separate "execute SQL Task", the second query (third, fourth etc.) queries can't find the volatile table created by the earlier queries. Each SQL task created a new session.  In other words, each execute SQL task logged out after it was completed and can't find the volatile table created earlier.

Due to space constraints,  I can't save the results to a real table.  I was thinking that I could modify the SSIS package, to use one session for all of the "execute SQL tasks" .  How do I do this?  I wanted to please ask for your assistance to create one session  to run all of the queries- maybe there is an SSIS query container that would work.  
create volatile table tablename, no log
as(
select query 
) with data
on commit preserve rows;

create volatile table tablename2, no log
as(
select query 
From tablename1-- created from query above
) with data
on commit preserve rows;
(there are three more queries)

Open in new window

LVL 1
jjrr007Asked:
Who is Participating?
 
vdr1620Connect With a Mentor Commented:
In order to make use of the Temp tables in SSIS, you will need to change the property of the Execute SQL task Connection Manager

Click on Connection Manager --> Press F4 (properties)--> Change Retain Same Connection to TRUE

Never worked against TeraData Temp tables before..but,i guess you can give it a try
0
 
vdr1620Commented:
Also set the Delay validation Property of the Task to TRUE
0
 
jjrr007Author Commented:
This worked.  You are good.
0
 
jjrr007Author Commented:
Thanks for your time and expertise
0
 
vdr1620Commented:
No problem Glad it helped..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.