Losing Temp Tables Between Tasks

I have a control flow setup with 5 tasks.

1. Create Temp Table (Execute SQL)
2. Load Temp Table (Execute SQL)
3. Data Flow Task using the temp table
4. Data Flow Task using the temp table
5. Drop Temp Table (Execute SQL)

Tasks 3 & 4 are currently setup to run parallel/concurrently - meaning they both branch directly off of task #2. They both also lead to task #5. Also, my connection manager is set to retain its connection.

Here's the problem. When executing this flow, task #3 will sometimes fail, other times it will succeed - without me changing anything between runs (I simply end debugging and run it again). When it does fail it is always because it cannot find the temp table. One thing to note is that task #4 (running at the same time as #3 and using the same temp table) always finishes first because it does quite a bit less work. Last thing to note: if I set up task 4 to run after 3 it works everytime, but of course it takes much longer for the total package to execute.

How is it that the task can find the temp table on some runs, but not on others? If this is just quirky behavior that I am going to have to live with for the time being, is there a way to force a task to re-run X number of times before giving up and moving on?

Thanks,
David Martin

Temp tables are only guaranteed to remain in existence as long as they are referenced. Is it possible that the connection is being dropped and reopened by SSIS for some reason between tasks?

Or that there is some time during the processing where Sql Server cannot detect the temp table is still being referrenced, so drops it even if the connection is still open?

Seems like using temp tables is risky if you don't have full and explicit control over their existence. I want to use them also but not if I cannot guarantee they will be there when I need them!

|||

Make sure the "RetainSameConnection" property is set to "True" for the connection. This will allow the Execute SQL tasks to share the connection so the temp table can be accessed.

Frank

sql

loosing values when load from text file

When loading a table in a data flow from a text file that contains non-null float values, I am seeing erratic and inconsistent results. I am presently using SQL Server Destination in a data flow.

- With low volumnes of data, less that 50,000 rows, no problems

- But with higher volumnes, 2,000,000+ rows, I get different results depending on how I run the package. If I run is directly (right-click and click on Execute), I get the expected result.

But if I use SQL Server Agent to run the package, half of the values are lost and nulls are loaded instead. I have inspected the into text file and there are few rows with null for the column.

Any help would be appreciated!

Greg

>>>- With low volumnes of data, less that 50,000 rows, no problems

Did you have success using both BIDS and SQL Agent for the 50K load?

>>>- But with higher volumnes, 2,000,000+ rows, I get different results depending on how I run the package.

While using SQL Agent did you chose the Command subsystem and use dtexec or the SSIS subsystem?

Lookup Arrays

I need to load an array of order numbers.. Then later I need to be able to
'lookup' in that area for a specific order number (for example one that user
keys into text box).
how is this accomplished? ive researched Array and Arraylist and cannot
figure how without a bunch of work.
There must be something simple for this.
Can you help?
Thanks.
jrHello Jr,
Welcome to the MSDN newsgroup.
From your description, you have an order number list which want to be
stored in a list container and you'll lookup some certain order numbers
from that container later in your SQL Reporting service's report, correct?
Based on my experience, since these code should be in your custom code
period or an separate assembly, so you're using them as custom code or
custom assembly in your report , right? If this is the case, what
available to us are all those utility or collection classes in the .net
framework system.collections namespace(Such as the Array, ArrayList you
mentioned).
For your scenario, I'm wondering how will you identify a certain order
number? Just through the orderID or will you give each order number another
identity(a key value) for lookup? Here are two generic cases:
1. If you just want to get the order item's index in the container(such as
Array) through the order number(suppose its of string type), you can just
Array.IndexOf method to locate a certain item in the Array:
#Array.IndexOf, m¨¦thode (Array, Object)
http://msdn2.microsoft.com/fr-fr/library/7eddebat.aspx
Here is a simple test class which intializing the Array(orderList) in the
type's static initializer and expose a public static method for lookup item
index in the array:
==================public class ArrayUtil
{
public static string[] OrderArray;
public ArrayUtil()
{
}
static ArrayUtil()
{
OrderArray = new string[10];
for (int i = 0; i < OrderArray.Length; i++)
{
OrderArray[i] = "Order_" + i;
}
}
public static int FindOrder(string num)
{
return Array.IndexOf(OrderArray, num);
}
}
=====================
2. If each order nunber will be stored with an identity value as the lookup
key, I think the "HashTable" class is the reasonable choice. HashTable
class support storing multiple key/value pairs and have method for looking
up a certain item in it via its key value:
#Hashtable Class
http://msdn2.microsoft.com/en-us/library/system.collections.hashtable.aspx
Hope this helps. If there is any paricular concerns in your scenario or if
you have any other ideas ,please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Jr,
How are doing on this issue, have you got any progress or does my last
reply helps you a little on this? If you have anything unclear or still
anything else we can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)