Import Excel Data when column length is more than 255 using Script Task

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 sometimes we don’t have access or authorities to modify the Server Registry value.

To overcome this issue we will use the Script task.

Let’s see the demo

My excel file.

 

And database table

 

I am taking the script task.

 

Writing the below C# code to read excel file and insert theses record into the database.

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();                 SqlConnection con = new SqlConnection("Integrated security=true;Initial Catalog=Test;Data source=DESKTOP-DQLCKGK\\BAGESH");                 SqlCommand cmd;                 con.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)                             {                                         string s = "insert into product(productName,cost,Description) values(@p1,@p2,@p3)";                                 cmd = new SqlCommand(s, con);                                 cmd.Parameters.AddWithValue("@p1", dr["productName"].ToString());                                 cmd.Parameters.AddWithValue("@p2", dr["cost"].ToString());                                 cmd.Parameters.AddWithValue("@p3", dr["Description"].ToString());                                 cmd.CommandType = CommandType.Text;                                 int i = cmd.ExecuteNonQuery();                                                      }                             con.Close();                             conn.Close();                         }                         catch (Exception ex)                         {                throw new Exception(ex.Message + string.Format("Sheet:{0}.File:F{1}", sheet, FileName), ex);                                                     }                     }                 }             }

Save and close.

Now run this package.

Before running,  records into the table.

 

Running now.

Now see the records into the table.

 

Hope this will help when you are working with the excel file load.