The 2016 release of SQL Server Integration Services is a major revision of the software. But, instead of being a complete re-write of the product, it's more an evolution of the product. Here is the SSIS timeline since its beginning in SQL Server 7.0 (1998):

In the early years of SQL Server, Microsoft introduced a tool to help developers and database administrator (DBA) to interact with the data: Data Transformation Services (DTS). The tool was very primitive compared to SSIS and it mostly relied on ActiveX and T-SQL to transform the data. SSIS V1.0 (2005) appeared in 2005. The tool was a game changer in the ETL world at the time. It was a professional and (pretty much) reliable tool for 2005. 2008/2008 R2 versions were much the same as 2005 in the sense that they didn't add much functionality, but they made the tool more scalable.

In 2012, Microsoft enhanced...

This section will walk you through the various steps to create an SSIS Catalog in SSMS. As mentioned before, the SSIS Catalog contains information about the package components and their execution. As we will see later in the book, SSIS projects are deployed into this catalog. It can be easily queried for custom reports as well, allowing us to create SSIS executions using T-SQL. This is very useful for on-demand executions of SSIS packages.

SSIS versions prior to 2012 did not have these capabilities since the catalog appeared with 2012. It is still possible to bypass the deployment to an SSIS Catalog by using a special mode: the package deployment model. This is mostly used for backward compatibility with previous SSIS frameworks.

This section will talk about various loggings and how we can customize logging to suit our needs in terms of logging information. The reason why we need logging is because we want to retrieve some information on our package executions.

Here are some examples of logging info we might be interested in getting:

• How much time it took to execute a specific package
• How many rows have been transferred from one transform to another in our data flows
• What were the warnings or errors that were issued by the package execution
• The new values that have been assigned to a variable in a package, and so on

All the topics listed here will be discussed in the next sections of the book. For now, we'll focus on the customized logging levels.

There are various ways that we can log package execution information in SSIS. In versions prior to 2012 (or if we opt for a Package Deployment...

This section will guide you on how to install the Azure Feature Pack that, in turn, will install Azure control flow task and data flow components. The Azure ecosystem is becoming predominant in Microsoft ecosystems and SSIS has not been left over in the past few years.

The Azure Feature Pack is not an SSIS 2016 specific feature. It's also available for SSIS version 2012 and 2014. It's worth mentioning that it appeared in July 2015, a few months before the SSIS 2016 release.

This section assumes that you have installed SQL Server Data Tools 2015.

Prior to SSIS 2012, packages needed to be deployed one by one. We were usually downloading all packages from the source control software, such as Team Foundation Server (TFS), Visual Source Safe, SVN, and so on. Once downloaded, packages were moved to their destination. At that time, the person who deployed the packages had the choice to overwrite or skip existing packages. Usually, they overwrote all the packages since they were using the source control.

For those who didn't use the source control, they had all the necessary flexibility to deploy what needed to be deployed. Usually, they were keeping a backup somewhere on a file share of all packages. The reason why they chose what to deploy was mainly because they had doubts about the consistency of the packages in the file share. They were simply not sure of the state of the packages because...

Since its inception, SSIS designer never supported backward compatibility. For example, if you developed a package in SSIS 2014 and tried to deploy it in a SSIS 2012 catalog, you would not be able to do it. Or worse, if you opened a package developed with SSIS 2012 with a SSDT that was used with SSIS 2014, the package was upgraded. When another developer tried to open it with SSDT used for SSIS 2012, he/she was not able to do it. The package was upgraded, period.

With SSIS 2016, SSDT had the following enhancements:

• Backward compatibility to prior SSIS versions down to SSIS 2012.
• Unified SSDT: as we'll see later in the book, SSDT can be used for BI components development as well as database development. Prior to SSDT for Visual Studio 2015, it was confusing whether we were using SSDT-BI for BI development or SSDT-SQL for database development.