I have recently been doing some work using SSIS, and this is really the first time I've used for anything serious (ie beyond doing the tutorials that come with SQL Server 2005). I have also recently been learning WCF and as we needed to create some Web Services to deliver the information to SSIS, I decided that this would be the perfect opportunity to put my newly acquired WCF skills to good use. Now I always like to start things out very simply, which in this case meant prototyping the SSIS Web Service Task with the most basic of WCF Web Services I could possibly create, thinking once I get this working, I can then move on to the actual task that I want to achieve with confidence... unfortunately this is where my whole plan started to unravel. For those following at home, here are the steps I took.
1. Create a new WCF Web Service in Visual Studio 2005 by selecting File->New->Web Site, then choosing a WCF Web Service. (Note .Net 3.0 and related Visual Studio extensions are required for this. Also choosing a File based or IIS based web service shouldn't matter).
2. Open the web.config file change the binding to basicHttpBinding (although the same end result is still achieved with the default wsHttpBinding).
3. Expose the metadata over httpget by adding the following line to the service behavior in the config fil
<serviceMetadata httpGetEnabled="true" />
4. run the service.
5. Create a new SSIS project in Visual Studio by selecting File->New->Project, then choosing Business Intelligence->Integration Services from the menu.
6. Add an http connection that points to the wsdl file for your service, and a Web Service Task to the Control Flow that uses the http connection.
7. Edit the Web Service Task, and download the wsdl exposed by your service, and save it locally to a file by using the OverwriteWSDLFile = true on the General page.
8. Select Input, and then select 'MyService' from the drop down.
At this point I recieved an error message.
Item has already been added. Key in Dictionary: ‘DataContract1’ key being added : ‘DataContract1’
It then would NOT populate the Method DropDown, so I was unable to select a Service Method to call, and because you are confined to selecting from the drop down, there is no possible way to make any of the Web Service method calls.
Now I'm the superstitious kind when it comes to writing software, my firm belief is that if you can't get the most basics Web Service working, then you may as well not try to get your complex web service that wants to pass around meaningful data, working, you'll end up in tears.
A google search on the problem yielded the following forum post http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1023821&SiteID=17 which was helpful in revealing the underlying problem. The problem is with the generated wsdl and corresponding schemas, not sure exactly what is wrong with them, but for some reason, the UI in Visual Studio doesn't like the way WCF publishes its metadata, in particular the way it published metadata about its DataContracts (complex types). So I thought I'd make the default WebService that WCF generates even simpler by removing any trace of the DataContract. This worked fine, I could call a WCF WebService from within SSIS no problems... with the restriction that I could only pass primitive types to/from the web service. now this might be fine for your purposes, but I required something a little more powerful, so I decided to persist with figuring out the solution.
I tried a number of things with the generated schemas, but I think in the end I'd exhausted my limited understanding of wsdl and xsd, and was no further ahead. Next I remembered a very simple statement by a fellow collegue of mine Andrew Ball who was at the time giving me some SSIS advice. He said "... under the hood an SSIS package (dtsx file) is just a big xml file". Armed with this I began to see the issue as more of a tooling issue, and thought that maybe the issue is purely with the designer. So I cracked open a sample that Andrew had sent me and found where the various Web Service properties were being set. I also cracked open good old reflector and took a look at the way in which Complex Types were handled in the Microsoft.SqlServer.WebServiceTask.dll Which is responsible for the Web Service Task in SSIS. about half an hour later (give or take 10 mins) I had the following XML
<WSTask:MethodInfo WSTask:MethodName="MyOperation2" WSTask:MessageName="">
<WSTask:Documentation>
Basic Hello world service call that uses a DataContract
</WSTask:Documentation>
<WSTask:ParamInfo WSTask:Name="dataContractValue" WSTask:Datatype="DataContract1" WSTask:ParamType="Complex" WSTask:SeqNumber="0">
<WSTask:ComplexValue>
<WSTask:ComplexProperty WSTask:Name="FirstName" WSTask:Datatype="string" WSTask:ParamType="Primitive">
<WSTask:PrimitiveValue>Scott</WSTask:PrimitiveValue>
</WSTask:ComplexProperty WSTask:Name>
<WSTask:ComplexProperty WSTask:Name="LastName" WSTask:Datatype="string" WSTask:ParamType="Primitive">
<WSTask:PrimitiveValue>Baldwin</WSTask:PrimitiveValue>
</WSTask:ComplexProperty WSTask:Name>
</WSTask:ComplexValue>
</WSTask:ParamInfo>
Which when placed inside a web service task element, can be used to successfully call the web service with the DataContract.
So my conclusion is that there is a bug in the SSIS Web Service task UI that doesn't handle populatinf the Method drop down correctly (I'd trust WCF before I'd trust SSIS), but this is merely a bug with the UI, not with the way in which the Web Service task actually goes about calling the web service.
Added after post (literally minutes after): For the easy way to solve this problem see http://sjbdeveloper.blogspot.com/2007/03/fixing-wcf-ssis-web-service-task.html
Posts
