New Column containing contents of several columns into one

• nulad01

  SSC-Addicted

  Points: 412

  More actions

  June 7, 2013 at 2:27 pm

  #100152

  I have an Excel spreadsheet that will be used as an append file to upload to an SQL Server 2008 R2 table.

  The Excel spreadsheet has the following columns:

  EUnique# StartDate EndDate Location ColumnA ColumnB ColumnC ColumnD

  000000001 6/6/2013 1:48:23 PM 6/6/2013 1:49:50 PM LocA CC-H07

  000000002 6/6/2013 1:30:11 PM 6/6/2013 1:31:08 PM LocB HM-H08

  000000003 6/6/2013 1:29:00 PM 6/6/2013 1:29:47 PM LocC II-H09

  000000003 6/5/2013 8:36:54 PM 6/5/2013 8:37:02 PM LocA WW-H10

  I am attempting to do this in SSIS using a Drived Column.

  I need to make a new single int column based on the results from the above text columns: ColumnA, ColumnB, ColumnC, and ColumnD (I am attempting to do this in SSIS using a Drived Column).

  Example: (The NewColumn (int) field's contents should be something like this):

  CASE WHEN ColumnA is not null or ColumnA <> '' AND LTRIM(RTRIM(SUBSTRING(ColumnA,FINDSTRING(ColumnA,"-",1) + 1,LEN(ColumnA) - FINDSTRING(ColumnA,"",1)))) = 'CC-H07' THEN 100 (convert the 100 to an int datatype)

  WHEN ColumnA is not null or ColumnA <> '' AND LTRIM(RTRIM(SUBSTRING(ColumnA,FINDSTRING(ColumnA,"-",1) + 1,LEN(ColumnA) - FINDSTRING(ColumnA,"",1)))) = 'HM-H08’ THEN 200 (convert the 200 to an int datatype)

  WHEN ColumnA is not null or ColumnA <> '' AND LTRIM(RTRIM(SUBSTRING(ColumnA,FINDSTRING(ColumnA,"-",1) + 1,LEN(ColumnA) - FINDSTRING(ColumnA,"",1)))) = 'II-H09’ THEN 300 (convert the 300 to an int datatype)

  WHEN ColumnA is not null or ColumnA <> '' AND LTRIM(RTRIM(SUBSTRING(ColumnA,FINDSTRING(ColumnA,"-",1) + 1,LEN(ColumnA) - FINDSTRING(ColumnA,"",1)))) = ' WW-H10 THEN 400 (convert the 400 to an int datatype)

  The same continues for:

  ColumnB………,

  ColumnC………..,

  ColumnD……….

  If the column has a entry (there will be only one column entry per row), then it will be converted to an int data type depending on what is in the column’s field.

  Please advise the best way to accomplish this. CASE is not an option, and not sure how to accomplish this. Your advice will be greatly appreciated. Thanks!