Loading the fixed length file data in the table using script task in SSIS

We can load the fixed-width length fie using the script task also.

In this demo, we will see how to load this file using the script task.

File data.  

Mapping of this file

Name: 0-19

Mobile: 19-34

Address: 34-60

 Table where we are loading this file.  

In SSDT we are taking Script task.  

In this script task we are wring the below code.

public void Main()                                 {             try             {                 // TODO: Add your code here                                 DataTable dt = new DataTable();                 using (StreamReader reader = new StreamReader("H:\\SSIS1\\Source\\emp_Info_Fixed_width.txt")) //Reading the CSV file                 {                     // Adding the Header into the data table                                         int x  = reader.ReadLine().Length; //Just reading the first line of the file                     //Adding column in the header                     dt.Columns.Add("name");                      dt.Columns.Add("Mob");                       dt.Columns.Add("Emp_Add");                       while (!reader.EndOfStream)                     {                         string rows = reader.ReadLine().ToString();// reading the line and extracting the data from the line                         DataRow dr = dt.NewRow();                                                dr["name"] = rows.Substring(0,19).ToString();                         dr["Mob"] = rows.Substring(19,15).ToString();                         dr["Emp_Add"] = rows.Substring(34,25).ToString();                         dt.Rows.Add(dr);                                               }                 }                   //  creating connection to the data base                 string connection = "Data Source=BAGESH-PC\\BAGESHDB;database=Test;integrated Security=SSPI;";                 SqlConnection con = new SqlConnection(connection);                 //create object of SqlBulkCopy which help to insert                  SqlBulkCopy obj = new SqlBulkCopy(con);                   //assign Destination table name                  obj.DestinationTableName = "emp";                   //Mapping the Source column with destination column.                 //Keep in mind we need to provide the correct mapping other wise it will throw an error                 obj.ColumnMappings.Add("name", "name");                 obj.ColumnMappings.Add("Mob", "Mob");                 obj.ColumnMappings.Add("Emp_Add", "Emp_Add");                                //Opining the connection                 con.Open();                 //insert bulk Records into DataBase.                  obj.WriteToServer(dt);                 //Closing the conntion                 con.Close();                 Dts.TaskResult = (int)ScriptResults.Success;             }                 //If any error comes loging that error             catch (Exception ex)             {                 Dts.Events.FireError(0, "Fire Error", "An error occurred: " + ex.Message.ToString(), "", 0);                   Dts.TaskResult = (int)ScriptResults.Failure;             }                                 }

Build this code and close this window.

Now package is ready to run.

Before run records in the table.

 

Running the package. 

Executed successfully.

See the records in the table.

  

Data loaded successfully in the table.