SSIS 2015 Script task convert text file to UTF8 in C# or VB
<p>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.</p>

<p>MSDN has an "IO Streaming example" <a href="https://azure.microsoft.com/en-us/documentation/articles/sql-data-warehouse-load-polybase-guide/" rel="nofollow">HERE</a> 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.</p>

<p>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?</p>

<h2>PowerShell Code from MSDN</h2>

<pre><code>#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("&lt;MySrcFolder&gt;","&lt;MyUtf8stage&gt;.txt")

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

#Set target file path and file name
$tgt = [System.IO.Path]::Combine("&lt;MyDestFolder&gt;","&lt;MyFinalstage&gt;.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()
</code></pre>

<hr>

<h2>Update</h2>

<p>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:</p>

<pre><code>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() &gt;= 0)
            {
                writer.WriteLine(reader.ReadLine());
            }
        }
        Dts.TaskResult = (int)ScriptResults.Success;
</code></pre>