Part of my project is to pull configuration data from a web service provided by my client and save it locally for our app to use. The client wrote a very simple .ASMX, which returns a DataSet.
My first thought was to use SSIS to extract the data from the web service, and then do a dimension load into my local tables. But I hit a problem: When the SSIS control task saved the DataSet, it saved it as a diffgram:
The result of this is that when using the SSIS Data Flow task for “pull me some data from an XML File”, while it does get the schema correct, it gets 0 rows, every time.
There is a workaround to use XSLT to transform this XML so that it works correctly: Examples here and here; but I discovered this a bit late in the game. I ended up writing my own code to retrieve the data from the web service, and do a simple dimension load (insert, update, delete) against my local table. The code that calls it looks like this:
And the generic part of the code is this:
This code is using Dapper to take care of the parameter-to-SQL bits. It took about an hour or two to get the first one working just right.. and then another 1.5 hours for the remaining 6 dimension tables.
Its still not as fast as doing the work directly in SSIS, however, with SSIS I then have connection string management and other such fussy things to deal with, which could have taken just as long.
One nice fun thing I was able to do is take the saved XML from the web service (thank you SSIS), pull it local, embed it as a resource, and write a Stub version of the web service.
Good Times.
