Using Data Transformation Services, part 5: Package Execution with ASP.NET

In previous tutorials, step-by-step methods of creating a DTS package with ImportExportWizard, as well as using the DTS Designer, have been described in considerable detail. The scripting involved in data transformations was also described.

The main focus of this tutorial is to guide the user in executing the DTS packages designed and saved on the SQL Server from ASP.NET using Visual Studio 2003. In order to complete the tasks in this tutorial, the reader is advised to review the previous tutorials on this topic available online.

We will follow the steps indicated here:

Part A:

• Create a dts package
• Create an SQL Connection
• Create an MS Excel Connection
• Create a Transform Data Task
  This task populates a table in SQL server with data in an excel worksheet
• Configure the task
• Save the package and execute
• Verify the results of transformation

Part B:

• Create a Web project using VS 2003
• Add references to DTS COM Assemblies
• Write simple code to execute the package
• Verify the results

Part A:

We will completely skip this part, except for showing some of the important specifics that are needed in executing the package via ASP.NET using the VS 2003 IDE. Please refer to the earlier tutorials located here for details.

Creating the package details of the two connections

There are two connections, one to SQL Server 2000 and the other to the Excel worksheet. All that is needed to set up a connection is to drag the connection control from connection collection and drop it in the designer.

The next two pictures show the two connections. The first one is the Source (Excel Worksheet), and the second is the Destination (SQL 2000 Server).

After adding a transform data task, it is configured as shown. Please refer to previous tutorials for the details. This picture shows the Source tab of the Transform Data Task.

The next picture shows the Destination tab of the Transform data Task.

The following picture shows the Transformations tab of the Transform Data Task. No fancy transformation is attempted here, as the main focus is executing the package with ASP.NET

The finished package is as shown in the following picture, saved under the name ExcelPack3. The package can be executed in several ways as discussed in earlier tutorials. One obvious way is to click on menu item Package in the editor, and click on Execute. This action would copy the the Excel spreadsheet to a table in a SQL 2000 Server database.

Part B: Create a Web project

In the Visual Studio 2003 IDE, go to File-->New--> Project. This opens up the New Project Window, where you choose to create an ASP.NET Web application (named DTS in this tutorial). Rename the WebForm1.aspx to some chosen name (ExecPackage in this tutorial). Your Solution/Project would appear as shown here.

Add references to DTS COM Assemblies

In order to access the DTS properties and methods, we need to add appropriate references. Right click the References tab and click on Add reference.... This brings up the Add Reference window, where you can establish references to .NET, COM, or Projects. DTS is to be accessed as a COM interface, so go to the COM tab. Scroll down to see whether DTS related assemblies are available. If they are, you can select them and add them to the References node.

After adding this, you will be able to access the properties and methods of DTS objects. If not, you use the Browse... button to locate those files and add them to the references of your application. They should be found in the Program Files folder at a location similar to the following: C:\Program Files\Microsoft SQL Server\80\Tools\Binn. This is shown in the following picture.

There are two files that may be needed, dtspkg.dll and dtspump.dll. In this tutorial we will be using only the dtspkg.dll file. The dtspump.dll file is needed mostly for leveraging ActiveX scripting support.

With these references added, your project would appear as shown in the next picture. Also shown is the object browser for the project. You can see the Interop.DTS and Interop.DTSPUMP assemblies in the listing.

Write simple code to execute the package

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

Verify the results

After writing this code, build the solution and open the ExecPackage.aspx in the IE browser and click the button. You can now open up the SQL Server's Enterprise manager and verify that the Package was indeed executed (as shown in the next picture). The label placed on the ExecPackage.aspx page will show the message after successful execution. You can wrap up the code between try..catch to trap errors.

Summary

The most important step in executing the DTS packages with Visual Studio is to add the proper references to the COM assemblies. These may need to be added from the tools folder in the SQL Server installation files. It is also important to collect all the arguments, permissions, and so forth needed for loading the package before attempting to execute the package. Make sure the Load statement is all in one line, without extra white spaces. We will look at the DTS API in more detail in the next tutorial.

blog comments powered by Disqus