In a post on the Allegient blog, I showed how you can download JSON data with PowerShell. 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. Define Output Columns Once the Script Component has been defined asRead More →
I recently authored a post on the Allegient blog titled “Download JSON Data with PowerShell“. In this post I show how you can download JSON in its entirety or download it and write desired values to CSV.Read More →
You might be surprised to learn that you can post to twitter from Power Query. After all, the primary use for Power Query is obtaining and transforming data. While this is true, Power Query also offers the ability to perform an HTTP POST, which means that we can submit data via the web. My first blog post titled “Get Data from Twitter API with Power Query” involved an HTTP POST in order to obtain a token from the Twitter API. In this post I’ll show how you can Tweet from Power Query with some help from Temboo. So what is Temboo? Temboo is a service that allows usRead 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 →