Extracting Data Client Side

Question

I need to be able to extract and transform data from a data source on a client machine and ship it off via a web service call to be loaded into our data store. I would love to be able leverage SSIS but the Sql Server licensing agreement is preventing me from installing Integration Services on a client machine. Can I just provide the client copies of the Integration Services' assemblies to be referenced by my app? Does anyone have any ideas on how to best implement a solution to this problem apart from building a custom solution from the ground up? Ideally the solution would include leveraging an existing ETL tool?

Thanks for your suggestions.

Answer 1

If the server can reach out to the client machine, then you can just run the SSIS package on the server. What kind of data are you moving? If it's a flat file, you could FTP it to the server.

Another way to go about this is to use BCP. I'm not a big fan of this approach (SSIS is much faster, more robust, etc), but it can work in a pinch.

http://msdn.microsoft.com/en-us/library/ms162802.aspx

Answer 2

If you are providing your client with a service around their data, you should develop a standard that they need to deliver their data in, and negotiate a delivery method for that file well before you ever consider what to do with SSIS. Since from comments it appears that your data is on a machine in a client's remote location, the most common method I have seen is either having the client SecureFTP a file into your network for processing, or to have a job on your end that gets the file using SecureFTP. Once you have the file on your network, writing the SSIS to process it is trivial.