SSIS -- Failed to deploy project. Error 27203

user_12432699 0
Viewed 5472

I built a very simple SSIS package in Visual Studio 2019. This is for an online training class so there's nothing complex about it -- just copies a file from one location to another. The training class then moves on to deploying the package on the SQL server. The SQL server I'm using (Version 15.0.2000.5) is on my laptop -- I'm not trying to connect to any kind of production server or anything even outside of my machine. EDIT: SELECT @@Version generates the following: >Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 >Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows >10 Enterprise 10.0 (Build 18362: )

In any case, the deployment wizard gets to the last steps of deploying, then it fails. Loading project -- passed, Connection to destination server -- passed, Changing protection level -- passed, Deploying project -- fail. The error message is: Failed to deploy project. For more information, query the operation_messages view for the operation identifier '8'. (Microsoft SQL Server, Error: 27203)

My research into this issue so far had me run a query to look at the operation_messages.

I ran select * from SSISDB.catalog.operation_messages There were no results. Further research into why there were no results suggested that there might be an error with permissions. I played around with that for a while but I still wasn't able to deploy the package successfully (I'm not sure how to check if permissions are set correctly, but I tried). Besides, wouldn't permissions be fine since everything is happening on my single machine?

Anyway, I also researched error 27203 but the responses didn't seem to match my situation because either 1) they were too old and used older versions of both Visual Studio and SQL Server, or 2) involved connecting in to SQL server (again, my server is on my machine).

So I'm not sure what else to check next. I can't seem to get to the operation_messages to uncover a better description of what went wrong. And I can't get any info about error 27203 that seems to match closely to my situation.

Deploying directly from SSMS has the same result.

If this helps, here's a screenshot from the error dialog. The XML code for the package I'm working on only goes to line 85. No line 139 in the XML code.

Picture of error info

And a screenshot of the About screen from SSMS

About Screen

Ultimately, since this is just for training, is deploying a package to the server even necessary? Deploying packages seems necessary if you want to schedule the package to run regularly. But I can create SSIS packages that write data to the server without any problem. There's no issue if I just execute the packages from Visual Studio once I'm done creating them and if I'm never actually going to schedule a package to run, maybe I can skip this part about deploying packages.

Thanks in advance for any direction.

PS -- If the solution for the problem involves a setting change somewhere, I'll go with that. At this point in my learning, I'd rather not deploy using a workaround like via the command line, etc.

ssis deployment sql-server
4 Answers

I was having this error (and pulling my hair out) -- even though I didn't have any rows in the operation_message view, there was a row in [catalog].[operations]. I noticed server_name was the old name of the machine. For the most recent successful deployment in [catalog].[operations], the server_name was the current name of the machine.

I also noticed both SELECT @@SERVERNAME and SELECT SERVERPROPERTY('SERVERNAME') were both returning the machine's old name. (Usually @@SERVERNAME would have the stale name but SERVERPROPERTY('SERVERNAME') would have the current name).

So I ran

exec sp_dropserver 'old_name';
GO
exec sp_addserver 'current_name';
GO

and restarted the SQL service, and then the deploy worked.

user_1558920

This started happening to me when I changed the computer name of a MSSQL Server in EC2. It had still recorded the servername as the original computer name and not the new name. I solved this problem with the following command:

EXEC sp_dropserver 'oldservername';  
GO  
EXEC sp_addserver 'newservername', local;  
GO
user_166074610

Check if the SQL Server Integration Services service is running on the server your connecting to. Since you have no results from SSISDB.catalog.operation_message I suspect the service is stopped.

Start > Run > services.msc.

Depending on your version of SQL Server look for the SSIS service and check if its running or not.

SQL Server        Service Name
SQL 2008          MsDtsServer100
SQL 2012          MsDtsServer110
SQL 2014          MsDtsServer120
SQL 2016          MsDtsServer130
SQL 2017          MsDtsServer140
SQL 2019          MsDtsServer150

Also; if your Package uses Parameters go to the Parameters tab and make sure a default value is set for each parameter.

user_23443940

run sql browser service it will work,beacuse ssis dosen't understand any portNo except 1432

user_177150820
Related