Key tools for Data platform users: SSMS and SSDT- checkout enhancements and new options


It is always good enough to combine both relational database and business intelligence (BI) tools in one tool, SQL Server Data Tools (SSDT) is that name where you can get into one installed with an additional support to SQL Server Integration Services (SSIS) and SQL Server Reporting Services (SSRS).

SQL Server Data Tools (SSDT) is a modern development tool that you can download for free to build SQL Server relational databases, Azure SQL databases, Integration Services packages, Analysis Services data models, and Reporting Services reports. With SSDT, you can design and deploy any SQL Server content type with the same ease as you would develop an application in Visual Studio.

SSDT is based on Visual Studio and co-released with SQL Server as a free web download. This page provides links to preview and generally available versions of SSDT for Visual Studio 2015. You can also install SSDT based on older versions of Visual Studio that run side-by-side newer versions. See Previous releases of SQL Server Data Tools (SSDT and SSDT-BI) for details.

See this table for more information:

Download SQL Server Data Tools (SSDT)

SQL Server Data Tools Release status SQL Platform Support Project templates
Download the Latest SSDT Preview for Visual Studio 2015

Provides the unified template collection for relational database development, SSAS data models, SSRS reports, and SSIS packages.

Preview release, built on Visual Studio 2015 SQL Server 2016 and all earlier, supported releases

Azure SQL Database

Azure SQL Data Warehouse

Relational databases

Analysis Services models

Integration Services packages

Reporting Services reports

Download SQL Server Data Tools for Visual Studio 2015 (1) Product release, built on Visual Studio 2015 SQL Server 2014 and all earlier, supported releases

Azure SQL Database

Relational databases

You don’t need an introduction to SQL Server Management Studio (SSMS) which is an integrated environment to manage your SQL Server infrastructure instances with additional capabilities of Azure and on-premise data platform management. SQL Server Management Tools (including SSMS)

To download and install the latest version of SQL Server Management Studio (SSMS), see Download SQL Server Management Studio (SSMS).

 

For SSDT not just downloads and all that, from SQL Server 2016 CTP3 onwards we can use machine learning project using SQL Server 2016 SSIS and R Services (SSIS blog). From CTP 3 SQL Server 2016 and its native In-database support for the open source R language (SQL Server R Services), users can now call both R and RevoScaleR functions and scripts directly from within a SQL query and benefit from multi-threaded and multi-core in-DB computations. The R integration brings the utility of data science to your applications without the need to ‘export’ the data to your R environment. Moreover, users can now use SQL Server Integration Services (SSIS) to:

–  extract data from various on-premises and/or cloud sources to build training data

–  extract data from various on-premises and/or cloud sources to prepare production data

–  operationalize your R code to build and run a R model as part of your data integration workflow

–  operationalize your R code to build a workflow to retrain your R model regularly

–  load result from your R script to other destinations such as Excel, Oracle, Teradata and more

Further to this SSIS Blog confirms that:

Getting your SSIS custom extensions to be supported by the multi-version support of SSDT 2015 for SQL Server 2016 | SQL Server Integration Services (SSIS) Official Blog

Having your assemblies in the appropriate folder

First, you need to make sure you build your custom components for each supported versions of SSIS (e.g. SSIS 2012 components, SSIS 2014 components).  For each version, you also need to make sure your custom components assemblies references to the correct version of the SSIS assemblies (e.g. Your SSIS 2012 custom component should only reference to the SSIS 2012 Microsoft.SqlServer.ManagedDTS.dll). Then you need to put your extension assemblies in task version folder as well as GAC. For example:

–          For SSIS 2012 custom tasks, you need to put in the use %programfiles(x86)%\Microsoft SQL Server\110\DTS\Tasks folder.

–          For SSIS 2014 custom components, you need to put in the %programfiles(x86)%\Microsoft SQL Server\120\DTS\Tasks folder

–          For SSIS 2016 custom components, you need to put in the %programfiles(x86)%\Microsoft SQL Server\130\DTS\Tasks folder

 

Adding extension map file for SSIS 2014 and 2016

Starting from SSIS 2014, custom component developers are required to set an “alias” for each extension. The mapping between the alias and the extension is called “extension mapping”. In %programfiles(x86)%\Microsoft SQL Server\{version}\DTS\UpgradeMappings folder, you can find an “extension.xml” file, which contains all extension mapping for all extensions in the product. You also need to add a new extension map file for your extensions. Below is an example of the extension mapping:

 

<?xml version=”1.0″ encoding=”utf-8″?>
< Extensions xmlns=”http://www.microsoft.com/SqlServer/Dts/Extensions.xsd”>
< PipelineComponents>
< PipelineComponent Identifier=”Martin.MultiHash” Model=”.NET”>
< CreationName>Martin.SQLServer.Dts.MultipleHash, MultipleHash2014, Version=1.0.0.0, Culture=neutral, PublicKeyToken=51c551904274ab44</CreationName>
< TypeConverter name=”MultipleThreads”>Martin.SQLServer.Dts.MultipleHash+MultipleThread, MultipleHash2014, Version=1.0.0.0, Culture=neutral, PublicKeyToken=51c551904274ab44</TypeConverter>
< TypeConverter name=”SafeNullHandling”>Martin.SQLServer.Dts.MultipleHash+SafeNullHandling, MultipleHash2014, Version=1.0.0.0, Culture=neutral, PublicKeyToken=51c551904274ab44</TypeConverter>
< TypeConverter name=”IncludeMillsecond”>Martin.SQLServer.Dts.MultipleHash+MillisecondHandling, MultipleHash2014, Version=1.0.0.0, Culture=neutral, PublicKeyToken=51c551904274ab44</TypeConverter>
< /PipelineComponent>
< /PipelineComponents>
< /Extensions>

 

Once you have updated the extension mapping file and ensured all the custom component assemblies in the appropriate folder, your custom component can now work with the new multi-version support of SSDT 2015!