Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    23

    Unanswered: a visual basic script to DTS

    Hello I am trying to convert a VB script someone wrote for my company into a DTS package that will fire automatically whenever the file is updated on the server. I am using vb.net for the firing of the DTS package, but I am having trouble writting the DTS package itself. This is what the code looks like now

    Code:
    If fso.FileExists(cSource) Then
            With cne
                .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                    & "Data Source=" & cSource & ";" _
                    & "Extended Properties=Excel 8.0"
                .Open
                cSQL = "SELECT * FROM " & sSheet
                Set rse = .Execute(cSQL)
            End With
            
            While Not rse.EOF
                i = i + 1
                Me.Label1.Caption = "Updating Sold: " & i
                Me.Refresh
                If Not IsNull(rse("VEHICLE-STOCK-NO###")) Then
                   sSearch = "'" & rse("VEHICLE-STOCK-NO###") & "'"
                   cSQL = "UPDATE Inventory SET SoldDate = '" & rse("FNLZ-DT") & "', "
                   cSQL = cSQL & "LastUpdate = '" & Now & "' WHERE StockNo = " & sSearch
                   cSQL = cSQL & " AND SoldDate IS NULL"
                   cn.Execute (cSQL)
                End If
                rse.MoveNext
            Wend
            rse.Close
            cne.Close
        End If
    So basically I have an excel file that is downloaded once a week and then I run this program, well I am having some toubles with making it a DTS

    I need to convert this into an active X transfermation code I think
    Code:
    If Not IsNull(rse("VEHICLE-STOCK-NO###")) Then
                   sSearch = "'" & rse("VEHICLE-STOCK-NO###") & "'"
                   cSQL = "UPDATE Inventory SET SoldDate = '" & rse("FNLZ-DT") & "', "
                   cSQL = cSQL & "LastUpdate = '" & Now & "' WHERE StockNo = " & sSearch
                   cSQL = cSQL & " AND SoldDate IS NULL"
    end if
    Thanks a lot

    Damian
    "Everything is possible, somethings are just less likely then others"

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Search SQLDTS website for code examples and more information.

    HTH
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

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