SSIS: Simple File Encoding Converter Script Task

Here is a very simple SSIS script task to convert a single file from one code page to another. We will be converting any file that is UTF-8 to ANSI 1252. The original file will be replaced with the converted file.

Start with an SSIS script task and three read-only variables: User::Module, User::DataFlowInputDestFileName, and User::FileEncoding. The first variable is an arbitrary name for the package supplied by the user. The second is the full path and name of the file we want to convert. The third variable is the existing encoding of the file to convert e.g. UTF-8, ANSI, etc.

The code:


using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Text;

public void Main()
{
 Variables vars = Dts.Variables;
 
 string component = vars["System::TaskName"].Value.ToString();
 string fileEncoding = vars["User::FileEncoding"].Value.ToString();
 string filePath = vars["User::DataFlowInputDestFileName"].Value.ToString();
 string fileExt = Path.GetExtension(filePath);
 bool fireAgain = false;
 
 if (String.Compare(fileEncoding, "UTF-8", true) == 0)
 {
  try
        {
   // /////////////////////////////////////////////////////////////////
   // Setup the file streams for the conversion
   // /////////////////////////////////////////////////////////////////
   Dts.Events.FireInformation(0, component,
                    "Converting input file",
                    String.Empty, 0, ref fireAgain);
   
   StreamReader fileStream = new StreamReader(filePath);
   StreamWriter ansiWriter = new StreamWriter(
          filePath.Replace(fileExt, "-ansi" + fileExt),
          false,
          Encoding.GetEncoding(1252));
    
   // /////////////////////////////////////////////////////////////////
   // Read the UTF8 file and convert it
   // /////////////////////////////////////////////////////////////////
   while (fileStream.Peek() >= 0)
   {
    ansiWriter.WriteLine(fileStream.ReadLine());
   }

   // /////////////////////////////////////////////////////////////////
   // Close and dispose of the file streams
   // /////////////////////////////////////////////////////////////////
   Dts.Events.FireInformation(0, component,
                    "Closing and disposing of file streams",
                    String.Empty, 0, ref fireAgain);
   
   fileStream.Close();
   fileStream.Dispose;
   ansiWriter.Close();
   ansiWriter.Dispose;

   // /////////////////////////////////////////////////////////////////
   // Delete the original file. One could do a number of things here
   // such as keeping both files so the original is on hand in case
   // a something became corrupted.
   // /////////////////////////////////////////////////////////////////
   if (File.Exists(filePath))
   {
    Dts.Events.FireInformation(0, component,
                        "Deleting original file",
                        String.Empty, 0, ref fireAgain);
     
    File.Delete(filePath);
   }

   // /////////////////////////////////////////////////////////////////
   // Rename the converted file to the original file
   // /////////////////////////////////////////////////////////////////
   Dts.Events.FireInformation(0, component,
                    "Renaming temporary converted file to orginal file",
                    String.Empty, 0, ref fireAgain);
   
   File.Move(filePath.Replace(fileExt, "-ansi" + fileExt), filePath);

  // /////////////////////////////////////////////////////////////////
  // Exceptions
  // ///////////////////////////////////////////////////////////////// 
  catch (COMException cexp)
  {
   Dts.Events.FireError(cexp.ErrorCode, component, "Exception occured : "
          + cexp.TargetSite + cexp, String.Empty, 0);
   Dts.TaskResult = (int)ScriptResults.Failure;
  }
  catch (System.ArgumentException argex)
  {
   Dts.Events.FireError(0, component, argex.Message, String.Empty, 0);
   Dts.TaskResult = (int)ScriptResults.Failure;
  }
  catch (DtsRuntimeException runex)
  {
   Dts.Events.FireError(0, component, runex.Message, String.Empty, 0);
   Dts.TaskResult = (int)ScriptResults.Failure;
  }
  finally
  {
   if(!fileStream.IsClosed())
   {
    fileStream.Close();
    fileStream.Dispose;
   }
   
   if(!ansiWriter.IsClosed())
   {
    ansiWriter.Close();
    ansiWriter.Dispose;
   }
  }
 }
 // TODO: Add your code here
 Dts.TaskResult = (int)ScriptResults.Success;
}

Keep in mind that this is a naive approach to code page conversions and we have not touched on subjects such as the use of BOMs (Byte Order Marks). It should start you in the right direction if you are importing a flat file that needs to be converted before running it through the rest of your package.

Please follow and like us:

Leave a Reply

Your email address will not be published. Required fields are marked *