Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2003
    Location
    Argentina
    Posts
    11

    Unanswered: DTS Status in Visual Basic

    Hello, how do i get the package status of a DTS saved like a visual basic module, because when i execute the visual basic code everything freezes and i have no idea of the progress of the data_pump.

    what do i have to use to see how many rows were copied like the dialog that apears when you execute the dts from the enterprice manager?

    Thanks...

  2. #2
    Join Date
    Sep 2003
    Posts
    212

    Re: DTS Status in Visual Basic

    Originally posted by ArgenSQL
    Hello, how do i get the package status of a DTS saved like a visual basic module, because when i execute the visual basic code everything freezes and i have no idea of the progress of the data_pump.

    what do i have to use to see how many rows were copied like the dialog that apears when you execute the dts from the enterprice manager?

    Thanks...
    you cant. Once saved as a VB file you cnat find out whats happening. thats why u should save the dts package as Structured storage file, and then open that package elsewhere as per required.

  3. #3
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: DTS Status in Visual Basic

    if you declare your package with WithEvents

    "Private WithEvents goPackage As DTS.Package2"

    you can use the OnProgress event

    Private Sub goPackage_OnProgress(ByVal EventSource As String, ByVal ProgressDescription As String, ByVal PercentComplete As Long, ByVal ProgressCountLow As Long, ByVal ProgressCountHigh As Long)
    ...

    theres others events too...


    But to have the numbers of rows, i guess you'll have to do 2 counts
    Before the loading : Select Count(*) NbBof From Table1
    Store NbBof in a variable
    After the loading : Select Count(*) NbEof From Table1
    Store NbEof in a variable

    And the magic : NbEof - NbBof = Numbers of rows inserted


    I can't do better for you

  4. #4
    Join Date
    Sep 2003
    Posts
    212
    Or you could write to a log file showing you the progress of ur package

  5. #5
    Join Date
    Oct 2003
    Location
    Argentina
    Posts
    11
    Thanks Karolyn, that's exactly what I was looking for,

    but I dont know how to use it, because if the dts package is on a Bas module withevents can't be used, so I moved the code to a form, and when I declare the variable withevents, VB6 hangs up but really bad,
    "EXCEPTION_ACCESS_VIOLATION" and everything closes, this happens only when I put "withevents" on the declaration.

    Any help?? please...

    Bye, A.

  6. #6
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    witch version of SQL Server are you using ???

  7. #7
    Join Date
    Oct 2003
    Location
    Argentina
    Posts
    11
    Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

  8. #8
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    Did you reference the "Microsoft DTSPackage Object Library"
    from the SQL Server 2000 version ?

    Did you have before the older version 7.0 of SQL Server ?

  9. #9
    Join Date
    Oct 2003
    Location
    Argentina
    Posts
    11
    I have reference to this:
    -Microsoft DTSDataPump Scripting Object Library
    -Microsoft DTSPackage Object Library
    -Microsoft DTS Custom Tasks Object Library

    And never had or used sqlserver 7.

    The problem isn't with the references because the program works great without the Withevents statement, I'm really lost with that, any help?, here is the code with the declarations:
    Code:
    Option Explicit
    
    Public WithEvents goPackage As DTS.Package2
    Public WithEvents goPackageOld As DTS.Package
    The code hangs up in the line 'goPackage.Execute'.
    The error is this:
    Code:
    Error -2147221499 (80040005)
    Provider generated code execution exception:
    EXCEPTION_ACCESS_VIOLATION
    Bye, please help...

  10. #10
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    Maybe this infos from MSDN will help you understand your PB :
    ------------------------------------------------------------------------

    SYMPTOMS
    A Data Transformation Services (DTS) package that includes a "Transfer SQL Server Objects" task might fail with an access violation if the destination server is unnamed. If the package is run from the Enterprise Manager the following error message occurs:

    Need to run the object to perform this operation.
    (SQL-DMO) Code execution exception :
    Exception_Access_violation
    If you run the package by using the dtsrun utility, the following error message occurs:
    DTSRun OnError: Transfer SQL Server Objects, Error = -2147221499
    (80040005) "
    This problem only applies to Microsoft SQL Server 7.0 Service Pack 2.

    CAUSE
    The destination does not have the local server name set correctly.

    To verify the server name run the following query from Query Analyzer:

    select @@servername
    If the query returns "Null" the local server name is not set correctly.


    WORKAROUND
    If the server name exists, but is not identified as the local server, then you must run the following queries to resolve the issue:

    sp_dropserver 'servername'
    go
    sp_addserver 'servername', local
    If the server name does not exist you must run the following query to resolve the issue:

    sp_addserver 'servername', local
    For the name change to become effective, you must stop and restart the SQL Server service.

  11. #11
    Join Date
    Oct 2003
    Location
    Argentina
    Posts
    11
    I found the answer!!

    http://support.microsoft.com/support.../Q221/1/93.ASP

    Here is explained everything about events and DTS in Visual Basic, and explain how to solve the error I had.

    Bye,
    Thanks to all for your help!

Posting Permissions

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