How to pass file path to a SSIS package executed remotely?

Question

Hi,

I am trying to create a SSIS package to load data from an excel file to a database table. The excel to import the data will not be defined at the time of creation of the package. It is available, in a desired format, only when the user specifies and locates the file on the local file system at runtime. Could anyone please let me know if it is possible to pass the file path of the excel at the time of execution of the package instead of hard-coding the path of the file in the excel connection manager? As such, I want to remotely execute the package in the sql server using the Sql Server Agent. So, when the path of the excel file is specified, it would be done on a local machine where the application is deployed and I want this path to be passed to the package which will be executed in the server machine, remotely. Any suggestions on how to cater to this scenario would be of great help to us.

Thanx in advance.

Sowmya

Answer 1

Look at using package configurations: Package Configurations. You can dynamically change your excel file path at run time on a value that is stored in your database.

Answer 2

Hi,

Thanx for the reply.

As mentioned in the post, excel file path would be the path from the machine which is not the same where the package is supposed to be executed. The package is executed remotely. Could you please let me know using the package configurations, how to pass such the remote file locations?

-Sowmya