SQL Server

Troubleshooting SQL Server 2016 Integration Services

Troubleshooting SQL Server 2016 Integration Services

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

1. Install the latest SQL Server 2016 service pack.

Because many SQL Server 2016 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 2016 service pack was
service pack 1. You can download the SQL Server 2016 service pack 1 at here:
https://support.microsoft.com/en-us/kb/3182545

2. 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 delimiter than the total number
of columns. To work around this problem, you can set the AlwaysCheckForRowDelimiters
property of Flat File connection manager to false.

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

This is SQL Server 2016 bug. To work around this problem, you can use the
Transact-SQL stored procedure catalog.deploy_project when you publish projects.

4. 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. To work around this
problem, use a user-defined database role in SQL Server 2016 to encapsulate
the necessary list of Windows users, and then assign the permissions to that
database role.

5. Upgrade wizard appears again when you reopen the SSIS 2016 solution with
upgraded SSIS packages.

This problem occurs when the name of the upgraded packages contains dots (“.”).
To work around this problem, avoid using dots in the package name.

6. You may receive validation errors when you add a valid derived column
expression in a SSIS 2016 package.

This problem also occurs after you migrates an SSIS 2008 package to an SSIS 2012
package or later versions. This bug was fixed in Cumulative Update package 1 for
SQL Server 2016 service pack 1. You can download the Cumulative Update package 1
for SQL Server 2016 service pack 1 at here:
https://support.microsoft.com/en-us/help/3208177/cumulative-update-1-for-sql-server-2016-sp1

7. SSIS 2016 package doesn’t run when you call it from executable files outside
the %Program Files%Microsoft SQL Server130DTSBinn folder.

This is SQL Server 2016 bug. This bug was fixed in Cumulative update package 1
for SQL Server 2016. You can download the Cumulative Update package 1 for
SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/3164674/cumulative-update-1-for-sql-server-2016

8. The error 7330 occurs when you query the view that is created by the
Data Feed Publishing Wizard in SQL Server 2016.

This is the error message text: “Cannot fetch a row from OLE DB provider
“SSISOLEDB” for linked server “Default Linked Server for Integration Services”.
This bug was fixed in Cumulative update package 1 for SQL Server 2016. You can
download the Cumulative Update package 1 for SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/3164674/cumulative-update-1-for-sql-server-2016

9. You can’t select the “Save SSIS Package” option when you export a database
by using SQL Server Import and Export Wizard.

This problem occurs when you use the stand-alone version of SQL Server 2016
Management Studio (SSMS 2016), and there is no instance of SQL Server installed.
This bug was fixed in Cumulative update package 1 for SQL Server 2016. You can
download the Cumulative Update package 1 for SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/3164674/cumulative-update-1-for-sql-server-2016

10. When you set the value of the AutoAdjustBufferSize property to True, the
buffer size is not set correctly when the data flow engine handles blob data.

This problem occurs when you create a SSIS package by using SQL Server Data Tools
in Visual Studio 2015. In this case, the package may run more slowly than expected.
This bug was fixed in Cumulative update package 1 for SQL Server 2016. You can
download the Cumulative Update package 1 for SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/3164674/cumulative-update-1-for-sql-server-2016

11. The error occurs when you execute SSIS package on a Windows with Federal
Information Processing Standard (FIPS) enabled.

This is the error message text: “System.InvalidOperationException: This implementation
is not part of the Windows Platform FIPS validated cryptographic algorithms. at
System.Security.Cryptography.MD5 CryptoserviceProvider..ctor()”. This bug was fixed in
SQL Server 2016 service pack 1. To work around this problem, you can set the following
registry subkey to 0:
HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlLsafipsalgorithmpolicy
To resolve this problem, install the latest SQL Server service pack.

12. The error occurs when you open the “Configure SSIS Logs” window.

This is the error message text: “Log provider “Microsoft.LogProviderSQLServer” is
not installed correctly on this computer.” This problem occurs when you use SSIS
SQL Server Log Provider for logging. This bug was fixed in SQL Server 2016 service
pack 1. To resolve this problem, install the latest SQL Server service pack.

13. An access violation occurs when you execute two parent packages that run the
same child package in parallel.

This problem occurs when the child package contains a connection string that is
specified by an expression. This bug was fixed in Cumulative update package 2 for
SQL Server 2016.
You can download the Cumulative Update package 2 for SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/3182270/cumulative-update-2-for-sql-server-2016

14. The error occurs when you execute an SSIS package that contains an XML task.

This is the error message text: “The process cannot access the file file_path
because it is being used by another user.” This bug was fixed in Cumulative
update package 4 for SQL Server 2016. You can download the Cumulative Update
package 4 for SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/3205052/cumulative-update-4-for-sql-server-2016

Facebooktwittergoogle_plusredditpinterestlinkedinmail