Import / Deployment / Execution of SSIS packages fails after you migrate SSISDB from SQL Server 2012 to SQL Server 2014.
In this blog, I would like to discuss about one of the interesting scenarios that you would witness after you migrate your existing SSISDB catalog from SQL Server 2012 to SQL Server 2014. Issue: Whenever you try to deploy your new Integration services projects to the migrated SSISDB catalog (of SQL Server 2014) using Microsoft Visual Studio – SQL Server Data tools [SSDT] or when you try to import your old SSIS packages to your newly migrated SSISDB (of SQL Server 2014) directly from the SSISDB catalog (of SQL Server 2012) using the SQL Server Management Studio [SSMS] or Execute your already migrated SSIS packages on the newly migrated SSISDB on the SQL Server 2014, you may experience the following error message,
ERROR MESSAGE:
The required components for the 64-bit edition of Integration Services cannot be found. Run SQL Server Setup to install the required components.
A .NET Framework error occurred during execution of user-defined routine or aggregate "start_execution_internal":
System.Data.SqlClient.SqlException: The required components for the 64-bit edition of Integration Services cannot be found. Run SQL Server Setup to install the required components.
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteToPipe(SmiContext pipeContext)
at Microsoft.SqlServer.Server.SqlPipe.ExecuteAndSend(SqlCommand command)
at Microsoft.SqlServer.IntegrationServices.Server.ServerConnectionControl.RaiseError(SysMessageId messageId, SysMessageSeverity severity, Object[] args)
at Microsoft.SqlServer.IntegrationServices.Server.ServerApi.GetDtsPath(Int16 use32bitOn64, String& path)
at Microsoft.SqlServer.IntegrationServices.Server.ServerApi.GetISServerExecPath(Int16 use32bitOn64, String& path)
at Microsoft.SqlServer.IntegrationServices.Server.ServerApi.StartExecutionInternal(SqlInt64 projectId, SqlInt64 executionId, SqlInt64 versionId, SqlInt16 use32BitRuntime)
. (Microsoft SQL Server, Error: 27222)
Fig 1. Error message thrown when executing the SSIS packages on migrated SSISDB of SQL Server 2014.
CAUSE:
You may have taken a backup, restored and moved the SSISDB from your existing SQL Server 2012 SSISDB to your new SQL Server 2014 SSISDB following the below article, ( either using automated T-SQL Scripts or running through the screens of SSMS following the article)
Backup, Restore, and Move the SSIS Catalog: https://msdn.microsoft.com/en-us/library/hh213291.aspx
But you may have missed out or not implemented the action item #8 under Restore the SSIS Database, wiz.
“8. Determine whether the SSISDB catalog schema and the Integration Services binaries (ISServerExec and SQLCLR assembly) are compatible, by running catalog.check_schema_version .”
This stored procedure actually checks to see if your restored SSISDB catalog schema and Integration services binaries (IsServerExec and SQLCLR assembly) are compatible.
When we actually run this stored procedure, you may notice that it fails with the exact same error message as above,
USE [SSISDB]
GO
DECLARE @return_value int
EXEC @return_value = [catalog].[check_schema_version]
@use32bitruntime = 0
SELECT 'Return Value' = @return_value
GO
Fig 2. Same error message thrown when running the above stored procedure on the newly migrated SSISDB of SQL Server 2014
In continuation to this, if you look into the properties of the restored SSISDB on your SQL Server 2014, the schema build still points to the SSISDB 2012 rather than SSISDB 2014.
Fig 3. Right-Click the SSISDB under the Integration Services Catalogs and go to Properties check the Schema version & Schema Build.
On your SSISDB properties window:
Schema Version: 2
Schema Build: 11.0.XXXX.0 -> this points to SQL Server 2012 SP XXX (SSISDB version of source SQL Server)
Ideally, it should be pointing to SQL Server 2014, wiz. 12.0.0 XXXX -> pointing to SQL Server 2014 version.
Fig 4. Ideal Catalog Properties window with the Schema Version & Build pointing to SQL Server 2014.
Thus there is a mismatch on the versions of SSISDB catalog schema (set to SQL Server 2012 still) and Integration services binaries (IsServerExec and SQLCLR assembly) (set to SQL Server 2014) because of which any activities on your newly migrated SSISDB fails.
This will ideally make your entire SSISDB unusable i.e. If you try deploying SSIS project from SSDT/SSMS or import SSIS packages or execute any package inside SSISDB would fail with the same error message.
RESOLUTION/ WORKAROUND:
If you are using the MSDN article as it is or scripts to automate the backup, restore and movement of the SSISDB from SQL Server 2012 to SQL Server 2014 based on this article, then it will fail in the #8 action item and this at present seems to be a known behavior.
The article or automated scripts for migrating the SSISDB will work flawlessly when you are moving the SSISDB between the same versions of the SQL Server (SQL 2012 to SQL 2012 / SQL 2014 to SQL 2014). But not across different versions of SQL Server.
At present, there is no possible method / recommended way in which the Schema of the SSISDB can be changed directly from SQL Server 2012 to SQL Server 2014. (Dated to the time the blog is written)
This may be available in future releases of the product though. At present, the only possible workaround available is as below,
Workaround:
1. Backup the existing SSISDB on your SQL Server 2014 (Optional)
2. Delete the existing SSISDB on your SQL Server 2014
3. Recreate a new catalog from scratch. Check the properties of the SSISDB and confirm that the Schema build points to SQL Server 2014. (Check NOTE, if you run into any issues with creating a new catalog)
4. Redeploy the SSIS Projects from the SQL Server 2012 environment or import the packages as required.
5. Check the working of the SSIS packages now.
NOTE:
If “Create Catalog…” fails with the following error message,
TITLE: Microsoft SQL Server Management Studio
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Management.IntegrationServices)
------------------------------
ADDITIONAL INFORMATION:
The server principal '##MS_SQLEnableSystemAssemblyLoadingUser##' already exists.
Cannot find the login '##MS_SQLEnableSystemAssemblyLoadingUser##', because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15025)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.2000&EvtSrc=MSSQLServer&EvtID=15025&LinkId=20476
------------------------------
Go to Security -> Login -> check for any login with the name “MS_SQLEnableSystemAssemblyLoadingUser” and delete this login and the recreate the catalog again.
Referenced Articles:
1. Backup, Restore, and Move the SSIS Catalog: https://msdn.microsoft.com/en-us/library/hh213291.aspx
2. catalog.check_schema_version : https://msdn.microsoft.com/en-us/library/hh479596.aspx
Please drop in your comments or connect with Microsoft BI-ONE CSS team if you are still encountering the same issue even after performing the above steps.
Author: Krishnakumar Rukmangathan, Technical Advisor, SQL Server BI-ONE Developer team, Microsoft
Reviewed by: Sunil Kumar B.S, Escalation Engineer, SQL Server BI-ONE Developer team, Microsoft.
Comments
- Anonymous
July 06, 2016
Good Afternoon.Need to verify if a solution or workaround has been found other than to start with a fresh 2014 SSIS database and redeploy all of the user SSIS packages. Thank you for your time,Brian Hernandez