IngeiousSQL

JSON to DataSet SQL CLR

I have been working with SQL CLR functions and procedure recently, and have come up with a crazy solution involving JSON, Analysis Services, and Visual Studio Database projects. Before we get into the crazy solution, we are going to take another step into SQL CLR Procedures which will help with understanding the pain points with using an external library. In a previous postwe looked at…

Simple SQL CLR Objects

I have been working with SQL CLR functions and procedures recently, and have come up with a crazy solution involving JSON, Analysis Services, and Visual Studio Database Projects. However, Before we get into the crazy solutions, we are going to take a look at a simple SQL CLR implementation. This simple implementation will include procedures and functions that return formatted dates. Using a…

Comma Separated Values using the FOR XML PATH clause

In a previous post we looked at how we can build out a delimited list using the ISNULL and COALESCE functions. This functionality is great if you need to return the delimited list through a function or persist into a variable, however what if you need to return a comma separated list in a result set. This blog post will look at, and describe, how to build out a delimited or comma separated list…

Data Sets with JSON

Prior to SQL Server 2016, SQL Server did not provide many options to work with JSON documents. However, JSON has become a favored format for developers to pass data between applications and application layers. This blog post we will take a look at how to build a JSON document and return it in a result set. We are going to build on a previous blog post where we built a comma separated list of…

Data Sets with JSON (SQL 2016)

With the release of SQL Server 2016 JSON objects support is now available. With the ever growing popularity and versatility of JSON, including JSON support into SQL Server is good for both database and application developers. In a previous blog post we looked at how to output a JSON object in a dataset using the FOR XML PATH clause and some sting concatenation. This process can be rather…

Comma Separated Values with NULL Functions

Comma Separated Values or values that are delimited by a specific character are an easy way to pass a list of values from a function or in a dataset.  There are a couple ways to create comma separated values within SQL Server, as a single set which could be useful in a scalar function, or in a dataset which could allow passing additional values in a flat data set.  This blog post is going to…

SSIS Importing and Validating Credit Card Data

I spent a lot of my early career working with POS data. The irony of Point of Sale data is it is really was POS data. I spent a lot of time validating data all sorts of data, one of the big pain points was validating credit card data. When I decided to write a presentation that’s all about SSIS Scripting, the first demo idea that came to me was validating credit card data. In this blog post we…

Simple SSIS: Date Formats

One feature that I would like to see added to SSIS is the ability to format a date string with a specified format. I have been tasked many times with outputting file names or folders with a specific date format, such as YYYYMMDD, and found myself writing custom functionality to accomplish this task. This is not difficult in T-SQL, but in SSIS it’s not all that easy. In this blog post I am going…

Winner Winner Chicken Dinner

“Winner Winner Chicken Dinner”, and “Vegas, Vegas, Vegas” are quotes from two of my favorite Las Vegas movies. It’s no secret that my wife Kristin and I love Las Vegas, it’s also no secret that I love coming up with ingenious database solutions to random problems. So I was thinking, what it would take to create a simple 3 wheel slot machine in SQL Server. Is SQL Server the right place to put…

Simple SSIS: Traversing Files in a Directory

There are many common daily tasks that (SSIS) SQL Server Integration Services implements with minimal effort. These tasks can be done by adding a few SSIS tasks, data flows, and containers. In this blog series I am going to explore some of these simple tasks and how I go about implementing them. Looping through a directory of files definitely qualifies as simple task using SSIS. In this blog post…

Simple SSIS: Importing Data from Flat Text Files

There are many common daily tasks that (SSIS) SQL Server Integration Services implements with minimal effort. These tasks can be done by adding a few SSIS tasks, data flows, and containers. In this blog series I am going to explore some of these simple tasks and how I go about implementing them. Importing data from flat text files into a data store definitely qualifies as simple task using SSIS.…

SSIS Find Non-Imported Files

Traversing a folder looking for files to import into a database table is a common practice using integration services. What if you have little control over the import folder or files because of security restrictions? What if there are many files stored in the folder and number of files grows daily? One way to deal with this is using a Foreach Loop Container, call to a database table or log and…

T-SQL Bitwise Matching Logic – #TSQL2sDay

A good friend of mine Rick Kruger (@DataOgre|blog) is hosting this month’s T-SQL Tuesday Blog…

In prior blog posts I talked about a Standard PIVOT query and a Dynamic PIVOTquery. It is only fitting that I talk about how to implement an UNPIVOT query. As DBA’s we commonly unpivot data and turn it into normalized data for storing in a relational data…

Rules of Engagement - NoSQL Graphing Databases

Well Rick Krueger( blog | @DataOgre) and I are back at it again. We have decided to pair up and do…

Rules of Engagement - NoSQL Column Data Stores

Well Rick Krueger( blog | @DataOgre) and I are back at it again. We have decided to pair up and do…