Results 1 to 2 of 2

Thread: sql server DTS

  1. #1
    Join Date
    Jun 2002

    Unanswered: sql server DTS

    I am a student of MCA. Currently involved in a data warehousing project.I am facing some problems.if anybody solve this problem i will be thankfull to him.I am writing the questions.

    1. I have one flat file and one database table .We have to extract data by applying certain conditions from these two into a destination database table.

    i. How can i do this job ?
    ii. How can we do this by applying Lookups in SQL Server 2000.

    2. How to implement “Surrogate keys” for DTS (Data transmission Services ) in SQL Server 2000.

  2. #2
    Join Date
    Jun 2002
    New York City
    sql server 2000 DTS by wrox is a pretty good source for this. they really go into detail on how to leverage the features.

    if you dont want to read this book, here are some considerations:

    1) if you are moving the data from file to table and using lookup tables, it sounds like you have more than one table.
    2) The way to think about this is think of it as a challenge to automate the normalization of a denormalized table.
    2) TableA: start by importing the raw data into TableA - it is denormalized. (use the data transform process).
    3) TableB: create a table that is almost identical, except, where you need to have foreign key references. Change the datatype and\or name to reflect the values stored in the PK.
    4) use a stored procedure to insert from TableA to TableB. where the value must be changed to the fK reference, use a function that will do a quick lookup. if a value is not present in the lookup table, write code that will put the NEW value in an exception table and\or email you (xp_sendmail).

    Example Process:

    Get Text File => Import Data to Denormalized Table (TableA) => Load to Normalized Table (TableB)

    Example Load:

    insert into dbo.TableB
    select firstname,lastname,fnConvertEmployeeStatus(Employe eStatus)
    from dbo.TableA

    Example Function (semi-psuedo code):

    fnConvertEmployeeStatus(@EmployeeStatus varchar(10))
    set @EmployeeStatusID=(select EmployeeStatusID from EmployeeStatus where EmployeeStatus=@EmployeeStatus)

    if @EmployeeStatusID is null
    send mail warning to dba
    insert new value into exception table
    return 1 'default value
    return @EmployeeStatusID

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts