Why DTS ignores a second extension when loading a text file?
-
Hi all;
I’m not sure if this was posted before but here is the issue, I have a DTS that loads a .txt file into a table, my source file connection points to the path file “c:\data_file\users.txt” and I also have an ActiveX script that checks if the file with that name is not in that folder then it stops the DTS.
What happened was I used another file with this name “users.txt.tmp” and I was expecting the DTS to stop processing as the filename doesn’t match “users.txt”, but guess what, I was surprised that it skipped and loaded the file.
So, my question is, why the DTS ignored the second extension (.tmp) as if its not there and loaded the file? Is it a bug is SQL DTS that it takes the first extension and ignores the remaining ones?
I’m kind of stuck and don’t understand why that happened.
Your thoughts are really appreciated
Thanks
Mohammad Musleh
-
Do you have the c:\...\users.txt hardcoded, or is it passed into the data load from your activex script once the activeX script finds the file?
Any chance you could post the code that does the looking for the file?
-
Here is the ActiveX code I use to check for the existence of the file
Set objFSO = CreateObject("Scripting.FileSystemObject")
’’’ ImportPath = c:\data_file
’’’ ImportFile = users.txt
’’’ ImportPath and ImportFile are Global Variables
ImportPath = DTSGlobalVariables("ImportPath").Value
ImportFile = DTSGlobalVariables("ImportFile").Value
ImportFullPath = ImportPath & ImportFile
If objFSO.FileExists(ImportFullPath) Then
Main = DTSStepScriptResult_ExecuteTask
Else
Main = DTSStepScriptResult_DontExecuteTask
End If
Set objFSO = Nothing
-
Here is are the mains steps I have in the DTS
I have the following global variables
ImportPath, which equals to “c:\data_file” and
ImportFile, which equals to “users.txt”
FileExists, which equals to False “this is a flag I use either to move forward with the DTS or to stop it”
(1)
First thing I check in my DTS is the existence of the file that I’m loading by setting the flag “FileExists” to True or False
Function Main()
Dim objFSO, strDataFileName, ImportFullPath
Set objFSO = CreateObject("Scripting.FileSystemObject")
ImportPath = DTSGlobalVariables("ImportPath").Value
ImportFile = DTSGlobalVariables("ImportFile").Value
ImportFullPath = ImportPath & ImportFile
If objFSO.FileExists(ImportFullPath) Then
DTSGlobalVariables("FileExists").Value = True
Else
DTSGlobalVariables("FileExists").Value = False
End If
Set objFSO = Nothing
Main = DTSTaskExecResult_Success
End Function
(2)
Then I have an “Execute SQL Task” to clean up a holding table if it has data, in the workflow properties for this task, I have the following ActiveX code
Function Main()
If DTSGlobalVariables("FileExists").Value = True Then
Main = DTSStepScriptResult_ExecuteTask
Else
Main = DTSStepScriptResult_DontExecuteTask
End If
End Function
(3)
I load the file into a holding table
Then I’m done.
Thanks
-
I'm not sure if DTSStepScriptResult_DontExecuteTask is supposed to raise an error or just tell the package to skip that particular task? BOL is somewhat vague on the subject. Do you have FailPackageonErrors Set?
Also, have a look at this and see if it helps...
http://msdn2.microsoft.com/en-us/library/aa176253(SQL.80).aspx
-
answer to mrpolecat
Yes, users.txt was not there but users.txt.tmp was there.
-
answer to Luke L
No I don't have FailPackageonErrors set in the DTS, and I looked at the site you provided, it doesn't help
I'm suspecting "almost +" that its an issue within DTS itself that it ignores any extra extensions as long as the first filename and extension is found; and this is what I'm researching and trying to find an answer for.
-
Unless you have other workflow manipulation tasks in subsequent steps, then all you are telling DTS to do is not run the ExecuteSQL task. To prevent the import from occurring you'll also need to change it's precedence constraint.
Check out the following article,
http://www.sqldts.com/218.aspx
Page two details how the precedence constraints are modified to prevent a task from executing.
--------------------
Colt 45 - the original point and click interface
-
Like I said on the other forum, I think you have a bit of a technical error in the code. I think this...
ImportFullPath = ImportPath & ImportFile
... should be this...
ImportFullPath = ImportPath & "\" & ImportFile
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
You are right Jeff, I'm using "\" when I define the global variable, so my global variable ImportPath equals to “c:\data_file\”
-
Mohammad -
Just a quick comment then a couple of questions.
Comment: Your problem is not necessarily a problem with DTS, your code is utilizing an external resource (the FileSystem object) to poll the directory...
Which brings me to the questions:
(1) What O/S version/level are you running on?
(2) What version of VBScript (see http://www.computerperformance.co.uk/Logon/VBScript/VBScript_Windows_version.htm#Example_3_-_VBScript_to_Check_the_VBScript_Version_)?
(3) How is your c:\ drive formatted (NTFS?)
(4) Version/service pack level of SQL Server?
Joe
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply