Funny asterix character in SSIS

Question

Funny asterix character in SSIS

frdrckmitchell7

Ten Centuries

Points: 1351
More actions
August 4, 2016 at 8:40 am

#310220

Hi guys
I have a csv file and column A has numeric values (1,2,3,etc), but when I create a File Connection Manager in SSIS and preview the data there are funny asterixis in the data, which I cannot see in the physical csv file and now look like this *1, *2, *3, etc. The table I am pulling the data in is an int for this column. Can anyone please explain how do I go about fixing this?
Really appreciate the help.
Many thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246987 More actions · Answer 1

frdrckmitchell7 (8/4/2016)
Hi guys
I have a csv file and column A has numeric values (1,2,3,etc), but when I create a File Connection Manager in SSIS and preview the data there are funny asterixis in the data, which I cannot see in the physical csv file and now look like this *1, *2, *3, etc. The table I am pulling the data in is an int for this column. Can anyone please explain how do I go about fixing this?
Really appreciate the help.
Many thanks

Firstly, I think you mean 'asterisks'. Asterix is a French comic character.

If you open the CSV in a decent text editor (I use Notepad++) and select View / Show Symbol / Show All Characters, do you see anything strange in there?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

frdrckmitchell7 Ten Centuries Points: 1351 More actions · Answer 2

Hi, thanks for replying.

LOL, Yes, I am re-reading a lot of Asterix books lately so...

Okay, I will open it up in notebook++ and see what happens.

Thanks.

frdrckmitchell7 Ten Centuries Points: 1351 More actions · Answer 3

This is some sample data and what it looks like in UltraEdit/Notebook++:

"1|EM|0||Ken|J|Sánchez||0||?<IndividualSurvey xmlns=""http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey""><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>|92C4279F-1207-48A3-8448-4636514EB7E2|2009-01-07 00:00:00|"

"2|EM|0||Terri|Lee|Duffy||1||?<IndividualSurvey xmlns=""http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey""><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>|D8763459-8AA8-47CC-AFF7-C9079AF79033|2008-01-24 00:00:00|"

"3|EM|0||Roberto||Tamburello||0||?<IndividualSurvey xmlns=""http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey""><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>|E1A2555E-0828-434B-A33B-6F38136A37DE|2007-11-04 00:00:00|"

"4|EM|0||Rob||Walters||0||?<IndividualSurvey xmlns=""http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey""><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>|F2D7CE06-38B3-4357-805B-F4B6B71C01FF|2007-11-28 00:00:00|"

"5|EM|0|Ms.|Gail|A|Erickson||0||?<IndividualSurvey xmlns=""http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey""><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>|F3A3F6B4-AE3B-430C-A754-9F2231BA6FEF|2007-12-30 00:00:00|"

"6|EM|0|Mr.|Jossef|H|Goldberg||0||?<IndividualSurvey xmlns=""http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey""><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>|0DEA28FD-EFFE-482A-AFD3-B7E8F199D56F|2013-12-16 00:00:00|"

"7|EM|0||Dylan|A|Miller||2||?<IndividualSurvey xmlns=""http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey""><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>|C45E8AB8-01BE-4B76-B215-820C8368181A|2009-02-01 00:00:00|"

"8|EM|0||Diane|L|Margheim||0||?<IndividualSurvey xmlns=""http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey""><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>|A948E590-4A56-45A9-BC9A-160A1CC9D990|2008-12-22 00:00:00|"

"9|EM|0||Gigi|N|Matthew||0||?<IndividualSurvey xmlns=""http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey""><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>|5FC28C0E-6D36-4252-9846-05CAA0B1F6C5|2009-01-09 00:00:00|"

"10|EM|0||Michael||Raheem||2||?<IndividualSurvey xmlns=""http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey""><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>|CA2C740E-75B2-420C-9D4B-E3CBC6609604|2009-04-26 00:00:00|"

"11|EM|0||Ovidiu|V|Cracium||0||?<IndividualSurvey xmlns=""http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey""><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>|D2CC2577-EF6B-4408-BD8C-747337FE5645|2010-11-28 00:00:00|"

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 4

Thinking out loud, as it were... is your CSV stored in a Unicode-type format, and, if so, is SSIS aware of this?

Thomas Rushton
blog: https://thelonedba.wordpress.com

frdrckmitchell7 Ten Centuries Points: 1351 More actions · Answer 5

Yes, I think so. Checked the unicode check box in SSIS

Phil Parkin SSC Guru Points: 246987 More actions · Answer 6

frdrckmitchell7 (8/5/2016)
Yes, I think so. Checked the unicode check box in SSIS

If you uncheck it, what happens?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel