Using Data Transformation Services, part 5: Package Execution with ASP.NET - Write simple code to execute the package
(Page 4 of 5 )
Place a button on the ExecPackage.aspx design pane and write code in the click event of this button. When you go to the code page, add a reference to the DTS as follows:
Imports DTS
With the above imports statement added, you need to instantiate a new instance of the DTS package by using the New keyword as shown here. The intellisense utility should be active, so that you can see all the properties/methods of the Package that can be accessed, as shown here:
After instantiating a new package, you need to Load the package. Again, intellisense should come in handy. You can load the package saved in the SQL server, or from a repository. In this tutorial, the package saved on the SQL 2000 Server is loaded. Before you take this step, it may be advisable to get (rather, collect) a few of the arguments needed for the load method to work. Specifically you need the following arguments that are self-explanatory. When you save the package, you will be specifying some of these parameters, as we saw in our earlier tutorials.
All of this in one line !!!!
LoadFromSQLServer(ServerName as String,
[ServerUserName as String],
[ServerPassword as String],
[Flags as DTS.DTSSQLServerStoragFlags=
DTSSQLServerStorageFlags.DTSSQLStgFlag_Default],
[PackagePassword as String],
[PackageGuid as String],
[PackageVersionGuid as String],
[PackageName as String],
[ByRef pVarPersistStgOfHost as Object=""]
)
The PackageGuid and PackgeVersionGuid are created by the SQL Server when the package is saved. These may be seen by right clicking the Package-->Properties menu item in the DTS Designer which brings up the property page for the package as shown in the next picture. You may copy the "Guids" directly from this window and insert it into your code.
The package can now be executed by calling the Execute() method of the Package as shown here.
The code for executing the package is shown here. Make sure you put in the curly braces for the two "guids" correctly.
Private Sub Button1_Click(ByVal sender As Object, ByVal _
e As System.EventArgs) Handles Button1.Click
Dim pkg As New DTS.Package
pkg.LoadFromSQLServer("XPHTEK", _
"computer user", "xxxxxxxxx", _
DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, _
"computer user", _
"{FD8467BD-0FED-4F78-8F7A-C58A7E061AF7}", _
"{F987D202-6553-42AC-9CFE-41DC46E5EB90}", _
"ExcelPack3", "")
pkg.Execute()
Label1.Text = "Package Executed"
End Sub
Next: Verify the results >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
/ 12 
