Introduction
Many organizations have their data stored in many formats and in many locations within the specified network of computers. Extraction, Transformation and loading of heterogenous data using OLEDB providers, ODBC sources, text-only formats have become more and more rigorous and cumbersome. Very often, database administrators need to perform supportive tasks like data consolidataion, archiving, analysis and manipulation in generating decison-making reports. Also, they are into making of custom-tools that move data from various destinations, for improved system performance.
Data Transformation Services(DTS) provides a set of tools that enables you to organize such works thus simplifying the import, export capabilities of your data. It handles all types of known data formats available with Microsoft Access, Microsoft SQL Server or Microsoft Excel.
How far the DTS is helpful to you?
Firstly, you can upgrade your existing data schema and data types to the very recent version of SQL Server.
Secondly, You can build a DTS soultion to copy and transform database objects from one datasource to SQL Server database and embed the same solution in your application.
Thirdly, You can combine one or morespreadsheets put data in them in to a SQL database;interestingly, you can schedule this task.
Fourthly,your DTS solution can integrate with the Message Queueing service enabling the message-automation mechanism.
Lastly, your DTS solution can use the File Transfer Protocol to move files into a datafile and load data to a fact table and aggregate them using Analysis Services.
What are the DTS tools available for me?
The Import/Export Wizard - for building simple DTS packages, and
supports data migration and transformations.
The DTS Designer - a GUI based DTS object model
DTSRun - a command-prompt utility used to execute existing DTS
packages.
DTSRunUI - a GUI for DTSRun
SQLAgent - allows DTS to schedule package execution.
What are the common tasks in a DTS Package
A DTS package usually includes one or more tasks. Each task defines a work item that is being performed during package execution. There are many tasks related to:
Transform data tasks
Transform Data task
Data Driven Query task
Parallel Data Pump task
Copy and manage data tasks
Bulk Insert task
Execute SQL task
Copy SQL Server Objects task
Transfer Database task(1)
Transfer Error Messages task
Transfer Logins task
Transfer Jobs task
Transfer Master Stored Procedures task
Run tasks as jobs
ActiveX Script task
Dynamic Properties task
Execute Package task
Execute Process task
File Transfer Protocol (FTP) task
Message Queue task
Send Mail task
Analysis Services Processing task
Data Mining task
Summary
The DTS Designer provides a wide variety of solutions to data movement task along with programmatic access to the DTS object model. Using both COM based and .NET framework components, you can develop a custom DTS solution for your data environment.
DTS supports a developer in many ways by building, extending, and
executing solutions in the form of packages
|
No responses found. Be the first to respond and make money from revenue sharing program.
|