December 4, 2009 at 5:42 am
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
December 4, 2009 at 8:30 am
[font="Comic Sans MS"]
Sowmya
Sorry I couldn't understand the requirement. Please can you elaborate with an example? Like PackageA (localhost) -- PackageB (remote machine running thru sql agent) -- Excel Path Name (variable - derived by PackageA) .. etc ?
[/font]
[font="Comic Sans MS"]--
Sabya[/font]
December 4, 2009 at 10:03 am
Probably the easiest would be to configure your pacakge to use configurations and configure it to pull the filename from a SQL table. That way you could change the value and then call the package.
CEWII
December 8, 2009 at 12:27 am
Hi,
Thanx for the reply:-).
In my case, the package is executed remotely and so excel file path would be the path from the machine which is not the same where the package is executed. Could you please let me know using the package configurations, how to pass such the remote file locations? Or if it is possible this way?
-Sowmya
December 8, 2009 at 12:33 am
Hi Sabya,
I have a Package A on a server (S1) to be executed. This package is executed using the Sql Server Agent remotely. Now, the user initiates the execution of the Package A from a local machine L1 where the excel file is stored. So, my requirement is to pass this file name and path from the machine L1 to the package A which is executed on machine S1. Hope this clarifies the requirement. Any suggestions:-).
-Sowmya
December 8, 2009 at 12:37 am
If I understand correctly, you want the user to fire off a package which reads an Excel file from their PC?
Assuming that SSIS is installed on a network server and that the user is connected to the network, the method suggested by Elliot seems like it should work. The locally deployed application would have to write the details of the UNC path to the Excel file into a SQL Server table and then activate the package.
This, of course, assumes that privileges are in place which permit this level of access from the SQL Agent job to the user's PC. If it is a multi-user system, you may need additional sophistication in the write-file, read-file logic to avoid conflicts.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 8, 2009 at 7:31 am
[font="Comic Sans MS"]
Sowmya - as Phil said - this is very much possible. Looks like your pathname would be same for a package. Your package needs to read the path from a configuration (be it a configuration file or table).
You can define a variable and assign the path to it. Subsequently you can use the variable to any container or connection manager as appropriate.
If you need any help on how to use different type of configurations - check these out
http://www.sqlis.com/post/Easy-Package-Configuration.aspx
http://www.nigelrivett.net/DTS/SSISConfigurationFile.html
In your case the path would be a UNC path like \\remotehost\somepath\..
[/font]
[font="Comic Sans MS"]--
Sabya[/font]
December 8, 2009 at 10:20 pm
Thanx Phil and Sabya for your inputs:-).
On the similar lines what I have done is, created the Package with the excel source and provided a path where the excel could be found. And in the machine which invokes the package, I have added code which copies the file from the local machine to the server where my package is going to be executed, at the desired location using File.Copy(localpath, "\\remoteserver\sharedfoldername"). Could you please suggest if this is a recommended approach, as such without using any package configurations?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply