Execute DTS Package in SQL Server 2000 from VB.NET Application
-
We are looking for a way to fire a DTS package in SQL Server 2000 from within a VB.NET application. Does anybody know if this is possible?
-
You can do it from ASP so I assume the same is true of .NET. I am investigating this myself today, but to help you out the DTS package object is a COM object with the ProgID of 'DTS.Package'.
In ASP:
dim myDTS
set myDTS = server.createobject("DTS.Package")
There is a LoadFromSQLServer method which allows you to load an existing DTS package.
Hopefully I can give you more info if needed once I have done the work myself.
-
OK Its Easy!
Add a reference to the COM Object 'Microsoft DTSPackage Object Library'.
DLL = C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtspkg.dll
Import this into your class in .NET :
Imports DTS
Then create the code to create and execute the object :
Dim
objDTSPackage As DTS.Package
objDTSPackage.LoadFromSQLServer("SERVERNAME", "USER", "PASSWORD", "", "", "", "", "PACKAGENAME", "")
objDTSPackage.Execute()
Obviously replace the parameters in bold with the appropriate info.
There you go.....easy as!
-
Charles! Thanks for your help with that. I will test it today and let you know how it works out for us!
Dave Lucre
-
That will work great if you are either running the .NET application on the server or wish to run the DTS package on the remote machine where the .NET app is running. If you want to run the app from another machine, but start and execute the package on the SQL Server, it's a little more of a pain.
-
Now I am curious
We haven't got a DTS package to use for this yet, so it has not been done - but why would the app need to be running on that machine? I would have assumed that the DTS object would connect to the SQL server and tell it the package name to run, and it would just go?
-
Well, it works fine for me using an IP address as the server name, it picks up and executes the DTS package on our hosted server...
-
I have a similar question....
I was able to execute a dts package from vb.net. The package executes a stored procedure passing in a global variable.
How can I capture the return value of this stored proc in the .net. Or how do I set an output parameter with some error text in the dts and capture that in the .net?
Thanks,
Ninel
-
I am not sure about your needs but to run DTS package through a stored proc you either use DTSRUN.exe or XP_CMDSHELL which is SQL Server Agent dependent. Try the links below for DTSRUN.exe sample code and XP_CMDSHELL configurations with permissions. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp
http://www.sqlteam.com/item.asp?ItemID=19595
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie -
The other option for remote server execution is to execute package as Job, with sp_add_job, sp_add_jobserver, sp_add_jobstep
Dave
-
Hi, I'm baffled..
following the above examples, I created a package and it executes fine from .Net
However, within the packages, there are active x tasks that contain code eg:
set DTSGlobalVariables("MyConn").value = _
CreateObject("ADODB.Connection")
set conn = DTSGlobalVariables("MyConn").value
conn.provider="sqloledb"
conn.open "169.264.92.264", "sa", "sa"
conn.DefaultDatabase = "DI2"
generates an error,"Server not found or Authentication failed"
similarly
sExportPath = DTSGlobalVariables("ExportFolder").value
Set objfso = CreateObject("Scripting.FileSystemObject")
set objfsofolder = objfso.getfolder(sExportPath)
generates an error,".. can not find path" even though the path is set as \\[IPAddress\folder .
Executing from Enterprise Manager, on the app server, where the app resides as well, poses no problem....the minute it is executed from code the error occurs.
It is as if the DTS.Package, saved on the server, loses contact with the outside world (or is it inside) the minute it gets executed from .Net. e.g.
sExportPath = DTSGlobalVariables("ExportFolder").value
Set objfso = CreateObject("Scripting.FileSystemObject")
Line 12 = set objfsofolder = objfso.getfolder(sExportPath)
Error
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Path not found
Error on Line 12
Step Error code: 800403FE
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
For what it's worth, Ive developed an error routine for displaying the error messages in a msgbox on the Gui, should anybody be interested
-
2 friend,
I have already added dtspkg.dll, dtspump.dll and custtask.dll into my project but it seems not ready to run. It fires an err:
"Unable to cast COM object of type 'System.__ComObject' to Interface type 'DTS.CustomTask'. This operation failed because the QueryInterface call the COM component for the Interface with IID
'{10020904-EB1C-11CF-AE6E-00AA004A34D5}' failed due to the following error: No such interface supported (Exception from HRESULT:0x80004002(E_NOINTERFACE))"
Here is my code for deployment:
Imports DTS
Public Class Form1
Dim strCnn As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=test;Data Source=T109-MYLTK"
'for package.sourceCnn.properties
Dim serverName As String = "T109-MYLTK"
Dim userName As String = ""
Dim passWord As String = ""
Dim souCatalog As String = "pubs"
Dim desCatalog As String = "test"
'for pumpTask sample only
Dim pumpTaskSql As String = "select * from employees"
Dim pumpTaskDesTable As String = "dim_employee"
Dim oPackage As DTS.Package
Private Sub cmdDoDTS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDoDTS.Click
'define package.properties for a new package
oPackage = New DTS.Package
createPackage(oPackage, "myFirstDTSApp", "Pump data from pubs to test.dim_employee")
'define the connections for package
addConnection("cnn_1", 1, serverName, "", "", souCatalog)
addConnection("cnn_2", 2, serverName, "", "", desCatalog)
'define the tasks for package
addDataPumpTask("task_1", "pumpTask", pumpTaskSql, pumpTaskDesTable)
'define the steps of package for tasks run
addStepTask("step_1", oPackage.Tasks.Item("pumpTask").Name)
'save the package
oPackage.SaveToSQLServerAs("my.NET", "local")
'execute the package
oPackage.Execute()
End Sub
Private Sub createPackage(ByRef oPackage As DTS.Package2, ByVal name As String, ByVal des As String)
With oPackage
.Name = name
.Description = des
.WriteCompletionStatusToNTEventLog = True
.FailOnError = False 'Default = FALSE
.PackagePriorityClass = DTS.DTSPackagePriorityClass.DTSPriorityClass_Normal 'Win32 Process Priority
.MaxConcurrentSteps = 4 'Default = 4
.LineageOptions = DTS.DTSLineageOptions.DTSLineage_None
.UseTransaction = True
.TransactionIsolationLevel = DTS.DTSIsolationLevel.DTSIsoLevel_ReadCommitted 'Default = ReadCommitted
.AutoCommitTransaction = True
.RepositoryMetadataOptions = DTS.DTSRepositoryMetadataOptions.DTSReposMetadata_Default
.UseOLEDBServiceComponents = True 'Default = TRUE
End With
End Sub
Private Sub addConnection(ByVal name As String, ByVal id As Integer, ByVal serverName As String, ByVal userName As String, ByVal passWord As String, ByVal catalog As String)
Dim oCnn As DTS.Connection
oCnn = oPackage.Connections.New()
With oCnn
.Name = name
.ID = id 'The unique connection ID
.DataSource = serverName 'serverIP
.UserID = userName
.Password = passWord
.UseTrustedConnection = True 'Support for integrated security
.ConnectionTimeout = 30 'The default time-out value is 60 seconds
.Catalog = catalog 'The database name
End With
oPackage.Connections.Add(oCnn)
oCnn = Nothing
End Sub
Private Sub addDataPumpTask(ByVal taskId As String, ByVal taskName As String, ByVal sql As String, ByVal desTable As String)
Dim oTask As DTS.Task
Dim oPumpTask As DTS.DataPumpTask
'oTask = oPackage.Tasks.New(taskId)
oTask = oPackage.Tasks.New("DTSDataPumpTask")
'oTask = oPackage.Tasks.New("Task")
oTask.Name = taskName
oPumpTask = oTask.CustomTask
With oPumpTask
.SourceConnectionID = 1
.SourceSQLStatement = sql
.DestinationConnectionID = 2
.DestinationObjectName = desTable
End With
addTransformationToTask(oPumpTask, "test.dim_employee_pumpTest.tran", "transform")
oPackage.Tasks.Add(oTask)
oTask = Nothing
End Sub
Private Sub addTransformationToTask(ByRef oPumpTask As DTS.DataPumpTask2, ByVal tranId As String, ByVal name As String)
Dim oTransformation As DTS.Transformation
oTransformation = oPumpTask.Transformations.New(tranId)
With oTransformation
.Name = name
.TransformFlags = 512 'DTSTransformFlag_AllowLosslessConversion
End With
oPumpTask.Transformations.Add(oTransformation)
End Sub
Private Sub addStepTask(ByVal name As String, ByVal taskName As String)
Dim oStep As DTS.Step
oStep = oPackage.Steps.[New]
With oStep
.Name = name
.TaskName = taskName
End With
oPackage.Steps.Add(oStep)
oStep = Nothing
End Sub
End Class
Do you have any idea?
thanks
khanhmy
-
Sometimes an easier solution exists - maybe here too.
If one needs to execute a DTS package, why not just have your .NET app set a flag in a table that the DTS package itself reads to determine whether to "execute itself"?
The DTS package can be scheduled to run every 5 minutes and it will check that flag to see if it needs to execute the rest of the steps (i.e., move data, update data, whatever).
Why bother with all that coding in .NET?
Anyway, just a suggestion.
Good luck.
-
Did you ever get this resolved? I execute DTS packages from my .NET application all of the time without any problems.
-
how to i call objDTSPackage.Execute() with all parameters to pass i have don't know how pass i all parameter of this method so tell me please..!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply