New guy here - basic situation is as follows. Trying to import text
file into SQL Server. The file is sometimes empty so I want to skip
the attempted copy if the file is empty and move on to the next step.
the procedure I'm attempting is at:

http://www.sqldts.com/default.aspx?214

In the "task to skip", this is where I want my transformation,
basically a coded version of a simple text dump into the database. So
the first activex code looks like this:
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

' File Size
Option Explicit

Function Main()

Dim oFSO
Dim oFile
dim filesize
dim filename

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set oFile = oFSO.GetFile("C:\Documents and Settings\user\My
Documents\test.txt")
filesize = oFile.Size

If filesize <= 0 Then

Const vbYes = 6
Const vbYesNo = 4
Dim lResult

lResult = MsgBox("This file is empty. Would you like to skip the
next task? If you choose no, you will receive an error and the package
will not succeed.", vbYesNo)

If lResult = vbYes Then
DTSGlobalVariables("SkipTask").Value = True
Else
DTSGlobalVariables("SkipTask").Value = False
End If

Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Success
End If

' Clean Up
Set oFile = Nothing
Set oFSO = Nothing
End Function


The second one has workflow properties (options, properties) that look
like this:
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()


If DTSGlobalVariables("SkipTask").Value Then
'Don't execute this task
Main = DTSStepScriptResult_DontExecuteTask
Else
'Execute this task
Main = DTSStepScriptResult_ExecuteTask
End If

End Function



And the code itself on the second task is.....
Function Main()
MsgBox "This is THE task!"

DTSDestination("Col001") = DTSSource("Col001")
DTSDestination("Col002") = DTSSource("Col002")
DTSDestination("Col003") = DTSSource("Col003")
DTSDestination("Col004") = DTSSource("Col004")
Main = DTSTaskExecResult_Success

End Function


I'm getting a type mismatch on DTSSource and I don't know what to do.
Any ideas?

Hi

I'm new'ish to ActiveX Scripts myself, but saw no-one else has offered an
answer yet so thought I'd offer you an amature suggestion.

Whenever I want to test a condition in order to run subsequent steps, I test
the condition in the ActiveX script and either fail or succeed the ActiveX
task based on the results. I then use workflow to control what happens next
(ie on fail goto step x on success goto step y)

Hopes this helps

Paula

"unc27932 (AT) yahoo (DOT) com" wrote:

I thought I'd replied to this one but basically you want to test the file
size and you will do that using the the FileSystemObject. After discovering
the file's size you will enable or disable the relevant following task

Have a look at these for ideas

How can I check if a file exists?
(http://www.sqldts.com/default.aspx?211)

Multiple Paths in Workflow
(http://www.sqldts.com/default.aspx?218)

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"PaulaPompey" <PaulaPompey (AT) discussions (DOT) microsoft.com> wrote