MS SQL Server
  Home arrow MS SQL Server arrow Using Data Transformation Services - Part ...
ASP Free Forums 
.NET  
ASP  
ASP Code  
ASP.NET  
ASP.NET Code  
BrainDump  
C#  
Code Examples  
Database  
Database Code  
IIS  
Microsoft Access  
MS SQL Server  
Silverlight  
Visual Basic.NET  
Windows Scripting  
Windows Security  
XML  
Mobile Linux 
App Generation ROI 
IBM® developerWorks 
ASP Web Hosting  
ASP.NET Web Hosting 
Windows Web Hosting
 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
MS SQL SERVER

Using Data Transformation Services - Part 1
By: Jayaram Krishnaswamy
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 4 stars4 stars4 stars4 stars4 stars / 37
    2005-05-04

    Table of Contents:
  • Using Data Transformation Services - Part 1
  • Import Export Wizard
  • The DTS Designer
  • Package creation details
  • Executing the package/step

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT


    Using Data Transformation Services - Part 1


    (Page 1 of 5 )

    Data Transformation Services (DTS) enable transfer of data and objects between different SQL 2000 Server systems. This article includes an overview of the DTS package, as well as step-by-step instructions for transferring a table from the SQL Server to an MS Access file.

    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.

    The DTS Data Pump is at the heart of the transfer process connecting from source to sink. While data is being transferred, it may also be transformed by using the ActiveX scripting languages like jscript and vbscript. This special feature operates on rows of data where column contents from the source side can be made to match up with what is required by the destination, or even more demanding complex functions, as well as validated retrieval from external sources to local source can be accomplished.

    Contents of this tutorial

    There are two tools and an API available in SQL 2000 Server to interactively create DTS Packages. These are:

    • Import Export Wizard
    • DTS Designer
    • DTS API

    The first part of this tutorial shows a step-by-step approach for transferring a table from the SQL Server to an MS Access file. In the second part, an overview of the DTS package is presented. This is followed by the presentation of a concrete step-by-step example of copying a database from an SQL Entrprise Server to an MSDE server in the same machine. A future tutorial will consider the DTS API in considerable detail. While the Import Export Wizard is quite powerful, using the DTS Editor will provide a lot more power and flexibility to fine tune the data transfer.

    More MS SQL Server Articles
    More By Jayaram Krishnaswamy


       · Great article!One wonders if there is any way to transform data from a SQL Server...
       · If there is no SQL Server, then where is the SQL Server database? Is it perhaps in a...
     

    MS SQL SERVER ARTICLES

    - Windows Server 2008 as a Workstation OS
    - An Overview of Windows Server 2008 R2
    - LINQ to MySQL, Oracle and PostgreSQL Provide...
    - Completing the Introduction to Transact-SQL
    - A Brief Introduction to Transact-SQL
    - Lookups and Blocking Bad Data
    - Field Validation Rules for Blocking Bad Data
    - Using Masks to Block Bad Data
    - Blocking Bad Data
    - Using @@ROWCOUNT and TABLE Variables for Dat...
    - How to Use Variables, IF and CASE in Databas...
    - Creating Important Aspects of Notification S...
    - Working wth Variables in Database Interactio...
    - Delving Deeper into Notification Services
    - Notification Services





    © 2003-2009 by Developer Shed. All rights reserved. DS Cluster 2 Hosted by Hostway
    For more Enterprise Application Development news, visit eWeek