1

I created a SSIS Custom Log Provider (SQL Server 2012 / Visual Studio 2010 / .Net Framerowk 4), and now I have to call a WCF service. I am not sure how to do that, since this is a custom library project, with no config file.

Ideas? Thanks!

Roberto
  • 645
  • 1
  • 8
  • 16

1 Answers1

2

This is a tricky thing to do in SSIS. You would be better off calling the WCF Service from C# and using that app to populate and thus stage a table that you can get to from SSIS.

Having said this...if you want to access a WCF Service here are the basic steps.

  1. WCF Endpoint Config Settings. The endpoint to your WCF Serivce must be placed in the dtsexec.exe.config AND the DtsDebugHost.exe.config file located in the following folder: C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn it would be wise to do the same in the Program Files folder if you are running 64 bit SQL Server as well.

  2. Now you can create your SSISScript and add the Service Reference by right clicking on References and then Add Service.

  3. If you have complied your own "internal" Proxies and contracts into DLL then you will need to copy those to the same location as the dtsexec.exe above AS WELL AS registering them in the GAC.

  4. Remember...you need to do this where the SSIS package will run so typically your local instance of SSIS and the Production SQL Server.

  5. Now...an alternative to this may be to "Brute Force" code all of the WCF Endpoint settings in code in your SSIS Script. I have not tired this but if it works that would eliminate the need for the endpoint in those obscure locations and config files. It would be cleaner and a better route. You could then store your WCF endpoints and other settings as variables the pass them into the SSIS Script as readonly values.

Good Luck!

Qui_Jon
  • 163
  • 1
  • 10
  • Thanks for the great feedback. Does what you just said applies to Custom Log Provider as well? In your answer you mention SSIS Script and Package, but I am actually creating a DLL as a new Custom Log Provider, that will be registered in the GAC. Thanks! – Roberto Nov 01 '12 at 00:35
  • 1
    So is the log provider a DLL that needs to access a WCF endpoint? Sounds like you have access to the source of the code in the DLL. I would recommend extending that code in the DLL to take the endpoint and other WCF Config file "like" settings and make them part of the input variables of your custom object. Now you can "programatically" define your endpoint and settings directly in code in the DLL and its somewhat "dynamic". Check this out http://stackoverflow.com/questions/54579/wcf-configuration-without-a-config-file... – Qui_Jon Nov 01 '12 at 14:56
  • Yeah. I have that in code right now, and I was testing what config file I could put the binding and endpoint configuration. That's because it has to be configurable, since it will be different environment by environment. Do you know the config file I could use by any chance? Thanks! – Roberto Nov 01 '12 at 18:15