executing SQL 2000 package from vb.net

Results 1 to 2 of 2

Thread: executing SQL 2000 package from vb.net

  1. #1
    Join Date
    Dec 1969

    Default executing SQL 2000 package from vb.net

    Below is my code which is attempting to execute a package on SQL Server 2000. The first part of this code is responsible for uploading and renaming a file to the file system of the server which works great. Problem is I am not receiving any type of error when attempting to execute the package. <BR><BR>The package runs correctly if run from SQL 2000 so I am not sure why the below isn&#039;t executing the package. Any ideas? <BR><BR>Public Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click <BR> Dim ServerFileName As String <BR> Dim strStandardFileName As String <BR> Dim numUserID As Int16 <BR> Dim extension As String <BR><BR> numUserID = 1 <BR><BR> SqlConnection1.ConnectionString = "data source=SCHMIED1;initial catalog=ThoroClear;persist security info=False;user id=SA;password=password;persist security info=True;workstation id=QBERT;packet size=4096" <BR> SqlConnection1.Open() <BR><BR> Try <BR> ServerFileName = Path.GetFileName(File1.PostedFile.FileName) <BR> extension = Path.GetExtension(File1.PostedFile.FileName) <BR> strStandardFileName = numUserID & "Sett" & Format(Now(), "MMddyyyy") & extension <BR> File1.PostedFile.SaveAs("\Schmied1CDriveUploadedFi les\" & strStandardFileName) <BR> &#039;Run DTS Package - IMPORT File <BR><BR> Dim dtsp As New DTS.Package() <BR> dtsp.LoadFromSQLServer( _ <BR> ServerName:="Schmied1", _ <BR> ServerUserName:="sa", _ <BR> ServerPassword:="password", _ <BR> PackageName:="Import_Excel") <BR> dtsp.Execute() <BR> SqlConnection1.Close() <BR> <BR> Label1.Visible = True <BR> Label1.Text = "File has successfully imported" <BR> Catch err As Exception <BR> Label1.Visible = True <BR> Label1.Text = "Error: <BR>" <BR> Label1.Text &= err.Message <BR> End Try <BR> End Sub

  2. #2
    Join Date
    Dec 1969

    Default RE: executing SQL 2000 package from vb.net

    DTS is difficult to run from "outside" SQL so I suggest<BR>1) schedule the DTS (puts in SQL Server Agent)<BR>2) in schedule you will find a &#039;nice long string&#039; <BR>3) from ASP Set DB USE MSDB<BR>4) Sql= " EXEC sp_start_job @job_id =&#039;" & nice_long_string & "&#039;"<BR><BR>5) Oh yes make Sure file has finished loading before trying to run this

Posting Permissions

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