• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

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.
0
eaweb
Asked:
eaweb
  • 4
  • 4
1 Solution
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now