Load Text File Unicode and Non-Unicode Using Integration Service - TechNet Articles - United States (English) - TechNet Wiki

• Prepare file text
• Create Folder Upload and Folder Success Upload
• Prepare Package
• Variable for Path and File Name.
• Script Task for check type file
• Create two Data Flow Task component for load file text.
• Connect the line precedence contain editor using expression
• For each loop container for looping any file

Prepare two file text (*.txt) Unicode and Non-Unicode.

Create data in file text with the delimiter pipeline, comma, or semicolon. In case I use pipe line.  (UploadTest_ANSI1.txt, UploadTest_UNI1.txt)

Create folder

Create folder to take the file text, in this case when file text finish execute then file text move the other folder.

(Upload File, Success Upload)

Prepare Package

Create solution integration in SSDT (SQL Server Data Tools), so create Flat File Connection Manager (Source Unicode, Source ANSI, and Destination ANSI). In this example, I want to destination to *.txt ANSI. Can use any type data destination.

Variable Path and File Name

This step can be used  dynamic and effective with use parameter/ variables. Components to be made (Script Task, Data Flow, For Each Loop Container, will reference the parameter/variable).

Script task checking type data

Script task be used scanning type data with read contain data using file stream, and feedback to variable.

Code :

string filepath = Dts.Variables["PathSource"].Value.ToString() + Dts.Variables["FileName"].Value.ToString();

            //MessageBox.Show(filepath);

            FileStream file = File.OpenRead(@filepath);

            int[] checkfile = new int[4];

            for (int i = 0; i < checkfile.Length; ++i)

            {

                checkfile[i] = file.ReadByte();

            }

            file.Close();

            if (checkfile[0] ily:consolas;">            for (int i = 0; i < checkfile.Length; ++i)

            {

                checkfile[i] = file.ReadByte();

   &nb== 0xEF && checkfile[1] == 0xBB && checkfile[2] == 0xBF)

                Dts.Variables["vEncoding"].Value = "UTF-8";

                //MessageBox.Show("8");

            else if (checkfile[0] == 0xFE && checkfile[1] == 0xFF)

                Dts.Variables["Check_Encoding"].Value = "UTF-16 BIG";

                //MessageBox.Show("16 Big");

            else if (checkfile[0] == 0xFF && checkfile[1] == 0xFE)

                Dts.Variables["Check_Encoding"].Value = "UTF-16 LITTLE";

                //MessageBox.Show("8");

            else if (checkfile[0] == 0x00 && checkfile[1] == 0x00 && checkfile[2] == 0xFE && checkfile[3] == 0xFF)

                Dts.Variables["Check_Encoding"].Value = "UTF-32 BIG";

                //MessageBox.Show("32 Big");

            else if (checkfile[0] == 0x00 && checkfile[1] == 0x00 && checkfile[2] == 0xFF && checkfile[3] == 0xFE)

                Dts.Variables["Check_Encoding"].Value = "UTF-32 LITTLE";

               // MessageBox.Show("32 Litle");

            else

                Dts.Variables["Check_Encoding"].Value = "ANSI";

                //MessageBox.Show("ANSI");

              Dts.TaskResult = (int)ScriptResults.Success;

Data Flow Component

Create data flow component  for load data from source to destination. Because I want to transform all file to ANSI, I use data conversion component in data flow Unicode to transform type data DT_WSTR to DT_STR.

Precedence Contain Expression

After finish create data flow, so connect line from script component to two data flow. Right click the line, edit and will appear Precedence Contain Editor.  Create expression which determine the file text Unicode or Non-Unicode.

For each loop container

The advantage for this component make it easy to execute any file, the component checking some folder and execute all file in this folder.

Last step execute package for view load file and the result.

Before Execute

After Execute
Last step execute package for view load file and the result.

After Execute

 Download Sample