Solved

Multiple OLE DB Queries With SSIS

Posted on 2010-09-10
5
1,674 Views
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

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

0
Comment
Question by:jjrr007
  • 3
  • 2
5 Comments
 
LVL 16

Accepted Solution

by:
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
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33648000
Also set the Delay validation Property of the Task to TRUE
0
 
LVL 1

Author Comment

by:jjrr007
ID: 33650089
This worked.  You are good.
0
 
LVL 1

Author Closing Comment

by:jjrr007
ID: 33650095
Thanks for your time and expertise
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33650112
No problem Glad it helped..
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now