October 10, 2002 at 10:31 am
I have a module created from a DTS job that copies all the tabels from one database to another. I would like to incorporate this DTS job into VB code "push button" and it will kick it off.
Any one have a sample on how to do this? or know of any good source materials combining VB and DTS?
Thanks
October 10, 2002 at 11:53 am
When installing SQL Server 7 and 2000, there are optional developer code sets that contain examples similar to what you want.
Of course, now that I think about it, I use the Enterprise and Standard versions, and couldn't swear that the same examples are in the other installs.
October 16, 2002 at 11:28 am
you need to add the SQLDMO.DLL to your project, and then try this code
Public Sub ExecutJob(JobName As String, Server As String)
Dim oJob As New SQLDMO.Job
Dim sqlServer As New SQLDMO.sqlServer
'Trusted connection
sqlServer.LoginSecure = True
sqlServer.Connect Server
'see if sql server agent is stoped
If sqlServer.JobServer.Status = SQLDMOSvc_Stopped Then
sqlServer.JobServer.Start
' while sql server agent not running waits
Do While sqlServer.JobServer.Status = SQLDMOSvc_Starting
Loop
End If
For Each oJob In sqlServer.JobServer.Jobs
If UCase(oJob.Name) = UCase(JobName) Then
'verify if job exists
t=1
Exit For
End If
Next
if t<>1 then
exit sub
end if
'start job
oJob.Start
Set oJob = Nothing
sqlServer.Close
Set sqlServer = Nothing
end sub
October 16, 2002 at 10:46 pm
Just finished doing a similar thing where user uploads a csv file via ASP and clicks OK. this sets the new file as dts text file source and inserts/updates tables based on contents.
I just saved the vb module code into a class and made an activeX dll com object. calling this object is as simple as
Dim o As dts_DB.dts_LoadCust
Set o = New dts_DB.dts_LoadCust
o.RunDTSPkg "filename.csv"
hope this helps
October 17, 2002 at 7:52 am
quote:
I have a module created from a DTS job that copies all the tabels from one database to another. I would like to incorporate this DTS job into VB code "push button" and it will kick it off.Any one have a sample on how to do this? or know of any good source materials combining VB and DTS?
Thanks
devereauxj,
I have a FreeWare vb DLL that I built and would be happy to give you... One of its requirements however is that you will need to save a copy of the DTS package as a file
you can then use this dll in a Win32 app or for ASP.
S.Steefel
"Change is inevitable, so enjoy the ride"
S.Steefel
"Change is inevitable, so enjoy the ride"
October 17, 2002 at 9:38 am
Short and Sweet
Add a reference to the Microsoft DTSPackage Object Library
Then I uses this code:
Private Sub Command1_Click()
Dim objDTSPackage As DTS.Package
Set objDTSPackage = CreateObject("DTS.Package")
objDTSPackage.LoadFromSQLServer "YOUR_SERVER", "USER", "PASSWORD", "DTSSQLStgFlag_UseTrustedConnection, , , , "PACKAGE_NAME"
objDTSPackage.Execute
Set objDTSPackage = Nothing
End Sub
The program will hang depending on how long it take you package to complete
Ken
October 17, 2002 at 6:22 pm
There is one more way to do this. After creating a package save it as VB file. It will create a .bas file which you can use in your app.
October 18, 2002 at 7:35 am
Another easy way of doing this is the following.
1. Click on the DTS JOB and select Schedule
2. Create a schedule ( don't matter because we will disable this later.....)
3. Now look in the SQLServerAgent and you will have a nice job with a nice name
4. Now the job has a name you can execute this job simple by executing the following SQL statement.....
sp_start_job @job_name = <your job name>
Simple as that. And the nice part of it is that nobody has to fool around with stuff they don't know. The programmers probably know already how to execute an SP so they can implement this immediately.
Success
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply