Skip to content
Chris Koester

Data Analytics Consultant in Indianapolis, Indiana

Primary Navigation Menu
Menu
  • Home
  • About
  • Contact
  • Privacy Policy

Data Integration

Retrieve JSON Data from SQL Server using a Stored Procedure and C#

Retrieve JSON Data from SQL Server using a Stored Procedure and C#

2018-03-25
By: Chris Koester
On: 2018-03-25
In: Data Integration
With: 4 Comments

The previous post demonstrated how to load JSON into SQL Server using a stored procedure and C#. This post will examine the opposite – how to retrieve JSON data from SQL Server using a stored procedure and C#. Just like with the JSON loading technique, the new JSON functions in SQL Server 2016 allow for this retrieval technique. Here’s how it works in short: Data stored in a database table will be converted to JSON text in the stored procedure and returned to the C# client via an output parameter. SQL Server Table Let’s start with the SQL Server table, which will contain one rowRead More →

Load JSON Into SQL Server Using a Stored Procedure and C#

Load JSON into SQL Server Using a Stored Procedure and C#

2018-03-21
By: Chris Koester
On: 2018-03-21
In: Data Integration
With: 1 Comment

This post will demonstrate how to load JSON into SQL Server using a stored procedure and C#. This technique is possible with the new JSON functions starting in SQL Server 2016, and is nice for the following reasons: Simplifies application code. Just serialize the C# objects to JSON and send them to the database. Executing a stored procedure with C# is easily done with a small amount of code. JSON is widely used and familiar to developers. Structural changes to the data could potentially be done entirely on the database side, meaning that you could make changes without having to touch the application code. SQLRead More →

Download JSON Data with SSIS

Download JSON Data with SSIS

2017-06-06
By: Chris Koester
On: 2017-06-06
In: Data Integration
With: 12 Comments

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 →

Call an Azure Function from Power BI

Call an Azure Function from Power BI

2017-03-28
By: Chris Koester
On: 2017-03-28
In: Data Integration, Power BI, Power Query
With: 2 Comments

This post shows how to call an Azure Function from Power BI. This scenario offers some interesting possibilities, because it allows for integrating the power of several common programming languages with Power BI and Power Query. If you’re not familiar with Azure Functions, it’s a service that allows you to run small pieces of code without having to deal with a server. In this example, we’ll write a query in Power BI that submits a time zone to an Azure Function. The function simply returns the current time in the specified time zone. While this is a very basic example, it is important because itRead More →

Read the Top N Rows from Large Text Files with C#

Read the Top N Rows from Large Text Files with C#

2017-03-17
By: Chris Koester
On: 2017-03-17
In: Data Integration
With: 0 Comments

This post describes one way that you can read the top N rows from large text files with C#. This is very useful when working with giant files that are too big to open, but you need to view a portion of them to determine the schema, data types, etc. I’ve used PowerShell many times to do this with large csv files, but in this example we’re going to use C# and look at the Wikipedia XML dump of pages and articles. The 3017-03-01 dump is very large and comes in at 59.5 GB. The script is short and simple. All it does is readRead More →

Combine CSV Files without Duplicating Headings using C#

Combine CSV Files without Duplicating Headings using C#

2017-01-27
By: Chris Koester
On: 2017-01-27
In: Data Integration
With: 5 Comments

The simple script below shows how to combine csv files without duplicating headings using C#. This technique assumes that all of the files have the same structure, and that all of them contain a header row. The timings in this post came from combining 8 csv files with 13 columns and a combined total of 9.2 million rows. I first tried combining the files with the PowerShell technique described here. It was painfully slow and took an hour and a half! This is likely because it is deserializing and then serializing every bit of data in the files, which adds a lot of unnecessary overhead. NextRead More →

Download JSON With PowerShell - Allegient Blog Post

Download JSON Data with PowerShell

2016-12-07
By: Chris Koester
On: 2016-12-07
In: Data Integration, PowerShell
With: 0 Comments

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 →

Twitter status update from Power Query

Post to Twitter from Power Query

2016-07-15
By: Chris Koester
On: 2016-07-15
In: Data Integration, Power Query
With: 2 Comments

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 →

Query XML on the Web with C# and SSIS

Query XML on the Web with C# and SSIS

2016-05-05
By: Chris Koester
On: 2016-05-05
In: Data Integration
With: 0 Comments

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 →

Generate a Random Alphanumeric String in Power Query

Generate a Random Alphanumeric String in Power Query

2016-04-30
By: Chris Koester
On: 2016-04-30
In: Data Integration, Power Query
With: 6 Comments

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
/*
Generates a single, random alphanumeric string. The string length can be modified by changing the StringLength variable.
Author - Chris Koester
https://chris.koester.io/
*/
 
let
    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)],
                   each [Character]),
    RandomString = List.Accumulate(GenerateList, "", (a,b) => a & b)
in
    RandomString

The result of this query is a value like “kZSRd55cC67QyFWbjTXKlhnwiCttaZxU”. Every time the query is run,Read More →

Posts navigation

1 2 Next

Follow Me

  • Twitter

Topics

Analysis Services API Azure Azure Blob Storage Azure Functions big data C# code CSV Data integration DAX duplicates Excel HDInsight Hive JSON M MDX OAuth OPENJSON Parameters Power BI Power Map Power Pivot Power Query PowerShell REGEX Reporting Services REST SQL Server SSAS SSAS Tabular SSIS SSRS Stored Procedure Streaming Temboo text TMSL TOM TPC Twitter usgs VB xml

Certifications

MCSA: SQL 2016 Business Intelligence Development

Recent Posts

  • Generate Big Datasets with Hive in HDInsight
  • Delete SSAS Tabular Partitions with C#
  • Retrieve JSON Data from SQL Server using a Stored Procedure and C#
  • Load JSON into SQL Server Using a Stored Procedure and C#
  • Push Performance Counter Data into a Power BI Streaming Dataset

Categories

  • Big Data
  • Data Integration
  • Power BI
  • Power Query
  • PowerShell
  • Reporting Services
  • SSAS Tabular

Archives

  • March 2019
  • May 2018
  • March 2018
  • December 2017
  • November 2017
  • October 2017
  • August 2017
  • June 2017
  • March 2017
  • January 2017
  • December 2016
  • July 2016
  • May 2016
  • April 2016
  • December 2015
  • July 2015
  • February 2015
  • August 2014

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Privacy Policy Designed using Responsive Brix. Powered by WordPress.