Wednesday, May 16, 2012

What is DTS?

SQL-DMF: SQL Distributed Management Framework is a set of utilities that are not part of the database engine (like Query Analyzer and Enterprise Manager). There are three application programming interfaces, or utilities that allow us to access all the objects in the SQL Server. They are:
 SQL Namespace - SQL-NS
SQL Distributed Management Objects - SQL-DMO
SQL Data Transformation Services - SQL-DTS
DTS, which stands for Data Transformation Services, enable transfer of data and objects between different SQL 2000 Server systems. DTS was originally created to transfer data from OLTP data into OLAP data, which included the various steps of extracting the data, cleansing and transforming to remove inconsistencies, and then loading the data (popularly known as ETL). DTS can validate and summarize data while transferring -- making it extremely powerful.

DTS is available to all SQL Server editions from Enterprise to MSDE. DTS uses OLEDB, so it can be used for transferring data from one OLEDB to another OLEDB and therefore is not specific to SQL Server. It could be used to transfer data between any OLEDB supported databases, for example between Oracle to MS Access without an SQL 2000 middle man. Since Microsoft OLEDB for ODBC can connect to ODBC sources, the data transfer functionality is extended to ODBC sources, and DTS assumes an even more powerful role.

This article will be very useful if you are still using SQL Server 2000

SSIS replaced DTS in the recent SQL Servers after its 2000 Version. If you want a jump start on SSIS, I recommend my books which describes some 20 ETL Tasks. This book may be purchased from

Thursday, May 3, 2012

How do you create an empty database in SQL Server Express 2008 R2?

Step 1: Connect to SQL Server Express 2008 R2 in the SQL Server Management as shown.
Right click the Databases node and from the drop-down list shown  click New database.

Step 2: In the New Database window that pops-up provide a name for the database you want to create, herein Htek. If you want you can choose a size different from default size of 2 MB.

Click OK.
This is all that is necessary for creating an empty database on the server. You may click the navigation on the left side for working with file groups and many other options.

After clicking OK you will see the Htek database added to your Databases node which earlier had only System databases. Note however you may need to refresh by right clicking the Databases node and choosing Refresh at the bottom of the list.