how to import excel data to SSIS

• Patrick.I

  Hall of Fame

  Points: 3105

  More actions

  March 31, 2008 at 8:26 pm

  #122914

  I have some excel sheet to import into sql server 2005.

  But is it possible to tell SSIS to create a column called conatct_id and add a constant value like 1 to it plus another column with an incremental value

  So excel sheet e.g

  a b

  -- --

  boy girl

  and into sql i want to have

  id contactid a

  -- --------- --

  1 1 boy

  2 1 aunty

  any ideas would be appreciated

  Thanks

• SQL ORACLE

  One Orange Chip

  Points: 27807

  More actions

  March 31, 2008 at 9:21 pm

  #796270

  May this link help

  http://www.builderau.com.au/program/sqlserver/soa/How-to-import-an-Excel-file-into-SQL-Server-2005-using-Integration-Services/0,339028455,339285948,00.htm

• Sri8143

  SSCertifiable

  Points: 5251

  More actions

  April 1, 2008 at 7:17 am

  #796461

  hey patrick if u need to add an additional column other than from the excel u can use derived column ...if u go tit tast fine if not let me know....

  Thanks,
  Chinna

  Its the Journey which gives you Happiness not the Destination-- Dan Millman

• Frank Bazan

  SSCertifiable

  Points: 5348

  More actions

  April 2, 2008 at 6:13 am

  #797131

  Hi Patrick,

  To add a constant for your contact_id, you could do this by using a JET SQL Command for your excel source:

  SELECT

  F1 AS Col1,

  F2 AS Col2,

  1 AS ContactId

  FROM [Worksheet$]

  To add a row number you can use a script transformation in the dataflow which increments for each row that passes through it.

  If what you're saying is that you want to normalise the data from the spreadsheet and transform the columns into rows, then you will need to pass the data through the pivot component.

  HTH

  Kindest Regards,

  Frank Bazan