SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    call-e-for-nea
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    transformation scripts

    where can i find good information on creating vbscripts for transformations and column mapping/table column for DTS importing.

    Ill give gmail invites to the good answers.
    if someone can help me

    I need to import a text file and i want it to do something like

    Function Main()
    If "Error Occurs" Then
    Main = DTSTransformStat_SkipRow
    Else


    DTSDestination("EmailAddress") = DTSSource("Col001")
    Main = DTSTransformStat_OK

    End If
    End Function

  2. #2
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    call-e-for-nea
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    huh!
    okay what i need is for the IF statement to check if the source column is a duplicate value in any of the destination columns THEN skip row ELSE go ahead and import. I hope that makes sense and I hope I can get some help please.

  3. #3
    SitePoint Addict Guimauve's Avatar
    Join Date
    Aug 2004
    Location
    Chicago
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You need to import the text file into a database table that temporarilly holds the new information and then move only what you want into the permanent table.

    In other words, the VBScript function doesn't do the lookups, you add more steps to the DTS process that run after the text file is in the database to perform that work.

    It is then easy, for example, to only insert new rows if the key value doesn't already exist.
    Code:
    INSERT INTO Country (Name)
    SELECT Name
      FROM load_Country
     WHERE Name NOT IN (SELECT Name FROM Country)
    Charles

  4. #4
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    call-e-for-nea
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
     INSERT INTO Customers
                    (EmailAddress)
     SELECT Col001
     FROM  bak2
     WHERE (Col001 NOT IN
                        (SELECT EmailAddress
                         FROM   Customers))
    This code still gives me the same error:

    [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of UNIQUE KEY constraint 'IX_Customers'. Cannot insert duplicate key in object 'Customers'.

    bak2 is my temp table and Col001 is the default Column name.

    Could You Elaborate Charles?
    Last edited by atomi; Apr 20, 2005 at 13:59.

  5. #5
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    call-e-for-nea
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    NM I GOT IT!

    I had duplicate rows in my temp (bak2) table!

    So what I did was create a table named (temp)

    And did an INSERT INTO temp
    SELECT DISTINCT Col001 From bak2

    after that I ran the previous query using the (temp) table ofcourse.

    Thanks man.


    Charles you were a big help
    Last edited by atomi; Sep 2, 2004 at 15:51.


Bookmarks

Posting Permissions

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