Running SSIS packages outside the Developer tools using DTEXEC.exe without installing the Integration services.

In this blog, I would like to address one of the important information about SQL Server Integration services licensing.

Say that you had installed the Visual Studio - SQL Server Data Tools (SSDT) / Business Intelligence Development Studio (BIDS) and developed your SSIS packages on your development machines. The SSIS packages has some Script tasks or Script components internally and they execute perfectly fine from the SSDT/BIDS environment (design time) without any issues.

You would like to execute these packages outside the developer tools (SSDT/ BIDS) using the DTEXEC.exe (Microsoft SQL Server Execute Package Utility) tool. In this attempt, you went ahead and installed the SQL Server Express Edition with in turn install these components under the default location wiz. C:\Program Files\Microsoft SQL Server\120\DTS\Binn (For SQL server 2014). Now when you use DTEXEC.exe to execute these packages, your SSIS packages fails with the following error message.

Error Message:

C:\Windows\system32>dtexec /f "c:\users\admin\documents\visual studio 2013\projects\Integration Services Project1\Integration Services Project1\Package.dtsx"

Microsoft (R) SQL Server Execute Package Utility

Version 12.0.2000.8 for 64-bit

Copyright (C) Microsoft Corporation. All rights reserved.

Started: 11:20:30 AM

Error: 2016-10-25 11:20:36.22

Code: 0xC000F427

Source: Script Task

Description: To run a SSIS package outside of SQL Server Data Tools you must install Script Task of Integration Services or higher.

End Error

Warning: 2016-10-25 11:20:36.24

Code: 0x80019002

Source: Package

Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

End Warning

DTExec: The package execution returned DTSER_FAILURE (1).

Started: 11:20:30 AM

Finished: 11:20:36 AM

Elapsed: 5.328 seconds

You may wonder on why the same SSIS packages that were working fine in the Visual Studio SSDT / BIDS developer tools are now failing when executed outside the developer tools (Design time) using the DTEXEC.exe (Run time). Why are they complaining about installing Script Task of Integration Services.

The answer is pretty simple. SQL Server Integration services is not a free product, it comes with the licensed SQL Server installer. You may not be able to execute the complex SSIS packages that contains any tasks / components like Script tasks, Script components, Sort / Aggregate/ Fuzzy Lookup transformations etc. using the DTEXEC.exe runtime outside your developer tools. SSIS packages aren’t meant to be executed this way.

1. So if I cannot execute the SSIS packages outside the developer tools using the DTEXEC.exe without purchasing the SQL Server, then why did you provide this DTEXEC.exe with the SQL Express installation?

SQL Server Import/Export wizard is one of the free tools available with the SQL Server Express Edition using which you can copy data from a source to a destination. SQL Server Import/Export wizard internally creates simple SSIS packages to perform this task. So DTEXEC.exe is provided with the SQL Server Express installation only to help with the execution of the SSIS packages that are created as a part of the SQL Server Import/Export wizard usage. So DTEXEC.exe is provided solely for the purpose of running the SQL Server Import/Export wizard tool only.

Also please note that you would still be able to run your custom SSIS packages using DTEXEC.exe that comes with the SQL Server Express Installation that may contain simpler SSIS tasks/ components like source/destination components, the Data Conversion transformation, Execute SQL Task etc. Executing SSIS custom packages using DTExec.exe which ships with SQL Express is not a recommended way or a supported way of running the SSIS packages and it is expected that the package will fail with above errors when Integration Services is not installed

 

Hope you find this blog helpful to understand the licensing terms for Integration services.

 

Author:        Krishnakumar Rukmangathan – Support Escalation Engineer, SQL Server BI Developer team, Microsoft

Reviewer:    Sarath Babu Chidipothu – Support Escalation Engineer, SQL Server BI Developer team, Microsoft