Multiple OLE DB Queries With SSIS

Posted on 2010-09-10
Last Modified: 2012-06-27
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
select query 
) with data
on commit preserve rows;

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

Open in new window

Question by:jjrr007
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 16

Accepted Solution

vdr1620 earned 500 total points
ID: 33647992
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
LVL 16

Expert Comment

ID: 33648000
Also set the Delay validation Property of the Task to TRUE

Author Comment

ID: 33650089
This worked.  You are good.

Author Closing Comment

ID: 33650095
Thanks for your time and expertise
LVL 16

Expert Comment

ID: 33650112
No problem Glad it helped..

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Cannot get group page break to work in SSRS 2008R2. 14 40
mssql 7 32
Delete duplicates from SQL Server table 2 27
Section based report in SSRS 14 34
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question