Get data out a SQL Server database over the web
Hi I want to use SSIS to extract data out of a third party SQL Server database over the internet. What would be the best way to do this using SSIS, providing I have the relevant security credentials. Thank you
November 3rd, 2012 6:47am

Hi, I would prefer to define web service on the server and by SSIS, you can call that webservice and download the information and can use that information wherever its required. We can also add security, authentication/authorization, while accessing web service via internet. Thanks, Tejas SQLYoga.comTejas Shah SQL YOGA
Free Windows Admin Tool Kit Click here and download it now
November 3rd, 2012 7:18am

If that server accepts remote connections and you know the IP address, you could connect directly with an OLE DB or ADO.NET connection. It would be a bit odd though if they would open up their SQL Server to the entire net.MCTS, MCITP - Please mark posts as answered where appropriate.
November 3rd, 2012 7:37am

Hi, I would prefer to define web service on the server and by SSIS, you can call that webservice and download the information and can use that information wherever its required. We can also add security, authentication/authorization, while accessing web service via internet. Thanks, Tejas SQLYoga.com Tejas Shah SQL YOGA Hi This sounds like the best approach. I need some help to what are the steps in making this happen as my knowledge in this area is not great. I am guessing I can ask the third party developers to define a webservice. After this I can use the an OLE DB or ADO.NET connection manager to access the data like I would from any other database but by specifying the IP as the datasource? If you can help give me a few more details to achieving this, it would be much appreciated. Thanks
Free Windows Admin Tool Kit Click here and download it now
November 3rd, 2012 8:07am

Hi, Please find following steps: Web service needs to be hosted on the server, which can get the info from the database as per your requirementsThis web service created in #1, needs to be hosted on the server, so anyone (authorized) can access it from internet/intranet.In SSIS, we have "Web service task" where we can call that web service we have implemented on #2you can write that info to CSV/XML file on the disk, if needed and then do data flow task based on that Please let me know your comments. Thanks, Tejas SQLYoga.com Tejas Shah SQL YOGA
November 3rd, 2012 8:47am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics