| Author |
Topic |
|
albertkohl
Aged Yak Warrior
740 Posts |
 Posted - 2010-02-15 : 12:06:15
|
| can someone point me in the right direction on how to import a flat file, that has 2 columns, but during the import, combine those two columns into one? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-15 : 12:09:32
|
| you need to use a data flow task with flat file source which points to your file followed by derived column transformation to combine both column values to one and finally an oledb destination to point to your sql destination table.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-02-15 : 12:26:36
|
| i think i got it, but i get this error: Columns "tl1" and "TL1" cannot convert between unicode and non-unicode string data types.my Derived Column Transformation has the expression: AreaCode + Rootand the data type is Unicode (wont let me edit) how do i get it to convert properly? (by the way, the other columns are both DT_String 52) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-15 : 12:28:39
|
| cast it using (DT_WSTR,<length>)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-02-15 : 12:34:26
|
| can you by chance give me the full expression? every time i try i get an error :( |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-15 : 12:38:04
|
| which of columns is unicode?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-02-15 : 12:44:20
|
| the new one is showing up unicodethe import columns are both String [DT_STR]on the derived task, i have:Derived Column Name | Derived Column | Expression | Data TypeTL1 | <add as new column> | AreaCode + Root | Unicode string [DT_WSTR]the column it's going into is tl1 varchar(10) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-15 : 12:47:31
|
quote:
Originally posted by albertkohl
the new one is showing up unicodethe import columns are both String [DT_STR]on the derived task, i have:Derived Column Name | Derived Column | Expression | Data TypeTL1 | <add as new column> | AreaCode + Root | Unicode string [DT_WSTR]the column it's going into is tl1 varchar(10)
(DT_WSTR,<length>) AreaCode + (DT_WSTR,<length>) Root------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-02-15 : 12:56:40
|
| still getting the same error |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-02-15 : 12:57:23
|
| and also, the data type is still set to Unicode |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-15 : 13:04:53
|
| oops i think you want other way around ie. unicode to non-unicode so my question is does you field have some unicode values stored? if yes, how do you store it in string field? i think to be safer your destination field should be unicode.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-02-15 : 13:19:42
|
| LOL i dont even know what the difference is.it's a flat text file like:555,5555555{LF}and i want to mesh column 1 and 2 into a column on a table. these are basically phone numbers of people who do wanna be called by us, we update the list every week, and i just wanna program everything into a one-click package. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-15 : 13:24:16
|
| ok. in that case just do like(DT_STR,5,1252)AreaCode + (DT_STR,5,1252) Root------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-02-15 : 13:56:48
|
| pasting that into the expression still shows the same error, and datatype is still set to unicode :( |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-02-15 : 13:57:20
|
| this is what i show in my derived column transformation editor: tl1 <add as new column> (DT_STR,5,1252)AreaCode + (DT_STR,5,1252)Root Unicode string [DT_WSTR] 10 |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-02-15 : 16:43:25
|
| that's wierd. it's like because of the conversion, it was trying to make the new column Unicode, but i change the derived transformation to REPLACE AREACODE with areacode+root and then inserted that row into my database, that seems to work fine.LLAME! any ideas on why SSIS was trying to force the unicode? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-16 : 01:26:51
|
quote:
Originally posted by albertkohl
that's wierd. it's like because of the conversion, it was trying to make the new column Unicode, but i change the derived transformation to REPLACE AREACODE with areacode+root and then inserted that row into my database, that seems to work fine.LLAME! any ideas on why SSIS was trying to force the unicode?
do you mean to say that Areacode was originally interpreted as non unicode in the metadata?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-02-16 : 11:01:32
|
| yesir. |
|
|
|
|
SSIS Transformations