(Unresolved & Repost) SSIS package deployment failure after SQL server default TCP port change + original package execution pending

Question

(Unresolved & Repost) SSIS package deployment failure after SQL server default TCP port change + original package execution pending

TC 81

Hi all,

I have a case that SQL server is running remotely and recently the SQL server TCP port is changed from 1433 to 61433. After service reboot, I can connect to the server by (server name, port number) but cannot deploy packages from VS2019 to the server remotely. I tried to query with operation_id provided but I couldn't find the id specified. Also, when I try to run old SSIS package deployed on SQL server, it will show 'pending execution' and the execution never stops.
Have I missed any steps of SSIS package or SSISDB setting after port change? Thanks for help.

Symptom:

Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2022-12-28T22:32:29.437+00:00

Have you followed the instructions in the error message? That is, query the, operation_messages view for the operation identifier 437?

I don't work with SSIS myself, but I would guess this view is in the SSISDB database.
TC 81 Reputation points

2022-12-29T01:29:48.74+00:00

Hi,

I tried to query the operation_messages but no result returned. I could only find the related id in operation table. I think some process went wrong in package deployment (which is wrote by stored procedure).

1 answer

Your answer

Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2022-12-28T22:32:29.437+00:00

Have you followed the instructions in the error message? That is, query the, operation_messages view for the operation identifier 437?

I don't work with SSIS myself, but I would guess this view is in the SSISDB database.
TC 81 Reputation points

2022-12-29T01:29:48.74+00:00

Hi,

I tried to query the operation_messages but no result returned. I could only find the related id in operation table. I think some process went wrong in package deployment (which is wrote by stored procedure).

Answer 1

Ronen Ariely 15,206

Hi,

I have a case that SQL server is running remotely and recently the SQL server TCP port is changed from 1433 to 61433.

Don't confuse between the SQL Server connection port and the SSIS remote port.

The Integration Services service uses port 135, and the port cannot be changed. You have to open TCP port 135 for access to the service control manager (SCM).

https://learn.microsoft.com/sql/integration-services/service/integration-services-service-ssis-service?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699#configure-the-firewall

https://learn.microsoft.com/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699#ports-used-by-integration-services

Back to the issue:

What version are you using?

There was issue with this error in 2016 which was fixed, but it is not related to any post changing

https://support.microsoft.com/en-us/topic/kb4056831-fix-error-27231-occurs-when-ssis-package-that-contains-localized-language-is-deployed-in-sql-server-2016-aa1e1b3f-a2a7-e157-8b5d-216690dde2fe

I recommend first to confirm that the issue is related to the port changing.

Try first to change only the port back and confirm that this is the source of the issue or maybe something else was changed asd well and you are focusing on the wrong parameter

TC 81 Reputation points

2022-12-29T03:03:44.937+00:00

Hi,

Thanks for clarifing. My SQL server version is: 2019.150.4261.1

I tried to switch back to dafault port number (1433) and everything went back to normal. so I think it's related to port change.
Ronen Ariely 15,206 Reputation points

2022-12-29T13:03:50.597+00:00

Hi,

Make sure that you configure the port in the connection string using the SSIS connection manager

I am not sure why you see this error and not a different one, but let's confirm the connection string uses the right port - the new port of the SQL Server

These doc/post should help you for the task:

https://learn.microsoft.com/sql/integration-services/connection-manager/integration-services-ssis-connections?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699#create-connection-managers

https://www.mssqltips.com/sqlservertutorial/209/sql-server-integration-services-ssis-connection-managers/
Yitzhak Khabinsky 26,586 Reputation points

2022-12-29T13:35:22.93+00:00

Integration Services OS service is kept just for backward compatibility with SSIS 2005 - 2008R2.
It is not needed starting from SSIS 2012 onwards. Modern SSIS is using SSISDB and Intergration Services Catalog already for a decade.

This service is not required to create, save, and run Integration Services packages. SQL Server 2012 (11.x) supports the Integration Services service for backward compatibility with earlier releases of Integration Services.

integration-services-service-ssis-service
Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2022-12-29T21:52:55.643+00:00

I tried to switch back to dafault port number (1433) and everything went back to normal. so I think it's related to port change.

Did you switch back to 1433 in SQL Server or only in the package?
TC 81 Reputation points

2022-12-30T01:33:56.783+00:00

Hi,

I am not sure if deployment failure is related to the package content (e.g. connection manager). Assume that I have a package with no content, I should still be able to deploy it with VS or SSMS.
TC 81 Reputation points

2022-12-30T01:37:10.613+00:00

Hi,

I switch the port in SQL Server Configuration Manager.
Ronen Ariely 15,206 Reputation points

2022-12-30T14:39:10.64+00:00

Hi YitzhakK :-)

Happy New year

This link is the exact link which I gave the OP in my first response
Ronen Ariely 15,206 Reputation points

2022-12-30T14:44:53.937+00:00

How do you think the connection is done if not with a "connection string"?!?

If you did not configured the connection to use the new port then it surly sound like an issue.

I am guessing that the message source is the SSIS (as mentioned I am not familiar with this specific error except the scenario I mentioned), but if you changed the port to connect the server then you must connect in the new port and your system cannot guess that you have a new port.

I recommend to try to focus on this part - make sure that you changed the connection to use the new port in the SSIS side. According to your last information and my understanding that you did not done it, it sound to me like the potential source of the issue.

Share via

(Unresolved & Repost) SSIS package deployment failure after SQL server default TCP port change + original package execution pending

1 answer

Back to the issue:

Your answer