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

By:
On:
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#

By:
On:
In: Data Integration
With: 3 Comments

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

By:
On:
In: Data Integration
With: 15 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

By:
On:
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#

By:
On:
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#

By:
On:
In: Data Integration
With: 7 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 →

Query XML on the Web with C# and SSIS

By:
On:
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

By:
On:
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.

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

JSON Array in Power Query Featured Image

By:
On:
In: Data Integration, Power Query
With: 0 Comments

Update 2016-04-27:  Imke Feldmann sent me an M query that accomplishes the same goal of this post with less than half the code! The query is here. Imke’s blog is a great resource and you can also find her on Twitter. Thank you, Imke! Basic JSON structures can be parsed pretty easily by Power Query, as they often represent tabular structures that are familiar to those that work with data. Power Query simply converts the JSON to a table and you’re good to go. Working with a JSON array in Power Query, however, can be difficult and may result in duplicate rows in your dataset. JSON is builtRead More →

Select columns from multiple CSV files

By:
On:
In: Data Integration, PowerShell
With: 0 Comments

I recently encountered a scenario where I needed to use PowerShell to select columns from CSV files and output the results to a new set of files. This was necessary because an additional column was accidentally introduced to CSV files that were being loaded hourly with SSIS. When the additional column appeared, SSIS began to choke on the files, resulting in package failures. The hourly process actually continued working, but a couple dozen CSV source files needed to be modified before they could be loaded into the data warehouse. The diagram above shows the required modification. PowerShell to the Rescue PowerShell is my favorite Business Intelligence tool that isRead More →