Tips for using SQL Server 2016 Integration Services (Part 2)
Consider disabling constraints during the data load operation.
To disable constraints during the data load operation, you can turn off the
“Check constraints” option in the OLE DB Destination Editor.
Try to avoid using the Aggregate and Sort transformations.
Because using these transformations may be very resource expensive, you should
try to sort and aggregate data at the source.
Use SQL Server 2016 Integration Services containers.
Containers are SSIS objects that provide structure to packages and services to
tasks. Containers can include tasks and other containers. SSIS supports four
containers: Foreach Loop Container, For Loop Container, Sequence Container, and
Task Host Container. The Foreach Loop Container is used to run a control flow
repeatedly by using an enumerator. The For Loop Container is used to run a control
flow repeatedly by testing a condition. The Sequence Container is used to group
tasks and containers into control flows that are subsets of the package control
flow. The Task Host Container is used to encapsulate a single task (it allows to
use variables and event handlers on the task level).
Consider using an ADO NET destination component to load data into ADO.NET
The ADO NET destination component was first introduced in SQL Server 2008
Integration Services. This component is used to load data into a variety of
ADO.NET-compliant databases that use a database table or view.
Use the Lookup and Fuzzy Lookup transformations if you need to extend data with
values from a lookup table.
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.
Consider enabling “Table Lock” during the data load operation.
To enable “Table Lock”, you can turn on the “Table Lock” option in the OLE DB
Destination Editor. Selecting this option also enables fast load, which tells
the adapter to use the IRowsetFastload bulk insert interface for loading. Keep
in mind that fast load delivers do not provide as much information if there is
an error. So, you should disable “Table Lock” option during developing and enable
it only when deploying to production.
If you run SSIS packages on the same machine as SQL Server, use the SQL Server
Destination component instead of the OLE DB Destination.
Because using the SQL Server Destination Adapter provides better performance in
comparison with the OLE DB Destination, you should use SQL Server Destination
component whenever possible.
Try to avoid using the “Table or view” access mode in the OLE DB Source Adapter.
You should use the SELECT statement to retrieve data as more quickly way to perform
the same operation.
Use the Script task or the ActiveX Script task to extend SSIS package functionality.
These tasks were first added in SQL Server 2005 Integration Services. The Script
and ActiveX Script tasks are used to extend package functionality beyond the
functionality provided by the built-in Integration Services tasks.
Deploy your projects to the Integration Services server.
In SQL Server 2016 Integration Services, you can deploy your projects to the
Integration Services server. This is the new project deployment model. The
SSIS 2016 enables you to manage packages, run packages, and configure runtime
values for packages by using environments.
Use SQL Server Import and Export Wizard to import data from Azure Blob Storage.
SSIS 2016 supports the Azure Blob Storage in the SQL Server Import and Export
Wizard. Now the SQL Server Import and Export Wizard can import data from and save
data to Azure Blob Storage.
Consider using an ADO NET source component to get data from a .NET Framework
This component is used to consume data from a .NET Framework provider and make
the data available to the data flow.
Use the Analysis Services Execute DDL task for running Analysis Services data
definition language (DDL) scripts.
SQL Server 2016 Integration Services include a number of tasks that perform
business intelligence operations, such as running scripts written in the Analysis
Services data definition language.
If you need to combine data rows from multiple upstream data flow components,
you can use the Union All, Merge, and Merge Join transformations.
These transformations make it easy for developers to build packages with complex
data flow without writing any code.
Avoid using Integration Services if it is possible to use Transact-SQL
statements, such as a SELECT INTO statement, to transfer data.
Using Transact-SQL is the fastest way to move data between SQL Server tables,
so try to use Transact-SQL statements to move data whenever possible.
Use the File System task to upload, download and copy files and folders in the
This task was first added in SQL Server 2005 Integration Services. SQL Server 2016
Integration Services include some tasks that perform data preparation operations,
such as performing operations on files and folders in the file system.
If you should use ActiveX scripts in your data pump operations, try to use
VBScript instead of JScript or PerlScript.
Microsoft recommends using VBScript because scripts written in VBScript run
approximately 10 percent faster than scripts written in JScript, when the
scripts written in JScript run approximately 10 percent faster than scripts
written in PerlScript.
Close all extraneous programs running and stop all extraneous services.
Because extraneous programs and services use some system resources, it is a
good idea to close all extraneous programs and stop all extraneous services
before loading data into or out SQL Server.