[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

odd little update need... or a new SSIS config

imported three small-ish csv files. weird formats, took me forever to get it into place.  finally got it, and a very small percentage of several columns for a handful of records, have been split out into additional columns, like this:

this is one attribute:   AEROTAXI EJECUTIVO, S.A.
it was loaded so that the 'S.A.' is in a column by itself, like this:  AEROTAXI EJECUTIVO,                 S.A.

same thing with this one:  ARION SHIPPING CO. LTD.
one column, but it went in as two:   ARION SHIPPING CO.                       LTD.

Unfortunately, it's not all 'S.A.' or 'LTD.'... it varies.  Also, it's not always a comma separator.  As you can see, in my second example there is no comma.

I either need to persuade SSIS to upload without the column separation, or I need to just update the data, selectively.  This is not a recurring upload, so the latter would be fine.  I'm just having a problem CASEing the update from the second column.

Any thoughts?
0
dbaSQL
Asked:
dbaSQL
  • 3
  • 2
1 Solution
 
jogosCommented:
You don't need a CASE , just concatinate column2 to column1 when in column2 you detect such a thing

WHERE col2 in ("LTD"."S.A.",...)  
0
 
jogosCommented:
not a '.' but a ',' ofcourse
WHERE col2 in ("LTD","S.A.",...)  
0
 
dbaSQLAuthor Commented:
hmmm... i hadn't thought of that.  but, i need to detect it.  i won't be able to get back onto this until tonight, but i will let you know how things progress.
0
 
jogosCommented:
To find which content in col2 probably a select to find Col1's ending on a possible separator can be helpfull (or just not ending at a character.
0
 
dbaSQLAuthor Commented:
this will work just fine, jogos.  thank you for your suggestion
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now