<%@ 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.
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.
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
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:
CREATE PROCEDURE exportData AS
'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
myCommand.Connection = myConnection
myCommand.CommandText = "exportData"
Label1.Text = "Successfully exported!"
Label1.ForeColor = Color.Black
Catch ex As Exception
Label1.Text = ex.ToString
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.