Loading the excel file using SqlBulkCopy in script task in SSIS

There are many ways to load the excel sheet into the database. In this demo, we will see how we will read the excel file and load this file using SqlBulkCopy in the script task.

Below is the excel file  

We need to load into the below table. 

Let’s see how we will load this file.

We need to create an ADO.Net connection manager and taking some variable as below.

 

Now we are taking the script task.

  

We are writing the below code to load this file.

public void Main()                                 {                                                 // TODO: Add your code here             try             {             string FolderPath=Dts.Variables["User::Source_Dir"].Value.ToString();             string TableName = Dts.Variables["User::TableName"].Value.ToString();             string SchemaName = Dts.Variables["User::SchemaName"].Value.ToString();                     var directory = new DirectoryInfo(FolderPath);             FileInfo[] files = directory.GetFiles();              //Declare and initilize variables             string fileFullPath = "";              //Get one Book(Excel file at a time)             foreach (FileInfo file in files)             {                 fileFullPath = FolderPath + "\\" + file.Name;                 //Create Excel Connection                 string ConStr;                 string HDR;                 HDR = "YES";                 ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileFullPath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";                 OleDbConnection cnn = new OleDbConnection(ConStr);                 //Get Sheet Name                 cnn.Open();                 DataTable dtSheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);                 string sheetname;                 sheetname = "";                 foreach (DataRow drSheet in dtSheet.Rows)                 {                     if (drSheet["TABLE_NAME"].ToString().Contains("$"))                     {                         sheetname = drSheet["TABLE_NAME"].ToString();                          //Load the DataTable with Sheet Data so we can get the column header                         OleDbCommand oconn = new OleDbCommand("select top 1 * from [" + sheetname + "]", cnn);                         OleDbDataAdapter adp = new OleDbDataAdapter(oconn);                         DataTable dt = new DataTable();                         adp.Fill(dt);                         cnn.Close();                         string ExcelHeaderColumn = "";                         string SQLQueryToGetMatchingColumn = "";                         for (int i = 0; i < dt.Columns.Count; i++)                         {                             if (i != dt.Columns.Count - 1)                                 ExcelHeaderColumn += "'" + dt.Columns[i].ColumnName + "'" + ",";                             else                                 ExcelHeaderColumn += "'" + dt.Columns[i].ColumnName + "'";                         }                       SQLQueryToGetMatchingColumn = "select STUFF((Select  ',['+Column_Name+']' from Information_schema.Columns where Table_Name='" +                             TableName + "' and Table_SChema='" + SchemaName + "'" +                             "and Column_Name in (" + @ExcelHeaderColumn + ") for xml path('')),1,1,'') AS ColumnList";                         //USE ADO.NET Connection                         SqlConnection myADONETConnection = new SqlConnection();                         myADONETConnection = (SqlConnection)(Dts.Connections["Ado_Dot_net_Conn"].AcquireConnection(Dts.Transaction) as SqlConnection);                          //Get Matching Column List from SQL Server                         string SQLColumnList = "";                         SqlCommand cmd = myADONETConnection.CreateCommand();                         cmd.CommandText = SQLQueryToGetMatchingColumn;                         SQLColumnList = (string)cmd.ExecuteScalar();                          //Use Actual Matching Columns to get data from Excel Sheet                         OleDbConnection cnn1 = new OleDbConnection(ConStr);                         cnn1.Open();                         OleDbCommand oconn1 = new OleDbCommand("select " + SQLColumnList + " from [" + sheetname + "]", cnn1);                         OleDbDataAdapter adp1 = new OleDbDataAdapter(oconn1);                         DataTable dt1 = new DataTable();                         adp1.Fill(dt1);                         cnn1.Close();                          //Load Data from DataTable to SQL Server Table.                         using (SqlBulkCopy BC = new SqlBulkCopy(myADONETConnection))                         {                             BC.DestinationTableName = SchemaName + "." + TableName;                             foreach (var column in dt1.Columns)                                 BC.ColumnMappings.Add(column.ToString(), column.ToString());                             BC.WriteToServer(dt1);                         }                      }                 }             }             Dts.TaskResult = (int)ScriptResults.Success;            }              catch (Exception ex)             {                 // Create Log File for Errors                 using (StreamWriter sw = File.CreateText(Dts.Variables["User::Source_Dir"].Value.ToString() + "\\" + "ErrorLog.log"))                 {                     sw.WriteLine(ex.Message.ToString());                     Dts.TaskResult = (int)ScriptResults.Failure;                 }            }   }

Now we are ready to run this task.

Records in the table.

 

Running this task. 

Package executed successfully.

See the records in the table.

   

File loaded successfully. J