SQL Server Performance

DTS

Discussion in 'SQL Server DTS-Related Questions' started by Raulie, Mar 31, 2004.

  1. Raulie New Member

    I am using the Transformation Task to copy columns from a view to a table. The view is a denormilized table some, of the values in the new table will be Integer Id that will reference other tables like lookups. Lets say that in the denormilized table a column called VENDORREGION has a char(n) value of "SOCAL" now in the new table it will be an integer value of lets say 1 which points to a Region table. Can some one point me to the right direction to accomplish this?
  2. Raulie New Member

    What is the main difference from using DataDriven Query Task and Tranformation Task?
  3. satya Moderator

    DataDriven query task allows you to perform flexible, TSQL based operations on data, including stored procedures and INSERT, UPDATE or DELETE statements.

    Transform Data task to copy data between a source and destination and to optionally apply column-level transformations to the data. The Transform Data task is the most basic implementation of the data pump engine in DTS. THis is optimized for insert-based copying and transforming of column-level data between commercial databases, spreadsheets, and text files.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. Raulie New Member

    Thanks I got it to work.
  5. Raulie New Member

    Another Problem.....
    I have another field I want to tranform (MYCOLUMN2) if these same conditions are met MYCOLUMN2 is a column in the same destination source.

    for example IF MYVARIABLE = "GREEN" THEN DTSDestination("MYCOLUMN1") = 1 AND DTSDestination("MYCOLUMN2") = 2 END IF

    every time I try to run this it inserts null into both columns however when I use only one column it works fine like in this example.
    MYVARIABLE= TRIM(DTSSource("COLORS"))
    IF MYVARIABLE= "GREEN" THEN DTSDestination("MYCOLUMN1") = 1 END IF
    IF MYVARIABLE= "BLUE" THEN DTSDestination("MYCOLUMN1") = 2 END IF
    IF MYVARIABLE= "PURPLE" THEN DTSDestination("MYCOLUMN1") = 3 END IF
    IF MYVARIABLE= "GREY" THEN DTSDestination("MYCOLUMN1") = 4 END IF
  6. Raulie New Member

    I got to work

    MYVARIABLE= TRIM(DTSSource("COLORS"))
    IF MYVARIABLE= "GREEN" THEN DTSDestination("MYCOLUMN1") = 1 END IF
    IF MYVARIABLE= "BLUE" THEN DTSDestination("MYCOLUMN1") = 2 END IF
    IF MYVARIABLE= "PURPLE" THEN DTSDestination("MYCOLUMN1") = 3 END IF
    IF MYVARIABLE= "GREY" THEN DTSDestination("MYCOLUMN1") = 4 END IF

    IF MYVARIABLE= "GREEN" THEN DTSDestination("MYCOLUMN2") = 5 END IF
    IF MYVARIABLE= "BLUE" THEN DTSDestination("MYCOLUMN2") = 6 END IF
    IF MYVARIABLE= "PURPLE" THEN DTSDestination("MYCOLUMN2") = 7 END IF
    IF MYVARIABLE= "GREY" THEN DTSDestination("MYCOLUMN2") = 8 END IF

    I just tranform both columns using AND.

Share This Page