In a post on the Allegient blog, I showed how you can download JSON data with PowerShell (Link no longer available due to company purchase and reorganization). Now we’ll take a look at how to download JSON data with SSIS. As a dedicated data integration tool, SSIS has many advantages over PowerShell for this type of work. SSIS does not have a built in source component for JSON data, so you’ll have to use the Script Component. When you drag the Script Component into a data flow, you’ll be asked how you want to use it. In this case you’ll use it as a source. DefineRead More →
This post describes how to query XML on the web with C# and SSIS. The emphasis is on the C# code, as I assume the reader is somewhat familiar with the SSIS Script Task. And in order to allow anyone to easily try this example, there is no authentication step. If you’re retrieving XML values from an API, there’s a good chance that you will need to authenticate first. I recently worked on a data integration project where I had to perform two steps involving a REST API in order to download web analytics data. In order to download data in bulk from this API, you firstRead More →
Update 2016/06/01: Bill Szysz has shared a solution in the comments below that is shorter and easier to understand than mine. He’s also shared a couple of other alternatives that seek to improve the randomness of the results. This post describes how to generate a random alphanumeric string in Power Query. This is likely not a common requirement for most Power Query users, but I saw this requirement in the Twitter API and thought it would be a fun challenge in M. Here’s the complete query. Later I’ll explain how it works.
Generates a single, random alphanumeric string. The string length can be modified by changing the StringLength variable.
Author - Chris Koester
StringLength = 32,
ValidCharacters = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456879",
fnRandomCharacter = (text) => Text.Range(ValidCharacters,Int32.From(Number.RandomBetween(0, Text.Length(ValidCharacters)-1)),1),
GenerateList = List.Generate(()=> [Counter=0, Character=fnRandomCharacter(ValidCharacters)],
each [Counter] < StringLength,
each [Counter=[Counter]+1, Character=fnRandomCharacter(ValidCharacters)],
RandomString = List.Accumulate(GenerateList, "", (a,b) => a & b)
The result of this query is a value like “kZSRd55cC67QyFWbjTXKlhnwiCttaZxU”. Every time the query is run,Read More →
Use Microsoft’s Power Query tool to retrieve data from the Twitter APIRead More →