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