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 SSIS in many ways. They rewrote the package XML to ease source control integration and make the package code easier to...
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 Mode instead of the default one, the Project Deployment Mode), the only way to enable logging was to enable it in each...
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.
We'll start SQL Server Data Tools, and open the CustomLogging project if not already done:
- In the SSIS Toolbox, scroll to the
Azuregroup. Since the Azure tools are not installed with SSDT, theAzuregroup is disabled in the toolbox. Thee toolss must be downloaded using a separate installer. Click on theAzuregroup to expand it and click onDownload Azure Feature Packas shown in the following screenshot...
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 they were using a manual process to maintain their solution. The source control software helps a lot with this. We...
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.
This recipe will show you a new neat feature of SSIS, which is the error column name. We could achieve something similar before SSIS 2016 but it involves using a script component and it is difficult to reuse this kind of transform. Although we can copy and paste a script component from one package to another, every time we do so, we must change the input columns and recompile the script. It's not very difficult but it's a tedious task. The error column name native implementation in SSIS 2016 is very welcome.
This recipe assumes that you have created the CustomLogging project and package from previous recipe.
- In the
CustomLogging.dstxpackage, navigate to thedft_dbo_CustomLoggingby double-clicking on the Data Flow task. Click on the path (blue arrow) between theole_src_SELECT_1and theole_dst_dbo_CustomLoggingtransform to select it. Right-click on it and selectDeleteto delete it.
- Drag and drop a derived column transform from the SSIS Toolbox...
Control Flow templates are an addition to SSIS 2016 that will surely be promising...in the future. For now, Microsoft put the foundation of something that looks very interesting.
If there's one thing missing with SSIS it is the reusability of custom components without doing .NET code. We'll see how to achieve custom task and transforms later in this book using .NET and you'll see that it's tedious to achieve even for something simple. Let's say that we would like to create a truncated table task; that is, a task that we would use to solely erase a table's content. This task would then appear in the SSIS toolbox and be available to all packages in your projects. This recipe is exactly what we will do using Control Flow templates.
This recipe assumes that you have done all previous recipes in this chapter or you have your own SSIS project open.
© 2017 Packt Publishing Limited All Rights ReservedAuthors (6)
