Import Excel Data when column length is more than 255 using Script Component as Source

To loading the excel file into the database we are using the Source as Excel Source.

 

But the problem occurs when the Columns have more than 255 characters. It is failing with truncation.

Read: ImportExcel Data when column length is more than 255

https://bageshkumarbagi-msbi.blogspot.com/2019/12/import-excel-data-when-column-length-is.html

Problem with the above solution that some time we don’t have access or authorities to modify the Server Registry value.

To overcome this issue we will use the Script task or script component.

Read: ImportExcel Data when column length is more than 255 using Script Task

https://bageshkumarbagi-msbi.blogspot.com/2019/12/import-excel-data-when-column-length-is_22.html

If we need to do some transformation on the excel data then we need to use the Script component.

Let’s see the demo

My excel file.

 

And database table

 

I am taking a Data flow task.

 

Taking a script component as a source.

 

Now I am creating output columns.

 

Go to script

 

I am writing below C# code to read the excel file.

public override void CreateNewOutputRows()     {                 string FileName = "product.xls";         string strConn;         strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=J:\\SSIS1\\excel file\\product.xls;Extended Properties=\"Excel 8.0;HDR=yes;IMEX=0\"";         DataSet output = new DataSet();         using (OleDbConnection conn = new OleDbConnection(strConn))         {             conn.Open();             DataTable schemaTable = conn.GetOleDbSchemaTable(                 OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });             foreach (DataRow schemaRow in schemaTable.Rows)             {                 string sheet = schemaRow["TABLE_NAME"].ToString();                 if (!sheet.EndsWith("_"))                 {                     try                     {                         DataTable outputTable = new DataTable();                         string Query = "SELECT * FROM [" + sheet + "]";                         OleDbCommand Ecom = new OleDbCommand(Query, conn);                         DataSet ds = new DataSet();                         OleDbDataAdapter oda = new OleDbDataAdapter(Query, conn);                         oda.Fill(ds);                         DataTable Exceldt = ds.Tables[0];                         foreach (DataRow dr in Exceldt.Rows)                         {                             Output0Buffer.AddRow();                             Output0Buffer.ProductName = dr["productName"].ToString();                             Output0Buffer.Cost = dr["cost"].ToString();                             Output0Buffer.Description = dr["Description"].ToString();                         }                         conn.Close();                     }                     catch (Exception ex)                     {                         throw new Exception(ex.Message + string.Format("Sheet:{0}.File:F{1}", sheet, FileName), ex);                     }                 }             }         }     }

Save and close it.

Now I am taking the OLEDB destination to load this data into the database.

         

     

Now the package is ready to run.

Before running this package records in the table.

       

Now running this package.

 

See the records in the database.