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.
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.
