In this Blog we'll list out 11 new much awaited features in SSIS 2016.
Note : By the time I am writing this blog SQL Server 2016 (RC3) and CTP 3.3 is there in the market.
1. Incremental Package Deployment :
In SQL Server 2005 and 2008 we used to deploy either through file system or to the msdb database. We used to deploy a package at a time and if we need to delete\update\add a new package, we simply were able to deploy that 1 package. But tin 2012 and 2014 we had to deploy the entire project containing all many packages at one go. The drawback was even if 1 package is changed the entire project had to be deployed again.
Thankfully in 2016 we can deploy entire project in one go as before and we also now have the flexibility to deploy package-by-package.
2. Separate Installation of SQL Server Database Engine and SSDT :
Compared to SQL SERVER 2005, 2008 we used to install SQL Server Database Engine and BI Tools in one go by just selecting the features; SSDT is installed separately in SQL Server 2012, 2014 and continues in 2016.
3. Custom Logging Level in SSIS Catalog :
Previously we had 4 built-it logging levels to choose from : Basic, Performance, Verbose and None. Now we can create multiple custom logging levels and save them to use\choose while running packages. Hence it is flexible, reusable, portable and easy to use. While creating custom logging level we have 3 categories of statistics to choose from : Executable Execution Statistics, Component Data Volume Statistics & Component Execution Statistics.
To show custom logging levels we now also have a new view catalog.cutomized_logging_levels.
A new role ssis_logreader role has also been introduced.
4. SSDT - Visual Studio 2015 accommodates all different versions in single installation :
No more requirements for installing several versions of SSDT. In configuration properties we can now choose TargetServerVersion as SQL Server 2012, SQL Server 2014 or SQL Server 2016.
5. Column Name in error output :
Now we have full path to error (Error Column - Description) column in output not just column id. Great help in debugging and identify the bad columns.
6. Package Parts :
We can now create a new control flow package part file (.dtsxp) and save it for reuse if something is to be reused multiple times. It will reflect in Package Parts folder and also in SSIS Control Flow Tool Box under package parts. Note that it is visible only for that project only.
7. Explicit Support for Excel 2013 and Excel 2016
8. Connection Mangers for HDFS :
For performing common HDFS operations we have connection managers to connect to hadoop clusters and tasks.
9. SQL Server Import Export Wizard :
Now we can import\export data to\from Azure Blob storage using Import Export Wizard
10. Project Upgrade :
When we upgrade from prev version to current version project level connection mangers work as expected, Layout and annotations remain unchanged.
11. Enable Always on support for Integration services catalogs :
Thanks you for reading. Please like and comment
Paras Singh
Follow me on twitter @msbigyan
Note : By the time I am writing this blog SQL Server 2016 (RC3) and CTP 3.3 is there in the market.
1. Incremental Package Deployment :
In SQL Server 2005 and 2008 we used to deploy either through file system or to the msdb database. We used to deploy a package at a time and if we need to delete\update\add a new package, we simply were able to deploy that 1 package. But tin 2012 and 2014 we had to deploy the entire project containing all many packages at one go. The drawback was even if 1 package is changed the entire project had to be deployed again.
Thankfully in 2016 we can deploy entire project in one go as before and we also now have the flexibility to deploy package-by-package.
2. Separate Installation of SQL Server Database Engine and SSDT :
Compared to SQL SERVER 2005, 2008 we used to install SQL Server Database Engine and BI Tools in one go by just selecting the features; SSDT is installed separately in SQL Server 2012, 2014 and continues in 2016.
3. Custom Logging Level in SSIS Catalog :
Previously we had 4 built-it logging levels to choose from : Basic, Performance, Verbose and None. Now we can create multiple custom logging levels and save them to use\choose while running packages. Hence it is flexible, reusable, portable and easy to use. While creating custom logging level we have 3 categories of statistics to choose from : Executable Execution Statistics, Component Data Volume Statistics & Component Execution Statistics.
To show custom logging levels we now also have a new view catalog.cutomized_logging_levels.
A new role ssis_logreader role has also been introduced.
4. SSDT - Visual Studio 2015 accommodates all different versions in single installation :
No more requirements for installing several versions of SSDT. In configuration properties we can now choose TargetServerVersion as SQL Server 2012, SQL Server 2014 or SQL Server 2016.
5. Column Name in error output :
Now we have full path to error (Error Column - Description) column in output not just column id. Great help in debugging and identify the bad columns.
6. Package Parts :
We can now create a new control flow package part file (.dtsxp) and save it for reuse if something is to be reused multiple times. It will reflect in Package Parts folder and also in SSIS Control Flow Tool Box under package parts. Note that it is visible only for that project only.
7. Explicit Support for Excel 2013 and Excel 2016
8. Connection Mangers for HDFS :
For performing common HDFS operations we have connection managers to connect to hadoop clusters and tasks.
9. SQL Server Import Export Wizard :
Now we can import\export data to\from Azure Blob storage using Import Export Wizard
10. Project Upgrade :
When we upgrade from prev version to current version project level connection mangers work as expected, Layout and annotations remain unchanged.
11. Enable Always on support for Integration services catalogs :
Thanks you for reading. Please like and comment
Paras Singh
Follow me on twitter @msbigyan
No comments:
Post a Comment