1

How to get the Excel file last saved user's information using C# code in SSIS to insert into a SQL table

I need to add the information about excel file to a SQL table.

I have already included other parameters like Excel file's location, file name, file size, last access time, modified time and all.

Wanted to include the user's name who had last saved that Excel file, using C# code in script task of SSIS

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tejaswini
  • 35
  • 2

2 Answers2

1

You can add the current username in a specific cell and you need to you need to use a macro to access the information and then make it available to your worksheet.

You can use of a user-defined function like below :

Function GetCurrentUserName()
    GetUserName = Application.UserName
End Function

And in the worksheet in the specified cell, you add :

=GetCurrentUserName()

Then in your Script Task, you can read the value of that cell :

String filePath = "put_the_name_of_the_path_of_the_file_here";
String yourSheet = "put_the_name_of_the_sheet_here";

string readUserName = "SELECT * FROM [" + yourSheet + "F4:H4]";

The F4:H4 is the location of your cell.

Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
1

If you can use the Excel Interop Assembly, the following should work.

var workbook = new Application().Workbooks.Open(@"yourFile.xlsx", ReadOnly: true);
dynamic properties = workbook.BuiltinDocumentProperties;
var lastSavedUser = properties["Last Author"];
Kevin
  • 2,566
  • 1
  • 11
  • 12