December 14, 2011 at 12:25 am
Hi
Im reading in data in from a text file. This is SMS replies from clients. My whole database is designed to cater only for non unicode characters, but every once in a while I get a problem with some one replying with something like below (Dont know what it is or how they do it).
The package fails and I have to open up the CSV file and take out the reply. What would be a nice way of working around the problem? I can change the staging table to cater for nvarchar, but i still need to discard this response in anycase, so if I can discard this reply when loading from the flat file it would be first prize.
","zÿÿí¶Ûmÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿí¿ÿmÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿí¿ÿmÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿí¶Ûmÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿíu×]ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿHi what ar"
December 14, 2011 at 12:38 am
chris.stuart (12/14/2011)
HiIm reading in data in from a text file. This is SMS replies from clients. My whole database is designed to cater only for non unicode characters, but every once in a while I get a problem with some one replying with something like below (Dont know what it is or how they do it).
The package fails and I have to open up the CSV file and take out the reply. What would be a nice way of working around the problem? I can change the staging table to cater for nvarchar, but i still need to discard this response in anycase, so if I can discard this reply when loading from the flat file it would be first prize.
","zÿÿí¶Ûmÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿí¿ÿmÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿí¿ÿmÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿí¶Ûmÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿíu×]ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿHi what ar"
How does the package fail? Which errors do you get?
And more importantly, which component throws the error?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 14, 2011 at 12:55 am
Hi Koen, thanks for the always quick replies!
The component that fails is the dataflow task and in there the flat file source. (the data conversion for column ".." returned status value 4 and text "text was truncated or one or more characters had no match in the target cod page")
Im busy testing and have now changed the staging table to nvarchar and the codepage of the flat file to 65001 which I believe is the unicode for ascii.
The package loaded the data into the table and Im busy checking on the load itself.
Im think of just running my user function which just returns ascii characters on the table, and then to load it further in to the production database. Should work, but would still prefer no discard the rows in the loading of the flat file.
thansk
December 14, 2011 at 1:27 am
Is it possible to have error handling on the source and to redirect all error rows to somewhere else?
If that works, you could put a derived column on the error path to replace the reply with something like "Text not readable" and then union all it back to the rest of the dataflow.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 14, 2011 at 2:20 am
Hi Koen.
Thanks for the suggestion. Sometimes its the small things that you over look. Can you believe that I forgot about the directing of an error row in the component. I normally just load the data into a table first and then do some error checking in SQL, and normally not in SSIS.
But I've changed the Flat file codepage back to 1252, the table in staging db is varchar and the error flat file is also 1252.
Working, read in 573 rows, and re-directed 5 rows to the error file.
Many thanks once again!
December 14, 2011 at 3:46 am
chris.stuart (12/14/2011)
Many thanks once again!
Great! Glad that you got it working. And now you can drink coffee instead of manually editing CSV files 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 14, 2011 at 4:45 am
Some say when you have some difficult to do, give it to a lazy person, because the lazy person will find a easy way of doing. Im a lazy person... I want to click one button to launch the space shuttle...
Thanks again, and I'll enjoy the coffee
December 14, 2011 at 5:17 am
chris.stuart (12/14/2011)
Some say when you have some difficult to do, give it to a lazy person, because the lazy person will find a easy way of doing.
That's good advice.
I consider myself lazy as well 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply