SQL Server

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

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

compliant databases.

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

provider.

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

file system.

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.

Facebooktwittergoogle_plusredditpinterestlinkedinmail