locked
SSIS data load question RRS feed

  • Question

  • I have the following data in my comma delimited text file:

    userid,customername, orderid
    user1,smith, chris Y,1222
    user2,smithlast, lindafirst T,6777

    Using SSIS I want to load lastname, firstname and orderID into a sql table from the above data as follows:

    lastname   firstname      orderID
    smith         chris Y        1222
    smithlast   lindafirst T   6777

    How can I populate the firstname and orderID column as the file is a comma delimited file?

    Thanks.


    sqldev

    Tuesday, June 12, 2012 4:23 PM

Answers

  • I got it, I used derived column transformation with the following expressions:
    FirstName:
    SUBSTRING([OrderID],2,FINDSTRING([OrderID],",",1) - 2)

    OrderId
    SUBSTRING([OrderID],FINDSTRING([OrderID],",",1) + 1,999)


    sqldev

    • Marked as answer by sqldev1 Tuesday, June 12, 2012 8:48 PM
    Tuesday, June 12, 2012 8:47 PM

All replies

  • Just use a flat file source and .csv, column names in first row ...

    Miss Never Giveup

    Tuesday, June 12, 2012 4:27 PM
  • Oh by the way, your header has 3 columns and data has 4 so better if u do this

    userid,customerFname, customerLname, orderid
    user1,smith, chris Y,1222
    user2,smithlast, lindafirst T,6777


    Miss Never Giveup

    Tuesday, June 12, 2012 4:32 PM
  • I want to load the string that's in the customername column into lastname and firstname table columns as follows:

    lastname   firstname      orderID
    smith          chris Y         1222
    smithlast    lindafirst T    6777

     Thanks.


    sqldev

    Tuesday, June 12, 2012 4:43 PM
  • Your comma delimited file is junk since you have commas in the data.  You need to get the provider of the data to add field delimiters for your text fields

    userid,customername, orderid
    "user1","smith, chris Y",1222
    "user2","smithlast, lindafirst T",6777


    Chuck Pedretti | Magenic – North Region | magenic.com

    Tuesday, June 12, 2012 4:49 PM
  • Thanks, is there anyway I can use FindString in a derived column transformation for this?

    sqldev

    Tuesday, June 12, 2012 5:22 PM
  • Maybe.  If you can guarantee that there is only 1 comma ever in the data field then you could bring the data rows in in a single field and bust them up with a script task.

    Chuck Pedretti | Magenic – North Region | magenic.com

    Tuesday, June 12, 2012 5:39 PM
  • I got it, I used derived column transformation with the following expressions:
    FirstName:
    SUBSTRING([OrderID],2,FINDSTRING([OrderID],",",1) - 2)

    OrderId
    SUBSTRING([OrderID],FINDSTRING([OrderID],",",1) + 1,999)


    sqldev

    • Marked as answer by sqldev1 Tuesday, June 12, 2012 8:48 PM
    Tuesday, June 12, 2012 8:47 PM