Possible DTS File Import Bug

[NickName]

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.

[John Bell]

Hi

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

John

"NickName" <da****@rock.com> wrote in message
news:11**********************@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.

[NickName]

Very helpful. Thank you.

[NickName]

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

[NickName]

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.

[John Bell]

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" <da****@rock.com> wrote in message
news:11*********************@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

[John Bell]

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!