Post reply

export comma delimited data from one source file to two destination tables

  • September 4, 2008 at 12:05 pm

    #382522

    I have to export comma delimited data from one source file to two destination tables.

    Example

    Data in source file

    Male,mike,nj,usa(4 columns)

    Female,nansy,8(3 columns)

    Table structures

    Destination 1) tblMale

    Sex char(4)

    Name varchar(20)

    State char(2)

    Country char(3)

    Destination 2) tblFemale

    Sex char(4)

    Name varchar(20)

    Age bigint

    Output in table “tblMale”

    Male,mike,nj,usa

    Output in table “tblFemale”

    Female,nansy,8

    Thanks

    BMR

  • September 4, 2008 at 2:22 pm

    #867423

    As with most things SSIS, there's more than one way (probably) to skin this cat 😉

    Here's something that worked for me though:

    1. Flat File Source

    The key here is to set the format in the data source to "Ragged Right" - this will effectively deal with the fact that some rows have 3 cols and some 4

    Effectively, the output from this will be one column rows as per the source file, having got this we can go to work

    2. From the output, add a Conditional Split

    Set up the Male Output formula to be SUBSTRING([Column 0],1,4) == "Male" (from your post, the rest by default must be Female

    So now you have two datasets, one male & one female.

    What I did next was to split the single column of both datasets in to 3 and 4 column datasets as per your description

    i.e. for the males

    3.Add a Derived Column Transformation

    Add four new columns to this with the following formulas to split the string

    Sex .... formula is SUBSTRING([Column 0],1,FINDSTRING([Column 0],",",1) - 1)

    Name.....SUBSTRING([Column 0],FINDSTRING([Column 0],",",1) + 1,(FINDSTRING([Column 0],",",2) - FINDSTRING([Column 0],",",1) - 1))

    State....SUBSTRING([Column 0],FINDSTRING([Column 0],",",2) + 1,(FINDSTRING([Column 0],",",3) - FINDSTRING([Column 0],",",2)) - 1)

    Country...SUBSTRING([Column 0],FINDSTRING([Column 0],",",3) + 1,LEN([Column 0]))

    Do the same sort of thing for females... then all you probably have to do is to do some unicode to non unicode data conversions and you're away....

    Probably more elegant solutions, and you could code it in a script, but this does work and illustrates a few of the data flow components.... 🙂

    Kind Regards, Will

  • September 4, 2008 at 3:36 pm

    #867474

    Thanks for reply.

    I am doing this in DTS. Will it work in DTS?

    Thanks again

    BMR

  • September 4, 2008 at 3:59 pm

    #867489

    Hmmm... Well this forum is for SQL 2005.... Since when is DTS part of SQL 2005 Business Intelligence ? Try posting in a DTS forum 😉

    Kind Regards, Will

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply