SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 SSIS Expression to Merge Two Columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 11/07/2013 :  12:33:14  Show Profile  Reply with Quote


I have a .CSV import that I will be performing a series of Transformations on. The first Transformation that I need to do is to merge two City Columns into 1 column.

The data that I have looks like this.

| City1 | City2 |
|Wichita| |
| |Houston|
| |Chicago|
|Denver | |

The required output should be,

| City |
|Wichita|
|Houston|
|Chicago|
|Denver |

I want to keep this as an SSIS Derived Column Expression so that I can tie it to the rest of the transformation that I need to perform.

I already went back to the vendor and asked them to correct the data, they denied it. Now it's up to me to correct the dirty data so that we can use it in a series of reports.

Thank you in advance for any support.


Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 11/07/2013 :  13:09:53  Show Profile  Reply with Quote
expression that I used:
City2 == "" ? City1 : City2

City2 becomes the column that I use.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/07/2013 :  13:16:08  Show Profile  Reply with Quote
Are you sure that they both wont have data in any case? what if both City1 and City2 have different data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 11/08/2013 :  11:24:39  Show Profile  Reply with Quote
I checked the data.

I ran into that with City1, it had Suite Numbers in it and I didn't want that. Plus, it wouldn't pull the data from City2 if the Suite Numbers were there. But i noticed that City 2 was always populated with a city name. So I just flipped it and pulled the City name from City1 into City 2 and used City2 as my clean column.

I have a years worth of data that I compared and it was all the same. City2 was the column to us and if City 2 was blank, I pulled the name fromCity1 over to give me a complete column.



Brian D. Brubaker
Business Intelligence Analyst
Viega LLC
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000