SSIS | Load Excel file with missed or misarranged columns | (SQL Server Integration Services)

In this video, we will learn how to load data into a sql server table from an excel file that has missed columns or misarranged columns.

Script:-
// TODO: Add your code here
String folderPath = Dts.Variables["User::FolderPath"].Value.ToString();
String tableName = Dts.Variables["User::TableName"].Value.ToString();
var directory = new DirectoryInfo(folderPath);
FileInfo[] files = directory.GetFiles();

string fileFullPath = "";

foreach (FileInfo file in files)
{
fileFullPath = folderPath + file.Name;

string HDR = "YES";
string conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileFullPath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
OleDbConnection con = new OleDbConnection(conStr);

con.Open();
DataTable dtSheet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);

string sheetName = "";
foreach (DataRow drSheet in dtSheet.Rows)
{
if (drSheet["TABLE_NAME"].ToString().Contains("$"))
{
sheetName = drSheet["TABLE_NAME"].ToString();
}
}

OleDbCommand oleDbCommand = new OleDbCommand("SELECT * FROM [" + sheetName + "]",con);
OleDbDataAdapter oda = new OleDbDataAdapter(oleDbCommand);
DataTable dt = new DataTable();
oda.Fill(dt);
con.Close();

SqlConnection sqlCon = new SqlConnection();
sqlCon = (SqlConnection)(Dts.Connections["LocalDbConnection"].AcquireConnection(Dts.Transaction) as SqlConnection);
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlCon))
{
bulkCopy.DestinationTableName = tableName;
foreach (var column in dt.Columns)
{
bulkCopy.ColumnMappings.Add(column.ToString(), column.ToString());
}
bulkCopy.WriteToServer(dt);
}
}
You can find me on
1-https://www.linkedin.com/in/satyarth-rao-5baba016a
2-https://www.instagram.com/satyarthrao/
3-https://twitter.com/RaoSatyarth
4-https://www.facebook.com/SatyarthProgrammingHub
5-https://github.com/Satyarth-Programming-Hub

Видео SSIS | Load Excel file with missed or misarranged columns | (SQL Server Integration Services) канала Satyarth Programming Hub