I have the following table :

CREATE TABLE DI_Simulation 
(
    [city] nvarchar(255), 
    [profession] nvarchar(255) 
);

I load the data from an URL with a Script task where I created a class Simulation and added two string attributes. I then deserialize the downloaded JSON data and create output rows.

I specify that the output columns city and profession are of type DT_WSTR but the following characters [é,à,è,...] are always replaced...

I tried different collations on both columns but no changes were seen. I also tried forcing UTF8 conversion on the Script Task but that also didn't work.

Any suggestions ?

EDIT: I should also mention that I have other tables where the insertion is made correctly, but this one especially has this issue, which I'm thinking the Script Task has something to do with it.

ServicePointManager.Expect100Continue = true;
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12 | SecurityProtocolType.Ssl3;

// Convert json string to .net object using the old school JavaScriptSerializer class
string Uri = "https://....";
JavaScriptSerializer serialize = new JavaScriptSerializer
{
    MaxJsonLength = Int32.MaxValue,
};

var simulation = serialize.Deserialize<Simulation[]>(DownloadJson(Uri));

EDIT 2:

WebClient client = new WebClient();
Stream stream = client.OpenRead(Url);
StreamReader streamreader = new StreamReader(stream, System.Text.Encoding.GetEncoding(1252));
var ags = streamreader.ReadToEnd();

/*System.IO.File.WriteAllText(@"C:\Users\hhamdani\Desktop\Data Integration Objetcs\simulation_data.json",
ags,
System.Text.Encoding.GetEncoding(1252));*/
var simulation = serialize.Deserialize<Simulation[]>(ags);

Instead of downloading with DownloadJson, I used streamreader to get the Json Data from the URL and forced the Encoding, when I save the data on a txt file it's good, but on the Database it's the same issue.

CodePudding user response：

Works fine from a script source component based on my reproduction

Setup

Table creation

A trivial table with two columns

CREATE TABLE dbo.[SO_71842511] (
    [TestCase] int,
    [SomeText] nvarchar(50)
)

SCR Do Unicode

Proof that we can inject unicode characters into the data flow task from a script source.

Define the Script Task as a Source. Add 2 columns to the output, one int, one DT_WSTR

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void CreateNewOutputRows()
    {
        Output0Buffer.AddRow();
        Output0Buffer.SomeText = "e e plain";
        Output0Buffer.TestCase = 0;

        Output0Buffer.AddRow();
        Output0Buffer.SomeText = "é e forward";
        Output0Buffer.TestCase = 1;

        Output0Buffer.AddRow();
        Output0Buffer.SomeText = "à a back";
        Output0Buffer.TestCase = 2;

        Output0Buffer.AddRow();
        Output0Buffer.SomeText = "è e backward";
        Output0Buffer.TestCase = 3;
    }
}

Results

CodePudding user response：

WebClient client = new WebClient();
Stream stream = client.OpenRead(Url);
StreamReader streamreader = new StreamReader(stream, System.Text.Encoding.UTF8);
var ags = streamreader.ReadToEnd();

This did the job for me. Thanks @billinkc