December 12, 2003 at 2:12 am
Is it possible to run a DTS Package from a access project? I would like to create a form in access and execute a dts package when a use clicks on a button.
Can anyone point me in the right direction?
Thanks
Karl
December 12, 2003 at 6:52 am
1. set a job that runs the package
2. make sure to disable the job so that it does not run on a schedule
3.Then run: exec sp_start_job
* Noel
December 15, 2003 at 9:30 am
If you need to you can create DTS packages with VBA and run them from your app. Saving your package as a visual basic file will give you all the VB code you need, which you can modify as needed. Note that there is a lot of superfluous VB code included, most of which simply explicitly states DTS default values. I get rid of as much as I can.
You’ll need to add “MicrosoftDTSPackage Object Library” to your references. Here’s an example that creates an ExecuteSqlTask package which lets me loop thru and truncate a bunch of a SQL Server tables:
Public Const gstrGalProvider As String = "SQLOLEDB"
Public Sub TruncateTableDts(strGalTableName As String
, strSqlServerName As String
, strGalDbName As String
, strSqlLoginName As String
, strSqlPassword As String)
'--- Procedure uses DTS to truncate a SQL Server table;
'--- strGalTableName contains the name of the table to be truncated
On Error GoTo DTSError
'--- Declare DTS object variables
Dim oPackage As DTS.Package
Dim oConnSource As DTS.Connection
Dim oStep As DTS.Step
Dim oTask As DTS.Task
Dim oExecuteSQLTask As DTS.ExecuteSQLTask
'--- Create the new DTS package
Set oPackage = New DTS.Package
oPackage.Name = "TruncateGalTable"
'--- Create DTS connection to SqlServer with format:
'--- PROVIDER=SQLOLEDB; SERVER=strSqlServerName;
'--- UID=strSqlLoginName; PWD=strSqlPassword
Set oConnSource = oPackage.Connections.New(gstrGalProvider)
oConnSource.Name = "TruncateTable"
oConnSource.ID = 1
oConnSource.DataSource = strSqlServerName
oConnSource.UserID = strSqlLoginName
oConnSource.Password = strSqlPassword
oPackage.Connections.Add oConnSource
'--- Create a single DTS step object
Set oStep = oPackage.Steps.New
oStep.Name = "TruncateTable"
oStep.TaskName = "TruncateTable"
oPackage.Steps.Add oStep
'--- Create a single DTS task object and custom task object;
'--- syntax specifically creates of a DTS "Execute SQL" task
Set oTask = oPackage.Tasks.New("DTSExecuteSQLTask")
oTask.Name = "TruncateTable"
Set oExecuteSQLTask = oTask.CustomTask
oExecuteSQLTask.Name = "TruncateTable"
oExecuteSQLTask.SQLStatement = _
"Truncate Table " & strGalDbName & ".dbo." & strGalTableName
oExecuteSQLTask.ConnectionID = 1
oPackage.Tasks.Add oTask
'--- Execute package and clean up objects
oPackage.FailOnError = True
oPackage.Execute
Set oExecuteSQLTask = Nothing
Set oTask = Nothing
Set oStep = Nothing
Set oConnSource = Nothing
Set oPackage = Nothing
Exit Sub
'--- DTS error handler
DTSError:
Dim lngErrNum As Long
Dim strSource As String
Dim strDescr As String
Dim strMessage As String
Dim strMsgBox As String
For Each oStep In oPackage.Steps
If oStep.ExecutionStatus = DTSStepExecStat_Completed Then
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
oStep.GetExecutionErrorInfo lngErrNum, strSource, strDescr
strMessage = strMessage & vbCrLf & _
"Step " & oStep.Name & " failed, error: " & _
lngErrNum & vbCrLf & strDescr & vbCrLf
End If
End If
Next
strMsgBox = "Package failed, error: " & Err.Number & vbCrLf & _
Err.Description & vbCrLf & _
strMessage
MsgBox strMsgBox, vbExclamation, oPackage.Name
End Sub
Hope this helps
Regards,
k2
December 15, 2003 at 5:42 pm
You can also use VBA and ADO to execute a stored procedure that calls the DTS package. Here's an example of a stored procedure that uses the system stored procedure xp_cmdshell and the dtsrun.exe command to execute the package:
CREATE PROC ap_CallDTS
AS
SET NOCOUNT ON
DECLARE @cmd varchar(255)
DECLARE @error int
--command string
SET @cmd = 'dtsrun.exe /S MYSQLSERVER /U myusername /P mypassword /N MyDTSpackageName'
--Exec DTS package
EXEC @error = master..xp_cmdshell @cmd
Return @error
December 16, 2003 at 1:44 am
I can get the sp_start_job idea to work.
I would like to learn more about the vba options as this is obviously more powerfull as I need to move the code to various severs, I assume i can change the connection info in code as opposed to recreating the dts packages and connections on all the different servers.
My question is, where can I get a simple (like me) guide on how to create create a working vba program that runs a dts job/ script. I am new to vba and learning as I go!
Thanks
Karl
December 16, 2003 at 12:03 pm
Try here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtspapps_21rn.asp
Or you can search around here:
regards
k2
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply