SQL Server

Tips for using SQL Server 2016 Integration Services (Part 3)

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

counters.

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

production environments.

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.

Facebooktwittergoogle_plusredditpinterestlinkedinmail