Friday, June 17, 2016

Whats New in SSIS 2016 - Expose SSIS Package Data Externally as a View


SQL 2016 has brought in lots of new and exciting features on the plate. One of the pretty cool feature that was added to SSIS in SQL 2016 is the ability to expose the data directly as a SQLServer database view object which can be accessed using T-SQL queries.

This is made possible by the introduction of two new components
1. A new linked server provider SSISOLEDB which is capable of exposing the SSIS data to SQLServer database
2. A new data flow component called Data Streaming Destination which will stream the data to be consumed through the linked server based on the above provider

Once you've installed SQL 2016 you can connect to the instance using management studio and when you expand the Server Objects - Linked Servers - Providers you can see this new provider listed as per below

Similarly you can launch SSDT (SQLServer Data Tools) 2015 and start an Integration Services project and create a sample package with a single data flow task. Inside data flow task tab you can see the new destination component available

Now that we have seen the two components lets see how they can be used together for exposing SSIS data as a SQLServer database object.


Consider the scenario where we have data coming from different sources which needs to be exposed to  our database as an object.
For the sake of this article lets consider the below scenario
1. Customer details coming from a SQL database
2. Customet Satisfaction score data coming from an Excel spreadsheet

The requirement is to merge the data from the above sources to be accessed within third database most often a datawarehouse for analytical and reporting needs.
For this kind of requirement the obvious way to approach is to create a SSIS package which can extract the required data from the various source and do the merge operation based on the related columns.
Such a package for the above case would look like this

The data flow tasks will look like below

The sources involved here are SQL database which has the Customer details as per below

OrderID int IDENTITY(1,1),
CustomerName varchar(100),
CustomerEMail varchar(100),
OrderDesc varchar(100),
OrderPrice Decimal(10,2),
OrderDate datetime
INSERT OrderDetails (CustomerName,CustomerEMail,OrderDesc,OrderPrice,OrderDate)
VALUES ('Customer 1','','Order ABC',130,'20151104'),
('Customer 2','','Order PQR',2150.50,'20151203'),
('Customer 5','','Order for DEF and Co',1785,'20160112'),
('Customer 7','','M/S Bhavya Comp Ltd',890,'20160120'),
('Customer 9','','XYZ',1125,'20160205'),
('Customer 1','','DEF',3400,'20160322'),
('Customer 9','','MNU',215,'20160312'),
('Customer 14','','PUK',345,'20160415'),
('Kirby Stevens','','WKP',2300,'20160420'),
('Kerney Michelle','','VER',485,'20160512'),
('Customer 1','','RTU',1320,'20160514'),
('Mark Bowerman','','BJY',1130,'20160603'),
('Customer 9','','SAR',765,'20160610')

And the query used is as below to get the count and total value of orders per customer

SELECT CustomerName,
COUNT(OrderDesc) AS OrderCount,
SUM(OrderPrice) AS TotalOrderPrice
FROM OrderDetails
GROUP BY CustomerName,
ORDER BY CustomerEMail

Source 2 is an excel spreadsheet with customer satisfaction data as below

And for this illustration we use the above query to get the average customer satisfaction score and  response count

SELECT email,
AVG(satscore) AS AvgSatScore,
COUNT(*) AS responseCount
FROM `Sheet1$`
GROUP BY email
ORDER BY email

From these two sources of data would be merged based on the common columns between the two resultsets as shown below

Once this is done we would get the required resultset. Now inorder to stream the resultset we will use Data Streaming Destination component and include the required columns to be accessed through the exposed view.

Data Streaming Destination will include the required columns as shown above
Once this is done we need to deploy the package to the Integration Services Catalog. 
The package will get deployed to the Integration Services Server as shown below

Once this is done we need to stream the package data as a view within a database in the associated instance.
This can be done using data feed publishing wizard which comes with the SQL 2016 installation. 

We can launch data feed publishing wizard and it will come up with the below window

Click Next and it will ask you to select the server and package to be published

Once selected it will also give you the option to modify the default values set for the connection managers and parameters in the package which I'm not changing for this example.

Clicking Next will bring it to Publish settings screen where we will specify the view name through which we need to expose the package data. 

One thing to note here is that Use 32Bit Runtime is set to True as one of the sources involved here is an Excel spreadsheet and the ACE driver installed is of 32 bit version in the server. In case you've the 64 bit version installed then you don't need to tweak this setting.
On clicking Next it goes to the validation screen and validates the various steps involved

Click on Next and finally click Finish to publish the package as a view

Once publishing is done you can connect to the server instance through SQL Management Studio and go to the specified database and expand views to see the view you published

Right click and Select data from the view and it will execute the package on the background and will provide you with the below result

You can confirm this by expanding Integration Services Catalog and finding the package within your deployed project and then right clicking and choosing the All Executions report which will show the log for successful execution of the package when view gets referred in the query


As shown by the illustration above you can use the new data feed publishing wizard available in SQL 2016 for exposing a SSIS package resultset as a view object within an associated relational database. This is a very useful feature which can be used for publishing a resultset consisting of data extracted from a variety of sources.

Thursday, June 2, 2016

SQL Server 2016 Available for General Availability Now

SQL 2016 has been released for general availability now
You can find the download links from MSDN

The evaluation period is for 180 days

SQL 2016 comes with lots of useful and cool features including

  • Native JSON support
  • Always Encrypted to protect sensitive data
  • Integration of R analytics scripts in T-SQL
  • Dynamic Data Masking
  • Polybase querying for integration with Hadoop, blob data etc
  • QueryStore providing historical execution plan information
  • Temporal tables with data versioning

Check out the announcement in official SQLServer blog for more details

One more exciting news is that SQLServer developer edition is now free starting from 2014 onwards. 

This provides us with one more compelling reason for trying out this exciting new release

Friday, April 29, 2016

T-SQL Tips: Can a UDF Return Values of Multiple Datatypes?

This was a question raised in one of the forums recently.
This blog explains how we can create a user defined function (UDF) which can return values of different datatypes under different conditions.
Consider the case where we require creating a user defined function which based on the conditions have to return values of different data types. Since a user defined function can have only a single datatype for the return value we need to find a datatype which can store values for different data types. There's a datatype available in SQLServer called sql_variant which can be used for this purpose

As per MSDN sql_variant is :-

A data type that stores values of various SQL Server-supported data types.

So we can utilize sql_variant datatype as the return value for the user defined function which will have the ability to store and return values for all the SQLServer supported datatypes

As an illustration we can consider a user defined function as below

ALTER FUNCTION dbo.TestVariant
@Mode char(1)
RETURNS sql_variant
DECLARE @Ret sql_variant
IF @Mode = 'i'
SELECT @Ret = 1
ELSE IF @Mode = 'v'
SELECT @Ret = 'Test'
ELSE IF @Mode = 'd'
SELECT @Ret = '2015-06-17'
ELSE IF @Mode = 'n'
SELECT @Ret = N'സ്വാഗതം'
ELSE IF @Mode = 'u'
SELECT @Ret = '9DF38CBA-D314-42D8-9DF2-E4BC3108501C'


If you analyse the function you can see that UDF returns values of different datatypes based on the input value for @Mode parameter.
Now try executing the function

SELECT dbo.TestVariant('i')


similarly see the output for other values of @Mode parameter

As seen from the output its evident that the data type of the value returned by UDF will change based on the input value. Thus we can make use of sql_variant datatype to make sure UDF returns values of various data types.

Tuesday, April 12, 2016

Power BI Tips: Creating a Map Visualization in Power BI


This blog explains how you can create a map visualization based report in Power BI desktop. Map visualization is one of the cool features that Power BI provides for doing analysis based on geographical locations. Its pretty useful tool when you want to show any data broken up by geographical locations.


To illustrate this, we can consider the below scenario
Consider the case of a retail chain having stores across multiple locations. The consolidated sales figures has been captured as per the below

Now lets see how we can visualize this data using Power BI's map visualization. The cool thing about this is that we dont need to do anything special to make this happen. The map visualization is able to display the location by identifying it from our data. This is made possible by Bing map APIs which it uses under the hood. From the location details it will be able to plot locations.
Now launch the Power BI desktop and point to the above data which is contained in a Excel or a CSV file using Get Data option

Map Visualization

Once the data is ready we can add the map visualization to the Power BI page from the visualizations options

Once you add the visualization you will get below control on the page

Select the control and drag State field to Location and Sales field to Values and you will get the below

You will see that the Bing map control automatically plots the data points in the map based on the location based field (State in this case)
You can see the data by hovering over the respective points in the map as shown below

This will help you to analyse the Sales for the state from the map.
Now if you analyze the data carefully we can see that there are few states where we have details for multiple districts.

If you want to analyze the district wise share of the data then we can make a small tweak in the above map to achieve this. To make this possible drag the District field to Legend property and then map will modify as below

If you analyze this you can see that each point now turns into a pie chart kind of format showing the contribution part of each of district within the state, Hover over a part and it will show district and state information corresponding the the part along with the value for the measure Sales. This is a very helpful visualization for quick reference without even having a need to drilldown to analyze the next lower level details in a hierarchy (geography in this case).

Filled Map Visualization

Another type of map visualization available in Power BI is called filled map visualization. This will plot the data points by filling the corresponding locations in the map. The intensity of the color changes as per the plot value with larger values being displayed in darker colors.
Now lets see how a filled map will look for the same data

The visualization fills the locations (States) based on the value (Sales)


As you see from above example map visualization is a very good option which you can use to do a quick reference analysis of data based on geographical data. Another good thing about this is that this is available as a standard option within Power BI desktop tool is now free to download. Only if you need to deploy this to a server you would need to take a license.
Hope you enjoyed this article on map visualization in Power BI. So what are you waiting for? Download a free copy of Power BI desktop and starting trying out!

Wednesday, March 30, 2016

Windows Build 2016 : Live Streaming

Here's a golden opportunity to watch the live streaming of the Microsoft Build 2016 Conference here for those whose are unable to attend. Microsoft Build is an annual conference event held by Microsoft for Developers. And this year its being held from March 30 – April 1 in San Francisco, CA.