Sunday, 31 July 2016

SSIS 2016 Feature Pack for Azure

A while ago I did a post on the SSIS (2012/2014) Feature Pack for Azure. Now a new version has been released for SSIS 2016 and they made some minor changes.

Download
If you start SSDT 2015 you will see a greyed out Azure toolbox section. If you select it you will find a download link. Download and install both the 32bit and the 64bit version.
Download link in the toolbox

























Installation
Installation (of both 32 and 64bit) is very simple Next, Accept, Next, Install and Finish.
Feature Pack for Azure Setup

























New toolbox items


























New features: 1 compression
The Azure Blob Source and Destination now support (de)compression (GZIP/DEFLATE/BZIP2), but the upload and download task don't.
Azure Blob Source

Azure Blob Destination











































New features: 2 Storage Connection Manager
The new Storage Connection Manager now also supports the new Storage Account (and also the Classic Storage Account). The old storage account uses the REST API and the new storage account uses the Azure Resource Manager (ARM) API, which is wrapped in PowerShell. Nearly the same, but managed differently. The new Storage Connection Manager also has an extra option to choose an other domain (Azure moon cake).
New Storage Connection Manager
Old Storage Connection Manager




































Unfortunately still no 'Azure File System Task' (please upvote).


All download links:
2012: https://www.microsoft.com/en-us/download/details.aspx?id=47367
2014: https://www.microsoft.com/en-us/download/details.aspx?id=47366
2016: https://www.microsoft.com/en-us/download/details.aspx?id=49492

Sunday, 19 June 2016

SSIS Data Streaming Destination

Case
What can you do with the Data Streaming Destination in SSIS and how does it work? It only has an 'Advanced' editor with very little explanation.
Editor Data Streaming Destination
























Solution
You probably never used or even saw this destination component before because it was a separate download in SSIS 2012 and for SSIS 2014 I couldn't even find the download page. But now for SSIS 2016 it's one of the standard toolbox items. And even now it's in the wrong toolbox section: you will find it under Common instead of Other Destinations. That can be easily solved by moving it the appropriate section of the SSIS Toolbox.
Moving Data Streaming Destination


























The Data Streaming Destination allows you to query its output via a linked server connection on the SSISDB. If certain sources or transformations are very hard with standard TSQL then you could solve it in an SSIS package. After that you can query its output with TSQL. Yes I know almost every thing is possible with TSQL when you for example use CLR stored procedures, but SSIS is just a visual alternative.


1) Data Flow Task
For this example I will use a package with one very basic Data Flow Task with a Flat File Source Component, a Derived Column and the Data Streaming Destination.
Simple Data Flow with Data Streaming Destination






















2) Data Streaming Destination
When you add the Data Streaming Destination and edit it, you will get the Advanced Editor. It only allows you to choose the input columns and you can change the name of an automatically generated identity column (see previous screenshot). For this example I pass through all columns and leave the id column name unchanged.
Pass through all columns

























3) Saving and deploying
Now I need to deploy the package(s) to the SSIS Catalog. You could add parameters and create an SSIS Catalog environment to fill them on package start. I will skip that for this basic example.
Deploying packages to SSIS Catalog

























4) Data Feed Publishing Wizard
Now start the SQL Server Integration Service Data Feed Publishing Wizard from the start menu and choose which package to execute in your view. You also need to provide the name and location of your view. For Linked Server validation errors go to step 4b.
(C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\ISDataFeedPublishingWizard.exe)
SSIS Data Feed Publishing Wizard


























4b) Error: The Allow Inprocess option for the OLE DB Provider is not enabled.
When you get a Linked Server error during validation you need to enable "Allow inprocess".
The Allow Inprocess option for
the OLE DB Provider is not enabled.






















Go to SSMS and connect to your server where the SSISDB is running. Expand Server Objects, Linked Servers, Providers and then right click SSISOLEDB and choose Properties. In the Provider Options enable "Allow inprocess" and click OK. After that Rerun volition in the Wizard.
Provider Options, Enable "Allow inprocess"


















4c) Error: The are more than one Data Streaming Destination components in the package and only one of them can pass data to a SQL Server database
The error says it all: You can only have one Data Streaming Destination! Remove the others first and rerun validation.
The are more than one Data Streaming Destination components
in the package and only one of them can pass data
to a SQL Server database

























5) Testing the view
Go to SSMS and execute your newly created view. You probably have to test you patience a little bit because it's not very fast. It first has to execute the package which takes a couple of seconds. I haven't found a good purpose for a real-life situation yet, but may be you can use it to create a (nearly) real time data feed from a webservice for your PowerBI report. Or..... let me know in the comments where you used it for.
Querying the new view 
















If you don't want to use the wizard you could just do it with TSQL:

USE [SSISJoost]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[MyDataStream] AS SELECT * FROM OPENQUERY([Default Linked Server for Integration Services], N'Folder=DataStream;Project=DataStream;Package=DataStream.dtsx')
GO
Related Posts Plugin for WordPress, Blogger...