November 25, 2002 at 9:07 am
Hi Guys,
I am new DTS. I have an urgent task to accomplish. I have a table with about 35 columns in SQl Server. I need to transform the records in this source table to a normalized table (about 20 tables). The table contains employee information ... Lname, Firstname, Sex, DoB, Country of Residence etc.
I need to push the Lname, Firstname, Sex, Dob to a Employee table and create a Employee Id (auto no). Further I need to lookup the country code from a Country lookup table in Destination database and insert the country code in the Address table for that employee id.
Please let me know the best way to address this problem
Thanks
Sid
November 25, 2002 at 9:32 am
Hi Sid -
The power of DTS is its flexibility. So, there are several ways to solve this problem. In it's simpliest form, you could create parallel data transformations. More dynamically, you could use Active-X or Dynamic properties Tasks to cycle through your data loads.
To get started, try this:
1. Create a new package
2. Add two SQL Server connections (drag and drop or double click) - one of these will be the source, the other the target connection.
3. Connect these with a Data Transformation Task
4. In the Data Transformation Task use a SQL query to define the result set to be loaded into the target table(the Query Builder may be helpful if you're not comfortable writing the SQL statemtn from scratch)
5. Note that you can create the target table (on the destination tab) by clicking Create button.
6. On the Transformations tab map the source to target columns. You have several transformation options to choose from here but a simple copycolumn is the default and likely the best choice
The online help is pretty good in DTS so you might want to check it out.
Scot J Reagin
Scot J Reagin
sreagin@hitachiconsulting.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply