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

Looping problem in DTS with ActiveX

I have a DTS package that downloads files via FTP, then processes each file. A few tasks are done when each file is processed:
(a) a holding table is truncated and 1 blank record is inserted into the holding table,
(b) the XML data in the file is inserted into the holding table via TextCopy.exe,
(c) the XML data is parsed using OPENXML and inserted into 2 additional holding tables, and
(d) the XML file is archived to another directory.
After (a), (b), (c), and (d) are completed, the DTS package loops back and executes (a), (b), (c), and (d) for the other remaining files.

It all worked flawlessly in testing, until I commented out a MsgBox line in the ActiveX task for item (b) above. Without the MsgBox command, the other tasks (b) and (c) don't appear to execute, though I can see that the looping is working, since the source files get moved to the archive location (in step (d)).

Attached is a screenshot of the DTS package (it can also be viewed at http://www.nmwildlife.org/images/DTS_screenshot.gif).

I think that the MsgBox issue is a red herring; in other words, I'm thinking that when I click the OK button on the MsgBox, there might be something about the return code which allows the tasks to be executed properly. However, I'm not a VBScript expert, so can't figure out where the problem lies or how to fix it.

Here's the code for the "Import w/ShellCmd" ActiveX task:

Function Main()
Dim objShell
Dim strPath
Dim strCmd

strPath = CSTR(DTSGlobalVariables("gv_FileFullName").Value)

strCmd = """C:\Program Files\Microsoft SQL Server\MSSQL\Binn\TextCopy.exe"" /S ""GROVER"" /U sa /P """" /D TESTProlaw /T dc_XML /C myXML /F " & _
strPath & _
" /W ""WHERE 1=1"" /I /Z"

Set objShell = CreateObject("WScript.Shell")
objShell.Run strCmd
Set objShell = nothing

MsgBox ""

Main = DTSTaskExecResult_Success
End Function

And here's the code for the "Begin Loop" ActiveX task:

Option Explicit

Function Main()

dim pkg
dim stpEnterLoop
dim stpFinished

set pkg = DTSGlobalVariables.Parent
set stpEnterLoop = pkg.Steps("DTSStep_DTSExecuteSQLTask_2") 'Start loop at the "Truncate dc_XML" task
set stpFinished = pkg.Steps("DTSStep_DTSActiveScriptTask_5")

' We want to continue with the loop only of there are more than 1 text file in the directory.
' If the function ShouldILoop returns true then we disable the step that takes us out of the package and continue processing

if ShouldILoop = True then
stpEnterLoop.DisableStep = False
stpFinished.DisableStep = True
stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting
else
stpEnterLoop.DisableStep =True
stpFinished.DisableStep = False
stpFinished.ExecutionStatus = DTSStepExecStat_Waiting
End if

Main = DTSTaskExecResult_Success
End Function

Function ShouldILoop

dim fso
dim fil
dim fold
dim pkg
dim counter

set pkg = DTSGlobalVariables.Parent
set fso = CREATEOBJECT("Scripting.FileSystemObject")

set fold = fso.GetFolder(DTSGlobalVariables("gv_FileLocation").Value)

counter = fold.files.count

'So long as there is more than 1 file carry on

if counter >= 1 then

for each fil in fold.Files
DTSGlobalVariables("gv_FileFullName").Value = fil.path
ShouldILoop = CBool(True)
Next

else
ShouldILoop = CBool(False)
End if

End Function

The goal is to get the DTS package to run without having to manually click OK on the MsgBox; that way, I can schedule it to run automatically.

Any help would be greatly appreciated. Thanks in advance!no, here the MsgBox is not returning anything. it should run perfectly without the MsgBox. i think it was used to give the shell the time to complete the process.
what u can try is - executing the shell in sync mode and drop the MsgBox. use this code and see if it works

....
Ret = objShell.Run (strCmd,,true)
.....sql

Looking for ideas

Hi everyone

We've got currenlty around 500 dts 2000 in production.

In order to know in what ETL processes we have Oracle connections, or FTP tasks or whatever, we did a VB6 app using dtspkg.dll which load all the properties for each DTS into Sql server tables. So that, then you could see from a specific DTS how many connections, Sql Tasks it had and so on..

How to accomplish the same with SSIS? I know, doing the same but using .Net, of course, but is there any else approximation? I am little bit concerned when we will have hundreds of them up.

Maybe 2005 is offering this feature automatically, I don't know.

Thanks in advance for your time/advices/ideas,

Try this -

Download details: SQL Server 2005 Business Intelligence Metadata Samples Toolkit
(http://www.microsoft.com/downloads/details.aspx?FamilyID=11daa4d1-196d-4f2a-b18f-891579c364f4&DisplayLang=en)

Even if the tools are visualisation is not what you want, it will have populated some tables with details of packages and objects that you can probably use. And if that is still not good enough the source code included will give you a start in writing your own tool.