<%@ Page %>

Automate Data Export using DTS and ADO.NET


Most of the Organizations have requirement of maintaining all corporate data at one centralized place. Since the enterprise is having different kinds of system it is quite hard to achieve the centralization. From SQL Server 7.0 onwards, Microsoft provided the drag and drop facility for automation to DBA. The tool that SQL Server provides is DTS (Data Transformation Services).

In this article, I'm going to show you how to create a simple DTS package and how to execute the created package from ASP.NET.

By creating DTS packages, we can combine several tasks into one process and use any automation supported programming language to execute the created packages and to monitor their progress for errors, etc.

DTS Package

A brief introduction of DTS is necessary before looking into Automation. A DTS package is a collection of connections, tasks, and workflow, which can be used to access, transform and manipulate data from a wide range of sources. Each task defines a job to be completed as part of the overall DTS execution process.

DTS package can be created programmatically but in this article we are going to use SQL Server 2000�s DTS Designer.

To load DTS Designer, Start-> Programs-> Microsoft SQL Server -> Enterprise Manager. Right-click on the Local Packages node displaying in Enterprise manager and select New package. Enterprise Manager will display the DTS Designer.

Creating a DTS Package

Now we are going to create a package that will do the following:
  • Query the database
  • Save the results of the query in a flat file.
Drag and Drop a connection object from the tool bar to the designer. Next, Click on the icon to add an execute SQL task to our package. Enter the following SQL in the SQL statement box (property) SQL task:
Select * From Northwind..Orders

Storing the resultset in a flat file

Define an output parameter by clicking on parameters button of execute SQL task property. Select the tab, "Output Parameters". Click on the rowset radio button and on the "Create Global Variables" button. Enter rsResults as variable name and choose the type of the variable as "Other". Click OK and then select rsResults from the drop-down list. Click Ok and then exit from the property pages. Drag and Drop ActiveX Scripting task to the Package. When its property page is displayed, you can see a VBscript code inside a textbox.
Function Main()
On Error Resume Next
Dim strRecord, objFSO, objFile, objRS, iCounter
Set objFSO = CreateObject("Scripting.FileSystemObject") 
Set objResults = DTSGlobalVariables("rsResults").Value
Set objFile = objFSO.OpenTextFile("C:\orderReport.txt",2,true)
while not objResults.EOF 
 For iCounter = 1 to objResults.Fields.length
   strRecord =  strRecord & objResults.Fields(1).value 
if err.count = 0 then 
    Main = DTSTaskExecResult_Success 
    Main = DTSTaskExecResult_Failure 
end if
End Function
Save the package as "pkgOrders" in the designer.

Calling the package in ASP.NET

Create a stored procedure, exportData, in master database of SQL Server 2000. The code for the stored procedure is:
Exec master..xp_cmdshell 
'DTSRUN /Slocalhost /NpkgOrders /Usa /Pabc'
In ASP.NET page, add a label and a button server control. On Button1_Click event add the following code:
Dim myConnection As SqlConnection
Dim myCommand As New SqlCommand
 myConnection = New 
 SqlConnection("Data Source=.;Initial 
 Catalog=master;user id=sa")
 myCommand.Connection = myConnection
 myCommand.CommandText = "exportData"
 myCommand.CommandType = 
 Label1.Text = "Successfully exported!"
 Label1.ForeColor = Color.Black
Catch ex As Exception
 Label1.Text = ex.ToString
End Try


DTS is one of the many features of SQL Server that can be used to create a professional set of objects to make your everyday workings with data easier and more streamlined. Just like databases, DTS packages can also be exported, so they can be shared amongst multiple servers. In this article, I discussed only about exporting the data with a simple query. You can write complex queries which fetches data from multiple databases/tables. We use DTS Packages to distribute data to different processing servers.

Chockalingam Tirupathi is working as Software engineer in bangalore. He is haivng 6 years of experience on Microsoft technologies. He is also a Microsoft certified professional.

Posted On : 08 November 2003

Tags : ADO.NET Data Access SQL Server