Tips for using SQL Server 2016 Integration Services (Part 3)
Consider encrypting Integration Services packages.
SQL Server 2016 Integration Services packages can be encrypted with various
levels of encryption. By encrypting packages, you can protect sensitive data.
If you need to modify column values, you can use the Data Conversion and
Derived Column transformations.
These transformations were first added in SQL Server 2005 Integration Services.
The Data Conversion transformation is used to convert the data type of a column
to a different data type. The Derived Column transformation is used to populate
columns with the results of expressions.
Use Partial cache mode or Full cache mode with the Lookup transformation.
Consider using Partial cache mode with the Lookup transformation when the incoming
data is repetitive and only references a small percentage of the total reference
table. Consider using Full cache mode with the Lookup transformation when the
reference table is relatively small and the incoming data references the full
spectrum of reference table rows.
Try to avoid SSIS lookups.
Because SSIS lookups slow down performance, try to use the Transact-SQL statements
to perform the same functions.
Use Integration Services performance counters for monitoring the performance
of the data flow engine.
For example, you can use the “BLOB bytes read” and “BLOB bytes written” counters
to check the number of bytes of binary large object (BLOB) data that the data flow
engine has read from or write to all sources. By using the Performance Microsoft
Management Console (MMC) snap-in, you can create a log that captures performance
If you need to sort data or identify similar data rows, you can use the Sort
and Fuzzy Grouping transformations.
The Sort transformation is used for sorting data and identifying duplicate data
rows. The Fuzzy Grouping transformation is used for identifying similar data rows.
Use Integration Services tasks that shipped with SQL Server 2016.
Though you can create your own task to build packages (you can write custom tasks
using a programming language, such as Visual Basic or C#) it is a good idea to use
tasks that shipped with SQL Server 2016. If it is possible to use internal SSIS
tasks, you should use them.
Consider increasing the “Commit Size” option.
This option is only available in the OLE DB Destination when using the SQL Server
OLEDB driver. By increasing the “Commit Size” option you can set a larger buffer
commit size for loading into SQL Server. This can improve performance when loading
large amounts of data into SQL Server.
Try to eliminate the data that needed to be transferred using the SSIS data flow.
To eliminate the data that needed to be transferred, you should remove unneeded
columns and rows from the source. Specify in the SELECT statement only the
necessary columns and use the WHERE clause to specify only the necessary rows.
Use the bcp utility instead of Integration Services when you need to export
data from the SQL Server table into a text file.
The bcp utility is much faster than SSIS, so try to use it whenever possible.
Consider using the debug dump files to help troubleshoot the SSIS package problems.
SQL Server 2016 Integration Services support the debug dump files (.mdmp and .tmp)
that provide information about what happens when a package runs. You can use this
information to troubleshoot problems that occur when you run the package.
Use SSIS 2016 Project Connection Managers.
The SSIS 2016 allows you to create connection managers at the project level that
can shared by multiple packages in the project. The connection manager you create
at the project level is automatically visible in the Connection Managers tab of
the SSIS Designer window for all packages.
Consider increasing the EngineThreads property.
This data flow property controls the number of worker threads the Execution
Engine will use. The default value for this property is 5. To improve the SSIS
performance, check how many threads the data flow naturally needs and try to keep
the EngineThreads value reasonably close to it.
If you need to create normalized data rows from non-normalized data or vice
versa, you can use the Pivot and Unpivot transformations.
These transformations were first added in SQL Server 2005 Integration Services
and make it easy for developers to build packages with complex data flow without
writing any code.
Use breakpoints when you debug the packages.
SQL Server 2016 Integration Services provide the ability to set breakpoints on
packages, containers, and task events. Using breakpoints can help you debug your
packages and find potential problems during the testing before transferring to
Consider signing Integration Services packages.
SQL Server 2016 Integration Services includes digital signatures for packages.
By signing packages, you can prevent users without appropriate certificate to
execute the package.
Try to avoid using data pump operations that use ActiveX transformation scripts.
Because data pump operations that use ActiveX transformation scripts can be much
more slowly than copy operations, you should avoid using ActiveX transformation
scripts whenever possible.
Use Integration Services roles for controlling access to packages.
SQL Server 2016 Integration Services includes the three fixed database-level
roles. There are: db_dtsadmin, db_dtsltduser, and db_dtsoperator roles. The
members of the db_dtsadmin role have full access to all packages. The members
of the db_dtsltduser role can perform all actions with their own packages, such
as creating, changing, deleting and executing packages. The members of the
db_dtsoperator role can perform only the read actions, such as view packages
and execute packages.