Modern Data Insight

DTS (Data Transformation Services) in SQL Server is very powerful feature and can be used to solve many database related issues starting from data migration, conversion and import/export. Even content publication/management applications can be developed easily using DTS. SQL Server’s DTS support is extended with latest version of SQL Server 2000. This article’s main goal is not only to expose the DTS functionality of SQL Server but also to show the simplicity in using DTS packages to implement core functionality. This case study takes up a sample financial web application “Trade Analysis” designed and developed by Prakash Financials. Trade Analysis application supports trade analysis for client specific trade data. Client in this article represents mostly an organization. ‘User’ is any user that uses the services of “Trade Analysis”. There may be many users from one single organization/client and there may be more than one client registered for the using “Trade Analysis”....

This article explains how to take a string of comma-separated values (CSV), and parse out the values individually. If you have a table with a column of CSV values, and you want to translate them into a normalized table, it can really difficult. You would need to use a cursor to loop through your CSV table and process each row. Fortunately there's a way to do this using just a regular SELECT. It requires a table of numeric values, but it does the job beautifully; you can INSERT normalized CSV values into a table with one SQL statement. The tricky part is that each word can be a different length, and each CSV can have a different number of words in it. We'll need a separate table (Tally), with a single column of sequential integer values. The "tally table" is mentioned in Joe Celko's SQL for Smarties -- the best SQL book ever written -- as a handy source for a numeric sequence. Here, it will replace the loop needed to parse out each value from the CSV string. SELECT...