I have a requirement wherein I will be executing an SSIS package from a command button on an MS Access front end. The requirement is to present a file picker window which will let user choose file to be uploaded and then once chosen it should be inserted into table in SQL Server.
Kindly let me know if this option can be made available in the package.
No, you cannot make this option available in an SSIS package.
The way I have handled this in the past is to write a .net application that uses the file picker to let the user choose the file, and then stores the user's filename/path in a table, and launches a job that calls the SSIS package, which gets the filename/path from the table, and then uploads that file.
I assume you can do the same with a MS Access front end, but I am not an Access expert.