Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Issues with running a DTS package remotely from a VB app

Posted on 2008-06-17
3
Medium Priority
?
314 Views
Last Modified: 2013-11-30
Hello, I am having a issue with running a DTS package from my VB app when the VB app is not on the DB server.  

Detailed:
DB:  MSSQL 2000
VB:  2008

The DTS package is a simple DTS that takes an excel file from a location on the DB server and loads the data into a MSSQL database table.  The version of SQL Server is 2000.  The VB app is simply running the DTS package when a button is pushed.  If the VB app resides on the DB server, the DTS runs succesfully, however once we move the VB app off of the server and onto a remote machine we recieve the error that the location where the excel file resides is invalid.

I have verified the remote user and remote PC has access to the directory where the excel file resides.  I have shared out the directory to verify all users have access to it.  I have also changed the DTS package to use a UNC path for the excel file location instead of the physical drive.  But I still have the issue.

Thanks for any help you can provide.
----- Run Package
Dim oPKG As DTS.Package, oStep As DTS.Step
        oPKG = New DTS.Package
 
        Dim sServer As String, sUsername As String, sPassword As String
        Dim sPackageName As String, sMessage As String
        Dim lErr As Long, sSource As String, sDesc As String
 
        ' Set Parameter Values
        sServer = "SERVER"
        sUsername = "USER"
        sPassword = "PASSWORD"
        sPackageName = "PACKAGE NAME"
 
        ' Load Package
        oPKG.LoadFromSQLServer(sServer, sUsername, sPassword, _
            DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, , , , sPackageName)
 
        ' Set Exec on Main Thread
        For Each oStep In oPKG.Steps
            oStep.ExecuteInMainThread = True
        Next
 
        ' Execute
        oPKG.Execute()
 
        ' Get Status and Error Message
        For Each oStep In oPKG.Steps
            If oStep.ExecutionResult = DTS.DTSStepExecResult.DTSStepExecResult_Failure Then
                oStep.GetExecutionErrorInfo(lErr, sSource, sDesc)
                sMessage = sMessage & "Step """ & oStep.Name & _
                    """ Failed" & vbCrLf & _
                    vbTab & "Error: " & lErr & vbCrLf & _
                    vbTab & "Source: " & sSource & vbCrLf & _
                    vbTab & "Description: " & sDesc & vbCrLf & vbCrLf
            Else
                sMessage = sMessage & "Step """ & oStep.Name & _
                    """ Succeeded" & vbCrLf & vbCrLf
            End If
        Next
 
        oPKG.UnInitialize()
 
        oStep = Nothing
        oPKG = Nothing
 
        ' Display Results
        MsgBox(sMessage)
 
----- End Run Package
 
 
 
 
----Connection on Form Load
 
        strconnect = "Provider=SQLOLEDB;Data Source=SERVER_NAME;
                              Initial Catalog=DB_NAME"
        con.Open(strconnect, "USER", "PASSWORD")
        con.CommandTimeout = 0
-----

Open in new window

0
Comment
Question by:DCG_Developer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 11

Expert Comment

by:dready
ID: 21805276
0
 

Author Comment

by:DCG_Developer
ID: 21812257
Thanks for the info but I only switched to a UNC path bc I was recieving the error file path not found when using the physical path i.e. C:\Folder\xxx.xls to the excel file.  So I don't think it is a path issue but some sore of odd rights issue when calling a DTS package remotely using a VB application.
0
 

Accepted Solution

by:
DCG_Developer earned 0 total points
ID: 21981761
It ended up being a rights issue where for some reason the excel file was not inheriting the folders permissions, after doing a restart of the server it seems to have corrected this, thanks.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question