Brady.Smith Brady.Smith - 1 month ago 30
C# Question

SSIS 2015 Script task convert text file to UTF8 in C# or VB

I want to convert my resulting txt file into a UTF8 formatted file so I can load it into my Azure SQL DW via Polybase. It is required the source file be in UTF8.

MSDN has an "IO Streaming example" HERE works perfectly for a single job. I am trying to architect an SSIS solution for around 30 tables though. I believe using this method would cause a race condition where the PS script will be locked by 1 SSIS package when another SSIS package needs it.

I am a sql dev, not a .NET dev so please forgive me. How would one convert the above to an SSIS C# Script task assuming I know how to pass parameters into the Script task?

PowerShell Code from MSDN



#Static variables
$ascii = [System.Text.Encoding]::ASCII
$utf16le = [System.Text.Encoding]::Unicode
$utf8 = [System.Text.Encoding]::UTF8
$ansi = [System.Text.Encoding]::Default
$append = $False

#Set source file path and file name
$src = [System.IO.Path]::Combine("<MySrcFolder>","<MyUtf8stage>.txt")

#Set source file encoding (using list above)
$src_enc = $ascii

#Set target file path and file name
$tgt = [System.IO.Path]::Combine("<MyDestFolder>","<MyFinalstage>.txt")

#Set target file encoding (using list above)
$tgt_enc = $utf8

$read = New-Object System.IO.StreamReader($src,$src_enc)
$write = New-Object System.IO.StreamWriter($tgt,$append,$tgt_enc)

while ($read.Peek() -ne -1)
{
$line = $read.ReadLine();
$write.WriteLine($line);
}
$read.Close()
$read.Dispose()
$write.Close()
$write.Dispose()





Update



I found a similar post which I was able to tweak to my needs, I swear I searched high and low before posting. Anyway here is what IS working for me. If you see anyway to improve it please share:

public void Main()
{
//$Package::SourceSQLObject = tablename
//$Package::StageFile_DestinationFolderPath = rootpath eg "C:\temp\"

string path = (string)Dts.Variables["$Package::StageFile_DestinationFolderPath"].Value;
string name = (string)Dts.Variables["$Package::SourceSQLObject"].Value;
string from = Path.Combine(path, name) + ".csv";
string to = Path.ChangeExtension(from, "txt");
Dts.Log("Starting " + to.ToUpper(), 0, null);
using (StreamReader reader = new StreamReader(from, Encoding.ASCII, false, 10))
using (StreamWriter writer = new StreamWriter(to, false, Encoding.UTF8, 10))
{
while (reader.Peek() >= 0)
{
writer.WriteLine(reader.ReadLine());
}
}
Dts.TaskResult = (int)ScriptResults.Success;

Answer
public void Main()
    {
        //$Package::SourceSQLObject = tablename
        //$Package::StageFile_DestinationFolderPath = rootpath eg "C:\temp\"

        string path = (string)Dts.Variables["$Package::StageFile_DestinationFolderPath"].Value;
        string name = (string)Dts.Variables["$Package::SourceSQLObject"].Value;
        string from = Path.Combine(path, name) + ".csv";
        string to = Path.ChangeExtension(from, "txt");
        Dts.Log("Starting " + to.ToUpper(), 0, null);
        using (StreamReader reader = new StreamReader(from, Encoding.ASCII, false, 10))
        using (StreamWriter writer = new StreamWriter(to, false, Encoding.UTF8, 10))
        {
            while (reader.Peek() >= 0)
            {
                writer.WriteLine(reader.ReadLine());
            }
        }
        Dts.TaskResult = (int)ScriptResults.Success;