SQL Server

Troubleshooting SQL Server 2014 Integration Services

Troubleshooting SQL Server 2014 Integration Services

If you have problems with SQL Server 2014 Integration Services (SSIS), review this troubleshooting
checklist to find potential solutions.

1. Install the latest SQL Server 2014 service pack.

Because many SQL Server 2014 Integration Services bugs were fixed in SQL Server service
packs, you should install the latest SQL Server service pack.
At the time this article was written the latest SQL Server 2014 service pack was service
pack 1. You can download the SQL Server 2014 service pack 1 at here:
https://www.microsoft.com/en-us/download/details.aspx?id=46694

2. When you try to change the Run64BitRuntime property of an Integration Services
project by using the SQL Server Data Tools (SSDT), you find the property is disabled
and cannot be changed.

This is SQL Server 2014 bug. This bug was first fixed in Cumulative Update package 1 for
SQL Server 2014. You can download the Cumulative Update package 1 for SQL Server 2014
at here:
https://support.microsoft.com/en-us/kb/2931693

3. Integration Services catalog permissions don’t work for Windows groups.

In this case, the user cannot see the packages to which they were granted permissions and
cannot validate or run those packages. This bug was first fixed in Cumulative Update package 1
for SQL Server 2014. You can download the Cumulative Update package 1 for SQL Server 2014
at here:
https://support.microsoft.com/en-us/kb/2931693
To work around this problem, use a user-defined database role in SQL Server 2014 to
encapsulate the necessary list of Windows users, and then assign the permissions to that
database role.

4. The dtexec command fails and the package cannot be executed when one of the
configuration files does not exist in SQL Server 2014.

In this case, the following error occurs: “DTExec: Could not import the configuration
file .” This bug was first fixed in Cumulative Update package 1
for SQL Server 2014. You can download the Cumulative Update package 1 for SQL Server 2014
at here:
https://support.microsoft.com/en-us/kb/2931693

5. The execution of an SSIS package that is stored in MSDB is slower than it is stored
on the file system.

This is SQL Server 2014 bug. This bug was first fixed in Cumulative Update package 2 for
SQL Server 2014. You can download the Cumulative Update package 2 for SQL Server 2014
at here:
https://support.microsoft.com/en-us/kb/2967546

6. SSIS 2014 XML validator only outputs “TRUE” or “FALSE” when you perform XML validation.

This bug was first fixed in Cumulative Update package 2 for SQL Server 2014. You can download
the Cumulative Update package 2 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2967546
After you apply the Cumulative Update package 2 for SQL Server 2014, a new task property
ValidationDetails appears. To obtain the detailed validation output in XML format, you have
to change the ValidationDetails value to true.

7. The error occurs when you add a custom connection manager in SSIS 2014 Designer.

This is the error message text: “The new connection manager could not be created.”
This bug was fixed in SQL Server 2014 service pack 1. To resolve this problem, install
the latest SQL Server service pack.

8. The error occurs when you preview a command of an SSIS package in SQL Server
Data Tools (SSDT).

This is the error message text: “Query Timeout Expired.” The problem occurs because the
CommandTimeout threshold is hard coded as 30 seconds for the preview feature in SSIS
designer. This bug was first fixed in Cumulative Update package 3 for SQL Server 2014.
You can download the Cumulative Update package 3 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2984923

9. The error occurs when you try to execute a SQL task in which the IsQueryStoredProcedure
attribute is set to True and you use .NET Data Provider for Teradata in SSIS.

This is SQL Server 2014 bug. This bug was first fixed in Cumulative Update package 4
for SQL Server 2014. You can download the Cumulative Update package 4 for SQL Server 2014
at here:
https://support.microsoft.com/en-us/kb/2999197

10. The package fails intermittentlym when the package is scheduled to run one time
every minute by using SQL Server Agent job.

In this case, you receive the following error message: “Unexpected Termination”. This bug
was first fixed in Cumulative Update package 4 for SQL Server 2014. You can download the
Cumulative Update package 4 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2999197

11. The error occurs when you use Office 15.0 Access Database Engine OLE DB Provider
as data source in SSMS 2014.

This is the error message text: “Could not connect source component.” This bug was first
fixed in Cumulative Update package 5 for SQL Server 2014. You can download the Cumulative
Update package 5 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3011055

12. Upgrade wizard appears again when you reopen the SSIS 2014 solution with
upgraded SSIS packages.

This problem occurs when the name of the upgraded packages contains dots (“.”). This bug
was first fixed in Cumulative Update package 5 for SQL Server 2014. You can download the
Cumulative Update package 5 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3011055
To work around this problem, avoid using dots in the package name.

13. The error occurs when you load an XML document that has time zone information.

This is the error message text: “Specified cast is not valid”. This bug was first fixed
in Cumulative Update package 6 for SQL Server 2014. You can download the Cumulative
Update package 6 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3031047

14. The error occurs when you run an XML Validation Control Flow task on a large
input file in SSIS 2014.

This is the error message text: “Insufficient memory to continue the execution of the
program.” This bug was first fixed in Cumulative Update package 6 for SQL Server 2014.
You can download the Cumulative Update package 6 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3031047

15. The execution of the package hangs when you run an SSIS package that connects
to Flat File Source.

This problem occurs when one of the rows has less delimiters than the total number
of columns. This bug was first fixed in Cumulative Update package 7 for SQL Server 2014.
You can download the Cumulative Update package 7 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3046038
To work around this problem, you can set the AlwaysCheckForRowDelimiters property of
Flat File connection manager to false.

16. When you deploy a project by using the CatalogFolder.DeployProject method,
the publication takes longer time than expected to be complete.

This bug was first fixed in Cumulative Update package 8 for SQL Server 2014. You can
download the Cumulative Update package 8 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3067836
To work around this problem, you can use the Transact-SQL stored procedure
catalog.deploy_project when you publish projects.