September 2, 2008 at 1:26 pm
Hi,
I am trying to create a tool in Excel(VBA) which exports data(in excel & txt files) to SQL Server tables. If the users do not have SQL Server installed on their computers, is there any way they can run an SSIS package?(through the excel tool or otherwise). Appreciate any help.
September 2, 2008 at 3:08 pm
I don't think we can run an ssis package with out the installation of sql server and ssis on local or remote...
September 2, 2008 at 3:48 pm
The user's laptops don't have sql server installed but they do have access to a remote sql server. In this case how do I get the user to run an SSIS package from his laptop without needing to install SQL Server on the local machine.
September 2, 2008 at 4:39 pm
okay... In that case the users need to connect to the remote server using remote desktop connection (they should be on the same network). then they can run the ssis packages in the sql server...if u want to develop the ssis packages then make sure that Sql Server Integration Services is installed on the remote server..
September 2, 2008 at 5:03 pm
ok. Thanks for your help.
September 3, 2008 at 5:29 am
To run an SSIS package, the machine needs to be an SSIS Server. Which also means you would need to buy a SQL Server license for every laptop. This is pretty impractical.
I would suggest you write a small web application that can be hosted on your SSIS Server. It is pretty easy to create an asp.net web page that the users could go to, upload the excel spreadsheet, and then the server would be able to use it for the SSIS package.
September 4, 2008 at 1:04 am
Using SSIS could be overkill for small amounts of data at a time.
You can also, using VBA, connect to the SQL Server database and load the data from the Excel spreadsheet or text files into the table, preferably by calling a stored procedure on the database for each row. Assuming you wouldn't have a large number of rows, this might be a better solution than creating a web page to upload the files and a package to import the data.
September 4, 2008 at 7:52 am
If performance and locking are concerns, another option is to simply export csv or text files to a network share from excel (using a machine and date/time stamp on the file) and create an SSIS package that runs for continuously (in a loop until a specific situation exists) or that is called periodically that imports any files found in this folder.
Just a thought...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply