Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unanswered: dts sql server 2000

    Hi!
    I have a problem with a DTS that would have to test, first of all, if the input file is valued or less. If it is not valued it interrupts the execution else goes ahead. The path of the file it is in a global variable.

    Someone has an example of a ActiveX Script that make my case?

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    What do you mean by "the input file is valued or less" ?
    If you do not have SQL Server Books Online (BOL) installed - please do so. The majority of questions asked in the SQL Server forum could be eliminated if people had access to bol. BOL

  3. #3
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    are you talking about 0kb input file or input file with some data in it?

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    That is what I was wondering as well. If that is the case you can use the fso object.
    If you do not have SQL Server Books Online (BOL) installed - please do so. The majority of questions asked in the SQL Server forum could be eliminated if people had access to bol. BOL

  5. #5
    Join Date
    Feb 2004
    Posts
    5

    0 kb

    the file is empty, 0 kb.

    What is fso object?

  6. #6
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    FSO is file system object

    this activex code will work for you
    =====================================
    Function Main()
    Dim fs, MyFile
    Dim FileLocation
    Dim FileName
    Dim FileInfo

    FileName = "test.txt"
    FileLocation = "c:\test\"
    FileInfo = FileLocation & FileName

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set MyFile = fs.GetFile(FileInfo)

    If MyFile.Size <> 0 Then
    set fs=nothing
    Main = DTSTaskExecResult_Success
    exit function
    Else
    Main = DTSTaskExecResult_Failure
    exit function
    End If

    End Function
    ======================================
    -rohit

  7. #7
    Join Date
    Feb 2004
    Posts
    5

    ??

    But my file path:

    FileName = "test.txt"
    FileLocation = "c:\test\"
    FileInfo = FileLocation & FileName

    is in a global variable.

    how I can make?

  8. #8
    Join Date
    Feb 2004
    Posts
    5

    ...

    but my DTS does not have to go in error when the file size = 0, simply must exit without to execute the successive task.

  9. #9
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    to access global variable from inside an activex code

    FileName = DTSGlobalVariables("GLOBAL_VARIABLE_FOR_FILE_NAME" ).Value
    FileLocation = DTSGlobalVariables("GLOBAL_VARIABLE_FOR_LOCATION") .Value
    FileInfo = FileLocation & FileName

    this activex code will be the first step in your package and on success of this rest of the process will follow. In this case if the file is 0 kb then the package will fail.

    In case you dont want the package to fail you will have to make 2 packages. First one with an activex step having code pasted below, and the second package with process whatever you wanted to do in case file has size greater than 0kb. You will have to run the first package everytime, in case the filse size is more than 0kb it will execute the second package (that does all the transformations), else the first package will finish still being a success.

    activex code for this
    ========================================
    Function Main()
    Dim fs, MyFile
    Dim FileLocation, FileName, FileInfo
    Dim SecondPackage

    FileName = DTSGlobalVariables("GLOBAL_VARIABLE_FOR_FILE_NAME" ).Value
    FileLocation = DTSGlobalVariables("GLOBAL_VARIABLE_FOR_LOCATION") .Value
    FileInfo = FileLocation & FileName

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set MyFile = fs.GetFile(FileInfo)

    If MyFile.Size <> 0 Then
    set fs=nothing
    Set SecondPackage = CreateObject("DTS.Package")
    SecondPackage.LoadFromSQLServer "SERVER_NAME", "USER_ID", "PASSWORD", , , , , "SECOND_PACKAGE_NAME"
    SecondPackage.Execute
    SecondPackage.Uninitialize()
    exit function
    End If

    Main = DTSTaskExecResult_Success

    End Function
    ======================================

  10. #10
    Join Date
    Feb 2004
    Posts
    5

    thanks

    Thank you so much.

    Is ok. good

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •