?
Solved

error importing a files in mssql 2000

Posted on 2008-11-19
10
Medium Priority
?
390 Views
Last Modified: 2013-11-30
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
Comment
Question by:eaweb
  • 4
  • 4
9 Comments
 

Author Comment

by:eaweb
ID: 22997250
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
 
LVL 15

Expert Comment

by:dbbishop
ID: 22998780
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 23000895
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:eaweb
ID: 23003002
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 23023335
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
 

Author Comment

by:eaweb
ID: 23185711
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 23190536
Right click on the diagram and select "Disconnected Edit ..." you can then see the names of each Task and Step
0
 

Author Comment

by:eaweb
ID: 23363829
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 23369347
>> 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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question