article@vishalon.net

Why do we need DTS (Data Transformation Service?)

Vishal Monpara — Fri, 04 Aug 2006 16:56:08 GMT

Lets say you have data in one format(It can be a database/file/excel spreadsheet also). You want to convert it into another format. You will have a thought of writing a VB program and convert the source file into the format of destination file. But wait a minute. If you have lots of files in the different format and you want to convert it into different formats, then would you write VB programs for each conversion? You can write, but it is very time consuming and error prone.

Here comes into picture the DTS. As name suggests, it is a service for transforming data from one format to another. We can draw a workflow diagram of the data and SQL server Enterprise Manager will automatically create code to execute it. Here we need not have knowledge of how to open a file in VB, how to manipulate data, and how to store in the destination. DTS package handles all these intricacies automatically. Suppose you have excel file in which user data is stored. Now you want to put only certain data (for ex. all persons whose email address ends with yahoo.com) to SQL Server. In DTS, you have to drag excel sheet object, SQL Server object and make them point to appropriate source and destination. Put an arrow pointing from excel sheet to SQL Server. An arrow represents transformation of data from one form to another. By default, it transforms all data. We can create a custom tranformation and depending on the condition, we can insert data into SQL Server.

So the ease with DTS is, if we have multiple transformation, it would be very easy for a programmer to transform data from one form to another without lots of coding.

So lets start using DTS with a simple example. You have excel file and you want to upload it in SQL Server. So here are the steps.

Enterprise Manager > Microsoft SQL Servers > [SQL Server Group] > [SQL Server] > DAta Transformation Services > Local Packages.
Right click on it and choose "New Package"
On the left hand side, you can see "Connection" and "Task". Connection represents source and destination of data. Task represents different tasks to convert the data from one form to another
In "Connection" 3rd button is Excel. Click on it. It will open a dialog box. Provide the name of the new connection and browse the source excel file and put the path in "File Name".
In "Connection" 1st icon represents SQL Server. Click on it and provide valid credentials and select appropriate database.
In "Task" menu 3rd button is "Transform Data Task". Now click on Excel icon which will become source. Click on SQL Server icon. It will become destination.
Double click on the arrow. It will open up property which is important to transform Excel data into SQL data.
Select appropriate sheet in Source tab. In Destination tab, select appropriate table name. For our case it is "Item".
You can see one to one mapping from source field to destination fields. Now press "OK".
In the main menu, go to "Package" > Execute. This command starts executing the package. Now if you open the table, you will see the data.

You can download the whole dts package by clicking on DTS Package Example. Please save the package in c:\. From enterprise manager, right click on "Data Transformation Services" and choose "Open Package", browset c:\DTS\DTSExample.dts. Once you open it, double click on SQL Server icon and change the credentials. Run the script
DTS.sql to create a table "Item".

Things to remember while converting Microsoft Access application into SQL Server application

Vishal Monpara — Wed, 10 May 2006 06:20:47 GMT

When the Access database grows much large and the performance of the application gets worsen, you might need to think of upsizing the Access database into SQL Server database. So that you can split your MS Access front end and SQL Server back end. Microsoft Access comes with a utility called "Upsizing Wizard". It is located at Tools > Database Utility > Upsizing Wizard. This utility tries its best to convert the whole database including data tables, queries, relationships etc. This wizard is able to convert all tables perfectly but it might convert all queries into stored procedures or functions or views.

Choose the solution between Access application with Linked tables or Access Data Project.

When you are linking a table from a database on the network, the datasource is not updateable. So if the application adds/updates/deletes record, then this solution is not for you. Access Data Project is the best solution for the application which needs updateable recordset. One can import different objects like forms/reports in Access Data Project. Here the tables are not linked but you can direclty manipulate the database.

How to convert query into stored procedure?

When the query is directly used in a form which is using/not using a form variable, convert it into stored procedure.
Ex. The query is select * from EMP where Name like [forms]![Emp Form]![Name]
Create Procedure GetEmployee
(
@Name nvarchar(20)
)
AS
Select * from EMP where Name like @Name

If the query is using another query without any variable, make the first query as a stored procedure and convert second query into view.

If the query is using another query with variable, make the first query as stored procedure with variable and convert second query into a function.
Now you can use this function result as a temporary table.

Use Pass-through query wherever possible, because it reduces network traffic.

and last but not least

PAY ATTENTION TO THE BOOLEAN COMPARISION AND NULL VALUE CONCATENATION

Once you upsize the database, MS wizard gives you report of how many tables successfully upsized and how it changed the datatypes of columns. Most of the time, it upsizes it correctly but sometimes it just skips some tables/records. So to check it I have created an Access form which can be run to check the consistency of the records. Open the main form "frmMain" give the appropriate value and press "Check..." button. It will store the results in database. Now you can check the results by viewing the queries.

Subtract Time in Infopath

Vishal Monpara — Sat, 14 Jan 2006 05:21:21 GMT

Infopath is a great tool for rapidly developing an application which gathers information from the user. Many times these applications uses start time and end time as their fields. Infopath has a "Time" datatype. We can directly assign this datatype to the fields. You can compare two fields of this datatype but substraction of fields is not handled. You have to manually parse the field value, subtract the values and change the appropriate fields. Here is the proposed solution of this problem.

This solution contains 3 functions

UpdateHours()
convertJScriptNumberToXML()
roundFloat()

Lets see all these functions one by one.

function UpdateHours()
{
	var TimeIn = XDocument.DOM.selectSingleNode("/my:Time/my:StartTime").text;
	var TimeOut = XDocument.DOM.selectSingleNode("/my:Time/my:EndTime").text;
	var index1 = TimeIn.indexOf(":");
	var hi = TimeIn.substring(0,index1);
	var index2 = TimeIn.indexOf(":", index1+1);
	var mi = TimeIn.substring(index1+1, index2);
	index1 = TimeOut.indexOf(":");
	var ho = TimeOut.substring(0,index1);
	index2 = TimeOut.indexOf(":", index1+1);
	var mo = TimeOut.substring(index1+1, index2);
	var ti = parseFloat(hi) + (mi/60);
	var to = parseFloat(ho) + (mo/60);
	var tt = to - ti;
	var node = XDocument.DOM.selectSingleNode("/my:Time/my:TotalHours");
	if (tt != "" && node.getAttribute("xsi:nil"))
		node.removeAttribute("xsi:nil");
	node.nodeTypedValue = convertJScriptNumberToXML(tt);
}

As the name suggests, this function updates the value of "TotalHours" field. First of all, it will get the value of "StartTime" and "EndTime" field. The value will be in the format of HH:MM:SS. So the value will be parsed according to the position of ":". Both the values will be converted inot the float representation. Now value will be sutracted and the result will be stored in "TotalHours" field.

function convertJScriptNumberToXML(value)
{
	var retVal;
	switch (value)
	{
		case Number.NEGATIVE_INFINITY:
			retVal = "-INF";
			break;
		case Number.POSITIVE_INFINITY:
			retVal = "INF";
			break;
		case value:
			retVal = roundFloat(value,2);
			break;
		default:
			retVal = "NaN";
	}
	return retVal;
}

This function converts the actual number into text based value. Here he number will be rounded upto 2 digits.

function roundFloat(value, decimalPlaces)
{
	if (value < -1E15 || 1E15 < value)
	{
		return value;
	}
	else
	{
		var nPowerToRound = Math.pow(10, decimalPlaces);
		return Math.round(value*nPowerToRound)/nPowerToRound;
	}
}

This function actually rounds the float value to the required decimal place. To apply this code to your application, copy/paste these three functions into the script file. In the "OnAfterChange" event of start time and end time field, call the function "UpdateHours".

Note: Please put the downloaded code in C:\

Utility for rendering HTMLTable control containing ASP .Net form elements into string

Vishal Monpara — Sat, 24 Sep 2005 19:29:58 GMT

Introduction

Many websites have some kind of contact form as shown in the following figure. User fills details and presses the "Send" button. This form takes input from the textbox, checkbox, radiobutton etc, builds string containing html and sends information as HTML email. If the form is expecting many values from user, the developer has to write down big chunk of HTML code in the backend. If the developer has to build more than one such forms, it is really cumbersome. To reduce the precious time of developer, this utility module is developed.

Problem in Direct Rendering of Control

We can use directly Control.Render() method to render html in HTMLTextWriter but with the condition that there should not be any webform controls like TextBox, Label etc. If still we are using this method, it will give error because each server control must be rendered within "form" tag which is having "runat=server" attribute.

Utility Module

Public Function GetHTML(ByVal objControl As Control) As String
	Dim sb As New System.Text.StringBuilder
	GenerateHTML(objControl, sb)
	Return sb.ToString
End Function

This function takes as argument the control object of which we need to get HTML. It will call private method GenerateHTML() which is responsible for generating HTML for the control.

Private Sub GenerateHTML(ByVal objControl As Control, ByVal sb As System.Text.StringBuilder)
	Dim str As New System.Text.StringBuilder
	Dim sw As New System.IO.StringWriter(str)
	Dim hw As New System.Web.UI.HtmlTextWriter(sw)
	If TypeOf (objControl) Is HtmlTable Then
		sb.Append("<table ")
		CType(objControl, HtmlTable).Attributes.Render(hw)
		sb.Append(str.ToString & " >")
		Dim objControl1 As Control
		For Each objControl1 In objControl.Controls
			GenerateHTML(objControl1, sb)
		Next
		sb.Append("</table>")
	ElseIf TypeOf (objControl) Is HtmlTableRow Then
		sb.Append("<tr ")
		CType(objControl, HtmlTableRow).Attributes.Render(hw)
		sb.Append(str.ToString & " >")
		Dim objControl1 As Control
		For Each objControl1 In objControl.Controls
			GenerateHTML(objControl1, sb)
		Next
		sb.Append("</tr>")
	ElseIf TypeOf (objControl) Is HtmlTableCell Then
		sb.Append("<td ")
		CType(objControl, HtmlTableCell).Attributes.Render(hw)
		sb.Append(str.ToString & " >")
		Dim objControl1 As Control
		For Each objControl1 In objControl.Controls
			GenerateHTML(objControl1, sb)
		Next
		sb.Append("</td>")
	ElseIf TypeOf (objControl) Is LiteralControl Then
		sb.Append(CType(objControl, LiteralControl).Text)
	ElseIf TypeOf (objControl) Is TextBox Then
		sb.Append(CType(objControl, TextBox).Text)
	ElseIf TypeOf (objControl) Is Label Then
		sb.Append(CType(objControl, Label).Text)
	ElseIf TypeOf (objControl) Is CheckBox Then
		Dim chk As CheckBox = CType(objControl, CheckBox)
		sb.Append("<input type='checkbox'" & IIf(chk.Checked, "Checked ", " >") & chk.Text)
	ElseIf TypeOf (objControl) Is RadioButton Then
		Dim rad As RadioButton = CType(objControl, RadioButton)
		sb.Append("<input type='radio'" & IIf(rad.Checked, "Checked >", " >") & rad.Text)
	End If
End Sub

This function uses recursion to get each element from control hierarchy. It renders HTML code for the control. Currently it supports, HTMLTable, TextBox, CheckBox, RadioButton, Label, and LiteralControl. As this function uses recursion, the processing may be slower than hardcoding the HTML string. So I wrote other function GetCode() which is responsible for emitting source code for building HTML string. This code you can put in a function and get HTML string of the control. The output of both methods are shown in figure.

Conclusion

If webform is not used very frequently, developer should use, GetHTML() function otherwise they should use use and throw function GetCode(), generate the code, put the code in file and remove this function from solution.