February 19, 2014 at 9:38 am
We have an export that runs via an SSIS package every morning, we have had no issues with the data up until a few weeks ago when suddenly the Name field started to export random characters. For example instead of Mr Joe Bloggs we get Mr ¯È䔀°dzæ©à…µ,"
The export usually contains around 40,000 records and the majority of them are fine, there are just a handful that come out like this.
I have checked the Stored Procedure that the SSIS package uses and the data comes out of that correctly, the package then puts the data straight into the file. There are no conversions/lookup etc so I am at a loss as to how this is happening.
Can anyone help?
February 19, 2014 at 11:22 am
What are the TextQualifier & ColumnDelimiter properties set to currently?
February 19, 2014 at 2:10 pm
Are you exporting to the right format? UTF-8 vs ANSI
What is the data type of the columns?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 20, 2014 at 4:16 am
Text Qualifier is " and Column Delimiter is Comma [,]
The code page for the export is 1252 (ANSI - Latin I) and the field data types are all set to string [DT_STR]
February 20, 2014 at 4:21 am
Seems like some unicode characters got stuck in your name column.
What is the data type in the SQL Server table?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 20, 2014 at 5:35 am
If you open the .dtsx file in a notepad, what does the text qualifier property show
<DTS:Property DTS:Name="TextQualifier" xml:space="preserve">_x0022_</DTS:Property>
OR
<DTS:Property DTS:Name="TextQualifier" xml:space="preserve">"</DTS:Property>
If it is the first option then try changing to the second option
February 25, 2014 at 7:29 am
The Name field in the SQL Table is varchar, I've checked the data within the database and the names look fine.
February 25, 2014 at 7:37 am
I have changed the .dtsx file to the second option and re-deployed the package, unfortunately I cannot run the export during office hours as it may lock up the system therefore I will come back tomorrow once the schedule job has run overnight to update.
Thanks 🙂
February 26, 2014 at 2:44 am
Unfortunately we still had the same issue this morning
February 26, 2014 at 2:48 am
Which provider do you use when reading data from SQL Server?
If you put a data viewer right after the source, is the data still correct?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 26, 2014 at 3:32 am
When we added a data viewer the data is correct, the issue seems to occur when the data is added to the csv file.
February 26, 2014 at 3:36 am
Are you sure the file is ANSI?
Open it up in a decent text editor such as Notepad++ (it is free) and check the encoding.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 26, 2014 at 5:12 am
I've opened the file in Ultra Edit and it is definitely ANSI
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply