Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Location
    London
    Posts
    2

    Unanswered: Calling DTS package from VB/Ms-Access 2000

    Hi All,
    My name is kanishk. I am facing some issue in calling the DTS package from Visual Basic code.
    The brief history of issue is :
    We have a application which uses Ms-Access as front end and MS-Sql server 2000 as backend. Perviously we has MS-Access 97 version and MS-Sql 2000 .In this comination the DTS package was working fine.
    Now We have changes the fornt end to MS-Access 2002. Here the DTS package is not working .

    Can you please help me out to get the proper syntax to call the DTS package from VB code.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    What's your current syntax and what SP you have on the server?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Sep 2004
    Location
    London
    Posts
    2

    DTS Package

    The DTS package is called from VB code. There is no SP at server side for DTS calling.

    The Syntax in MS-Access(VB code )in version 97 is :

    Sub RunDTS(strServer As String, strUser As String, strPassword As String, strDTS As String)
    Dim dtsp As New DTS.Package

    dtsp.LoadFromSQLServer _
    ServerName:=strServer, _
    ServerUserName:=strUser, _
    ServerPassword:=strPassword, _
    PackageName:=strDTS

    dtsp.Execute
    DoEvents

    End Sub

  4. #4
    Join Date
    Oct 2003
    Location
    Cape Town
    Posts
    38
    First make sure you have : Microsoft Dtspackage Object library activated in visual basic references.

    Here is a visual basic code template :

    Dim cn as ADODB.connection
    Dim oPKG As DTS.Package
    dim i as integer

    SET cn = currentproject.connection

    Set oPKG = New DTS.Package

    if cn.Properties("Integrated Security") <> "SSPI" Then
    'oPKG.LoadFromSQLServer cn.Properties("Data Source"), cn.Properties("USER ID"), cn.Properties("PASSWORD"), DTSSQLStgFlag_Default, , , , "DtsName"
    Else
    'oPKG.LoadFromSQLServer cn.Properties("Data Source"), ,, DTSSQLStgFlag_UseTrustedConnection, , , , "DtsName"
    End If


    On oPKG.FailOnError GoTo HandleErr

    ' ------------------------ Facultatif , Use only in case you want to determine source file location at runtime

    i = 1
    For i = 1 To oPKG2.Connections.Count
    FileSourceName = oPKG.Connections.Item(i).DataSource

    oPKG.Connections.Item(i).DataSource = Me.FileLocation '( source file full name )

    Next i
    ' ---------------------

    oPKG.Execute
    oPKG.UnInitialize
    Set oPKG = Nothing

    ExitHere:
    Exit Sub
    HandleErr:
    MsgBox "Error " & Err.Number & vbCrLf & vbLf & Err.Source & vbCrLf & vbLf & Err.Description, vbCritical
    Resume ExitHere
    Benone

Posting Permissions

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