Sql-server – Imported SSIS package fails if imported with SSMS on the SQL server, but works when imported from a remote PC

sql serverssisssms

I am facing a strange issue on SQL Server 2017 Enterprise edition running in AWS on EC2.

When we are on the SQL server and we import an SSIS package using the locally installed SSMS connected to Integration Services it is successful. But, the package fails to run with an error: 

The binary code for the script is not found. 
Please open the script in the designer by clicking Edit Script button and make sure it builds successfully.

However, if we perform the exact same procedure from a remotely connected Windows 10 PC using the SSMS that is installed on the PC, the package will run properly.

Both the server and the Windows workstation are running SSMS 17.9.1. Comparing versions in Help -> About shows the exact same version numbers on everything. The same domain user is performing the procedure and that user is a domain admin and SQL Sysadmin.

This makes no sense. Is there another component being used I am unaware of? Any ideas?

Best Answer

Here is what I found in regards to this issue.

First, I exported the broken package from the server after importing it. I also exported the working package from the server after importing it. I compared the two files side by side and quickly discovered that the script within our package where the errors occurred was missing it's binary data in the BinaryItem element. I was also able to look directly at the package data on the server using the query:

SELECT id
    ,NAME
    ,CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) PackageXML
    ,description
FROM [msdb].[dbo].[sysssispackages]

The missing binary code indicated to me that the script was not being properly compiled during import.

After wasting a lot of time trying to find out why, I realized that the script was fairly simple so I should take a look in the script to see what was in there that might cause the import to fail.

I discovered a reference to Microsoft.Office.Interop.Excel which is part of the Microsoft Office Primary Interop Assemblies installed with Microsoft Office. On the SQL server, Microsoft Office is not installed. As a result, the .DLL file related to this reference is missing on the SQL server.

Removing the faulty, and unneeded reference solved the issue. I was able to confirm with Process Monitor that the server was looking for this .DLL file during import and not able to find it.

Apparently, if this happens during import there is no indication of the failure. Long story short, the error message was accurate. The binary code was missing. That was a result of a failure to compile the source code during import.

Related Question