Day at the Office: SSIS, DiffGrams, XML, Dimension Loading using Dapper

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. 

imageMy 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.

Tagged with: , , ,
Posted in Code

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

.net 3d 3d-printing 4k abc15 algorithms ames android anonymous types audio editing aws backup basecamp beatunes biorhythm bittorrent blender blog boston marathon bpm c# caffeine campfire candycrush car carmax certification charity chiropractor cities-skylines clog clone codelouisville codepalousa coding coffee collaboration color run ComputerElbow ComputerVision configuration consulting cooking crash course crashplan crestwood cryptocurrency cycling dabda dan dapper DataSet ddl diabetes dictation dotnetcore dotnetmud downtown e-cycling elite excel exercise expiration facebook fashion feature-branching firefall flipflops Flow FL Studio focus food forecastle fortresscraft franklinplanner gadgets game-design games git github google docs google maps gopro gps grandpa greenshot hack half marathon headless health heart rate hiren ignew integration testing interop inventory ios ipad itunes javascript jobs karma kdf keyboards keys kittens lamont laptop lavalamp leaf lego life lifehack linq linqtotwitter linux los angeles louisville mandelbulber massage therapy mastery-teaching maths merge metformin Minecraft miniature modeling monitor mud muhammad ali institute music mvc mycartracks netfabb nexus10 node nomanssky nostalgia nutrition nwipe oldham county grand slam opal openjscad openscad owin pacedj paper mockup pepakura performance photoscan politics pomodoro postgresql powershell premiere priorities prius process product-management project-management qa ReFS resharper review rmi roman road 5k RSI rubiks running samsung 700t sandals schedule scooter scribblelive selenium service shapeways sleep slic3r sneakersync snot software software-engineering solidoodle soylent spacegame speaking sql sqlite SSDT SSIS standing state-machine stayfocusd stonehearth studying sunset tablet teaching team teamcity teamtreehouse terraform tesla testing tfs time timelapse torque touch tracks trs80 Tuple tutor twitter ubuntu unit testing utilities video video editing visual studio vscode vsvim warp stabilizer windows 8 windows home server wordpress wpf xml zozo
%d bloggers like this: