Im runnung SQL 2000 on Windows 2003 Server

I have a simple DTS that loops through files in a directory and imports them into a table. It then deletes the files

Step 1 - ActiveX
Loops through and gets the firat filename, and assigns it to a global variable
Step 2 - Dynamic Properties task
Set the TextFile Source to the Global Variable holding the file name
Step 3 - Data Driven Query
Update the table with data from the file
Step 4 - Deletes the file and resets the global variable for the filename to "". Then loops back to step 1

this works fine on the first loop, but on the second loop it fails on step 3 stating the source file is not found. Im at a loss as i know the global variable is showing a valid filename and that file exists. Is there a way to set the source property directly from axtive x ? maybe that will cure the problem

The method as described in http://www.sqldts.com/?246 has worked well for
me. That doesn't use a dynamic properties task, but sets the source via an
ActiveX script.

--
Jacco Schalkwijk
SQL Server MVP


"Peter Newman" <PeterNewman (AT) discussions (DOT) microsoft.com> wrote

Jacco,

Thanks, that has helped a little. I'm managing to set the Text file ( Source) Data Source to the correct file name, but am still having problems setting the SourceObjectName on the Data Driven Query

"Jacco Schalkwijk" wrote:

Here is rough example of how to set the SourceObjectName in script, showing
the collections and objects you go through-

DTSGlobalvariables.Parent.Tasks("DDQTaskName").Cus tomTask.SourceObjectName =
"Fred"


--
Darren Green
http://www.sqldts.com
"Peter Newman" <PeterNewman (AT) discussions (DOT) microsoft.com> wrote

setting the SourceObjectName on the Data Driven Query

Darren, ive tried that but get the same results.
maybe if i post the code, someone might spot where im going wrong

Step 1 - Active x


Function Main()

Dim pkg
Dim stpUpdateQuery
Dim stpFinished
Dim stpResetLoop

Set pkg = DTSGlobalVariables.Parent
Set stpUpdateQuery = pkg.steps("DTSStep_DTSDataDrivenQueryTask_1")
Set stpFinished = pkg.steps("DTSStep_DTSActiveScriptTask_4")
Set stpResetLoop = pkg.steps("DTSStep_DTSActiveScriptTask_5")

' Ensure all steps are disables to start with
stpUpdateQuery.DisableStep = True
stpFinished.DisableStep = True
stpResetLoop.Disablestep = True

' Check to see if any files exist
IF ShouldILoop = True Then
DTSGlobalVariables("gv_DTSRunning").Value = 1
stpUpdateQuery.DisableStep = False
stpResetLoop.DisableStep = False
stpUpdateQuery.ExecutionStatus = DTSStepExecStat_Waiting
Main = DTSTaskExecResult_Success
exit function
Else
stpFinished.DisableStep = False
stpFinished.ExecutionStatus = DTSStepExecStat_Waiting
Main = DTSTaskExecResult_Success
exit function
End if



End Function


Function ShouldILoop

dim fso
dim fil
dim fold
dim pkg
dim counter
Dim conTextFile

set pkg = DTSGlobalVariables.Parent
set fso = CREATEOBJECT("Scripting.FileSystemObject")
set fold = fso.GetFolder(DTSGlobalVariables("gv_FileLocation" ).Value)
counter = fold.files.count
Set conTextFile = pkg.Connections("FileSource")

'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
ELSEIF counter > 1 then
for each fil in fold.Files
DTSGlobalVariables("gv_FileFullName").Value = fil.path
ShouldILoop = CBool(True)
Next
ELSEIF counter = 0 then
ShouldILoop = CBool(False)
exit function
End if

conTextFile.DataSource = DTSGlobalVariables("gv_FileFullName").Value
DTSGlobalvariables.Parent.Tasks("DTSTask_DTSDataDr ivenQueryTask_1").CustomTask.SourceObjectName = DTSGlobalVariables("gv_FileFullName").Value

End Function


Step 2 is the Data driven Query

Step 3 - Active x

Function Main()

Dim pkg
Dim stpStartLoop
Dim fso



Set pkg = DTSGlobalVariables.Parent
Set stpStartLoop = pkg.steps("DTSStep_DTSActiveScriptTask_3")
Set fso = CreateObject("Scripting.FileSystemobject")
stpstartloop.Disablestep = True

' Delete the file
fso.DeleteFile DTSGlobalVariables("gv_FileFullName").Value
Set fso = nothing
DTSGlobalVariables("gv_FileFullName").Value = ""

DTSGlobalvariables.Parent.Tasks("DTSTask_DTSDataDr ivenQueryTask_1").CustomTask.SourceObjectName = ""

stpStartloop.Disablestep = False
stpStartloop.ExecutionStatus = DTSStepExecStat_Waiting
set pkg = nothing

Main = DTSTaskExecResult_Success
End Function


I know its not pretty, but then again im more of an amateur at this. Whats happining is if i run the DTS, it completes 1 loop without a problem, but on the second pass, although it gets the correct filenam, the DDQ fails stating the file does not exist, how ever I can execute each step in turn and complete several 'loops' without a problem. I am at a loss as to why it wont run

Thanks for any help


"Darren Green" wrote:

Quick guess, have you checked close connection on completion on the tasks
that use any connection which can changed during execution? If the
connection is open, and you change it's properties they do not take effect
as the task already has a copy of the info, so doesn't bother looking again
hence any changes are missed.

--
Darren Green
http://www.sqldts.com

"Peter Newman" <PeterNewman (AT) discussions (DOT) microsoft.com> wrote

sk.SourceObjectName = DTSGlobalVariables("gv_FileFullName").Value
sk.SourceObjectName = ""
the second pass, although it gets the correct filenam, the DDQ fails stating
the file does not exist, how ever I can execute each step in turn and
complete several 'loops' without a problem. I am at a loss as to why it
wont run

Darren,

Im lost now, can you explain the close conection and how and where i should be doing this as i havent a clue

"Darren Green" wrote:

Right-click a task, select Workflow, Workflow Properties, Options - See the
check box.

This closes the connection after the task completes. If a connection is
already in use any changes will not be "read", as this only happens when it
is opened. To ensure the connection is opened for each iteration of your
package loop, and hence the new settings are "read" you can force a close
with this option.

Make sense?


--
Darren Green
http://www.sqldts.com

"Peter Newman" <PeterNewman (AT) discussions (DOT) microsoft.com> wrote

I am having a the same dts looping problem. I checked "close connection on
completion" and do not get the error, but the the job ends. Does something
else need changed?

"Peter Newman" wrote: