Results 1 to 5 of 5
  1. #1
    Join Date
    May 2006
    Posts
    4

    Question Unanswered: sql server 2000 DTS problem urgent help needed pls

    Hi every one
    I am new meber of this community with an urgent new problem with SQL server DTS process pls HELP!
    PROCESS DETAILS:

    My data base(db) is on main frame(flat file with hierarchical db format) I used vb script to get that db and split it in to three files so processing is easy after this I use dts PROCESS WITH EXECUTE SQL TASK in combination of some bulk insert scripts to pick these files from server directory and import it in to sql server tables. I know I could have use bulk insert task in dts but my formate file was giving me a problem while I try to develop it through that process so I ended up using tsql bulk insert statment with formate file in the EXECUTE SQL TASK .

    PROBLEM :
    My problem is I have to manually activate VB script where as I want this to be part of my DTS packet so it can be automated.

    I know there is Active x scripting task in DTS but have not used it can some one please tell me if this script can be put in to Active X scripting task with or with out any modifications and also what are the modifications I need to do so it can run before other DTS task (which picks the file and insert it in three different tables after format file application.

    this is the VB SCRIPT:Const

    ForReading = 1,ForWriting = 2
    Dim fso, SourceFile, TargetFile, retstring,Level1No, Level2No, Level3No, Level1Str, Level2Str, Level3Str

    Level1No = 0
    Level2No = 0
    Level3No = 0

    Set fso = WScript.CreateObject("Scripting.FileSystemObject")

    'Set SourceFile = fso.OpenTextFile("C:\Source\WGEE.NONX.GMM220D.UNLO AD.G00178888V00", ForReading)
    Set SourceFile = fso.OpenTextFile("C:\Source\DataNotepad.txt", ForReading)

    set l1 = fso.OpenTextFile("C:\Source\Level1.txt", ForWriting,True)
    set l2 = fso.OpenTextFile("C:\Source\Level2.txt", ForWriting,True)
    set l3 = fso.OpenTextFile("C:\Source\Level3.txt", ForWriting,True)


    Do While SourceFile.AtEndOfStream <> True
    retstring = SourceFile.ReadLine

    if Instr(1,retstring,"PACM",0) > 0 then
    Level1No = Level1No + 1
    Level1Str = Rpad(Level1No," ",10)
    l1.WriteLine(Level1Str & retstring)
    End if

    if Instr(1,retstring,"PACPRS",0) > 0 then
    Level2No = Level2No + 1
    Level2Str = Rpad(Level2No," ",10)
    l2.WriteLine(Level1Str & Level2Str & retstring)
    End if

    if Instr(1,retstring,"PACACC",0) > 0 then
    Level3No = Level3No + 1
    Level3Str = Rpad(Level3No," ",10)
    l3.WriteLine(Level1Str & Level2Str & Level3Str & retstring)
    End if

    Loop

    Function Rpad (MyValue, MyPadChar, MyPaddedLength)
    Rpad = MyValue & string(MyPaddedLength - Len(MyValue), MyPadChar)
    End Function

    THANKS ALL

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As your post is much more technical in nature than the traditional "Hello, world" post in the New Users and Introductions forum, I'm going to move the thread to the Microsoft SQL Server forum where it should get more attention.

    There is an Active-X scripting task in DTS, but for this kind of job I prefer to use an Execute Process to run CSRIPT.EXE for quick and dirty conversions like this.

    -PatP

  3. #3
    Join Date
    May 2006
    Posts
    4
    Please explain the solution u just mentioned. I don't know how to do that (CSRIPT.EXE ) and also can this be automated.

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    You can find information about CSCRIPT here:

    http://www.microsoft.com/resources/d....mspx?mfr=true

    Execute Process is a task type in DTS. You should be able to find it pretty easy in the toolbar on the left side. Why don't you try creating an active-x task also, selecting VBScript as the script type, and pasting your script into it. That will answer your question on whether your script will work fairly easily.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Good point Derrick... I'd assumed that they'd already tried using an ActiveX script task and found that it hadn't worked. It would be a good exercise to determine if the script will run as an ActiveX task, since that has the fewest possible moving pieces compared to running CSCRIPT.EXE if the script wasn't written to run there.

    -PatP

Posting Permissions

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