I'm trying to import Excel sheet data into the DB via SSIS script task, I'm continuously getting the above error message.
The Excel file has 3 sheet, and we have separate table for each sheet of that Excel file in the database.
Also I'm not getting any error in the region Namespaces.
Can anyone help, what I'm missing here?
public void Main()
{
try
{
string filePath;
filePath = Dts.Variables["FilePath1"].Value.ToString();
ReadXLS(filePath);
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
Dts.TaskResult = (int)ScriptResults.Success;
}
}
public void ReadXLS(string FilePath)
{
FileInfo existingFile = new FileInfo(FilePath);
using (ExcelPackage package = new ExcelPackage(existingFile))
{
string eachVal = "";
int wsheet = package.Workbook.Worksheets.Count;
for (int sheet = 0; sheet<wsheet; sheet++)
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[sheet];
string SheetNames = worksheet.Names.ToString();
int rowCount = worksheet.Dimension.End.Row;
int colCount = worksheet.Dimension.End.Column;
for (int row = 2; row <= rowCount; row++)
{
string queryString = "Insert into [Billing_]" + SheetNames + "values(";
queryString += "(";
for (int col = 1; col <= colCount; col++)
{
eachVal = worksheet.Cells[row, col].Value?.ToString().Trim();
queryString += "'" + eachVal + "',";
}
queryString = queryString.Remove(queryString.Length - 1, 1);
queryString += ")";
runQuery(queryString);
}
}
private void runQuery(string QueryString)
{
string connectionString = @"Data Source = ./; Initial Catalog = Testing; Integrated Security = SSPI;";
string commandText = QueryString;
using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(commandText, conn))
{
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
User contributions licensed under CC BY-SA 3.0