error importing a files in mssql 2000

hi,

i have this problem:
i am generating every 2 hours 4 comma delimited file with filename as1, as2, as3, as4 in order to import them using a dts in my mssql 2000 db sequentialy. the files can be of 0kb or have data in it.

in my dts i am importing all 4 files in the db. now i get an execute error for the files that are of 0kb. the files with data are imported which is ok.

but how can i make the dts to do not execute the importing of files that are of 0kb and do only the import of files that are > than 0kb.
eawebAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

eawebAuthor Commented:
i get the following step error:
error creating datafile mapping: the volume for a file has been externally altered so that the opened file is no longer valid
0
dbbishopCommented:
You have this posted in the VB Script zone, so if you have the code in an ActiveX script task, you can use the filesystem object to determine the size:

Dim fso

Set fso = CreateObject("Scripting.FileSystemObject")

fso.GetFile("path & filename")
If fso.Size > 0 Then
        ' import the file
End If
0
Anthony PerkinsCommented:
Is there any task that needs to execute after that.  In other words do you do anything regardless of whether the file is 0 bytes or not.  If you can post the DTS diagram it would be helpful to better understand what you are trying to achieve (a picture is worth a thousand words).
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

eawebAuthor Commented:
please find attached the diagram

first i run a sql task to create a temp table
then i insert the generated file data in the database. here is where i get an error on the insert task of the file with 0kb

error:
error creating datafile mapping: the volume for a file has been externally altered so that the opened file is no longer valid

what i need is if the file is of 0kb don't execute the insertion and try the next one.
Doc1.doc
0
Anthony PerkinsCommented:
This is how you do it:
Assuming the Data Transformation Step is called DTSStep_DTSDataPumpTask_1. the first SQL Execute Task immediately after is called DTSStep_DTSExecuteSQLTask_2 and the Text Connection is called "STM DB FILE CUR" then add this code in the ActiveX Script Step (Workflow) for the Task:

Option Explicit

'  This skips the Import Step when the file is empty

Function Main()
Const      TEXT_IMPORT_STEP = "DTSStep_DTSDataPumpTask_1", _
      SQL_IMPORT_STEP = "DTSStep_DTSExecuteSQLTask_2", _
      TEXT_CONNECTION = "STM DB FILE CUR"

Dim FSO, FileName
Dim Pkg, Constraints, Constraint

Set FSO = CreateObject("Scripting.FileSystemObject")
Set Pkg =  DTSGlobalVariables.Parent

Set Constraints = Pkg.Steps(SQL_IMPORT_STEP).PrecedenceConstraints
Set Constraint = GetConstraint(Constraints, TEXT_IMPORT_STEP)

FileName = Pkg.Connections(TEXT_CONNECTION).DataSource

If FSO.GetFile(FileName).Size > 0 Then
      Constraint.PrecedenceBasis = DTSStepPrecedenceBasis_ExecResult
      Constraint.Value = DTSStepExecResult_Success
      Main = DTSStepScriptResult_ExecuteTask
Else                        
      Constraint.PrecedenceBasis = DTSStepPrecedenceBasis_ExecStatus
      Constraint.Value = DTSStepExecStat_Inactive Or DTSStepExecStat_Waiting
      Main = DTSStepScriptResult_DontExecuteTask
End If

Set Pkg = Nothing
Set FSO = Nothing

End Function


' Get Constraint by Source Step Name
Function GetConstraint(Constraints, StepName)
Dim Constraint

For Each Constraint In Constraints
      If Constraint.StepName = StepName Then
            Set GetConstraint = Constraint
            Exit For
      End If
Next

End Function


Now repeat the same for all the other tasks.
0
eawebAuthor Commented:
hi acperkins,
i am having a problem finding the following variables data

===can be found in the transform data task properties but not sure
TEXT_IMPORT_STEP = "DTSStep_DTSDataPumpTask_1", _

===i dont know where to find this one
SQL_IMPORT_STEP = "DTSStep_DTSExecuteSQLTask_2", _

===is the text file
TEXT_CONNECTION = "STM DB FILE CUR"


could you please point me where i can find the related variables data in the diagram
the run is giving me error that it can't find the data names i gave to variables.
0
Anthony PerkinsCommented:
Right click on the diagram and select "Disconnected Edit ..." you can then see the names of each Task and Step
0
eawebAuthor Commented:
acperkins, I edited the package with disconnected edit and run the package, but it looks like it hangs the enterprise.

see below the code i use and attched the design image i use it for:

Option Explicit

'  This skips the Import Step when the file is empty

Function Main()
Const      TEXT_IMPORT_STEP = "DTSStep_DTSDataPumpTask_1", _
      SQL_IMPORT_STEP = "DTSStep_DTSExecuteSQLTask_2", _
      TEXT_CONNECTION = "ERR DB FILE"

Dim FSO, FileName
Dim Pkg, Constraints, Constraint

Set FSO = CreateObject("Scripting.FileSystemObject")
Set Pkg =  DTSGlobalVariables.Parent

Set Constraints = Pkg.Steps(SQL_IMPORT_STEP).PrecedenceConstraints
Set Constraint = GetConstraint(Constraints, TEXT_IMPORT_STEP)

FileName = Pkg.Connections(TEXT_CONNECTION).DataSource

If FSO.GetFile(FileName).Size > 0 Then
      Constraint.PrecedenceBasis = DTSStepPrecedenceBasis_ExecResult
      Constraint.Value = DTSStepExecResult_Success
      Main = DTSStepScriptResult_ExecuteTask
Else                        
      Constraint.PrecedenceBasis = DTSStepPrecedenceBasis_ExecStatus
      Constraint.Value = DTSStepExecStat_Inactive Or DTSStepExecStat_Waiting
      Main = DTSStepScriptResult_DontExecuteTask
End If

Set Pkg = Nothing
Set FSO = Nothing

End Function


' Get Constraint by Source Step Name
Function GetConstraint(Constraints, StepName)
Dim Constraint

For Each Constraint In Constraints
      If Constraint.StepName = StepName Then
            Set GetConstraint = Constraint
            Exit For
      End If
Next

End Function


preview.bmp
0
Anthony PerkinsCommented:
>> I edited the package with disconnected edit and run the package<<
Why are you editing it?  I said "see the names of each Task and Step".  I suspect your package is totally hosed now, I suggest you start again and recreate the package and then follow my instructions.

Incidentally, do not add the code I posted into an ActiveX Script Task as I suspect you have done here.  It is meant to go in the "ActiveX Script Step (Workflow) for the Task" you are trying to skip.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.