I have a SSIS package with script task. c# script use ACE Oledb 12.0 provider to connect to excel file. The question is, how to connect to excel file in read-only mode (if someone open the file, my script should not have an error - it should work). The code, I tried here:
string fileToTest = Dts.Variables["User::FileName"].Value.ToString();
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + fileToTest + @";Extended Properties=""Excel 8.0;READONLY=1""";
OleDbConnection excelConnection = new OleDbConnection(connectionString);
excelConnection.Open();
string sqlQuery = "SELECT * FROM [SheetName$A1:FZ1000]";
OleDbDataAdapter dataAdt = new OleDbDataAdapter(sqlQuery, excelConnection);
DataSet dataSt = new DataSet();
dataAdt.Fill(dataSt, "TblName1");
DataTable dataTbl = dataSt.Tables["TblName1"];
I receive oledbexception, if someone open the file.