Inspired? No home

Using Data Transformation Services

For many tasks a server-side script does the trick. But in many cases the task may be time consuming and we can't have the client waiting now can we? Other tasks might require to be executed at certain times, i.e. every night for an import to a database. That is exactly my current task. And for that there is an excellent solution in Microsoft SQL Server. Many web-developers using MS-SQL are not aware of many of the other features included. Data Transformation Services (DTS) is one very useful feature. You might have used it when importing or exporting data in enterprise manager. The simple DTS Import/Export wizard let's you easily import/export data to/from many data sources. But there is much more to DTS than this wizard. Look under 'Data Transformation Services' in Enterprise Manager. Here you can create a DTS package that can be executed from procedures, scripts, manually or to be scheduled by MS-SQL. This package allows you to easily do tasks like import, get data from ftp, run active-x script, send e-mail etc. There are about 15 standard tasks that are easy to use. An with the active-x task you can use VBS and COM to do whatever you require. What's nice about this is that you set up various tasks and you set a workflow. So when task 1 is finished you go to task 2. You can then specify, if task 1 fails then goto task 3 if successful then goto task 2. It can all be run in a transaction so you can tell it to commit/rollback the transaction when the package is finished or at any point in the tasks. There are also global variables you can share between the tasks. For import/export tasks it is very useful and it includes a very good wizard-kinda tool where you can map fields from the source to the destination and also use an active-x script to do modifications between the source and destination. To learn more about DTS have a look at SQL Server Books online, SQLDTS.com or SQLTeam.com.

Written on 10 July 2004.
blog comments powered by Disqus