Possible DTS File Import Bug

Env: SQL Server 2000 on in WIN NT 5.x
Job: import mutiple flat files into several tables daily.
Catch: one or two of the several flat files might be empty.

First thought/test:
Use [first row as fields] option for the import process.
Problem, DTS can't complete (as a package).

As an alternative, I could probably detect if a file is empty then
decide what to do with it, with VB activeX, it might be feasible,
question, VB has a command for "FileExist", how about "FileLen" or the
like for determining the length of a file?

TIA.Hi

See http://www.sqldts.com/default.aspx?292 and
http://www.sqldts.com/default.aspx?246

John

"NickName" <dadada@.rock.com> wrote in message
news:1102971903.347486.145350@.f14g2000cwb.googlegr oups.com...
> Env: SQL Server 2000 on in WIN NT 5.x
> Job: import mutiple flat files into several tables daily.
> Catch: one or two of the several flat files might be empty.
> First thought/test:
> Use [first row as fields] option for the import process.
> Problem, DTS can't complete (as a package).
> As an alternative, I could probably detect if a file is empty then
> decide what to do with it, with VB activeX, it might be feasible,
> question, VB has a command for "FileExist", how about "FileLen" or the
> like for determining the length of a file?
> TIA.|||Very helpful. Thank you.|||Very helpful. Thank you. However, activeX problem, error obj "string
C:\myDir\file1.csv" required, the code seems to be correct.

' File Size
' check file size if empty | 0 quit

Option Explicit

Function Main()

Dim oFSO
Dim oFile
Dim sSourceFile

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set sSourceFile = "C:\myDir\file1.csv"

Set sSourceFileV = sSourceFile.Value

Set oFile = oFSO.GetFile(sSourceFileV)

If oFile.Size > 0 Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
' Clean Up
Set oFile = Nothing
Set oFSO = Nothing
End Function|||Never mind about the activeX error, the objFile.Value attribute was
unnecessary. But the "connectors" does not seem to have an option to
connect this ActiveX script with a package and making sure run the
ActiveX script first, instead of last.

TIA.|||Hi

You can use workflow to govern the order of the steps, if you had one or
more files to process then you can use the looping example
http://www.sqldts.com/default.aspx?246, although I would expect a three way
split for in the shouldILoop procedure or a second comparison step to cater
for files with size, files with no size and no files to process.

In your code sSourceFileV is not needed, use
Set oFile = oFSO.GetFile(sSourceFile)

John

"NickName" <dadada@.rock.com> wrote in message
news:1103038306.437005.86870@.f14g2000cwb.googlegro ups.com...
> Very helpful. Thank you. However, activeX problem, error obj "string
> C:\myDir\file1.csv" required, the code seems to be correct.
> ' File Size
> ' check file size if empty | 0 quit
> Option Explicit
> Function Main()
> Dim oFSO
> Dim oFile
> Dim sSourceFile
> Set oFSO = CreateObject("Scripting.FileSystemObject")
> Set sSourceFile = "C:\myDir\file1.csv"
> Set sSourceFileV = sSourceFile.Value
> Set oFile = oFSO.GetFile(sSourceFileV)
> If oFile.Size > 0 Then
> Main = DTSTaskExecResult_Success
> Else
> Main = DTSTaskExecResult_Failure
> End If
> ' Clean Up
> Set oFile = Nothing
> Set oFSO = Nothing
> End Function|||Hi

You can use workflow to determine the order of execution.

John

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!