2

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?

Improve this question
10
  • On both machines, open a command prompt and look for isdeploymentwizard.exe C:\>dir /s /b isdeploymentwizard.exe and also check where isdeploymentwizard.exe My suspicion is that the the deployment tool - isdeploymentwizard.exe is upgrading the package prior to deployment on the local scenario.
    – billinkc
    Commented Nov 14, 2019 at 16:23
  • @billinkc Looks like a difference for sure. I notice two things. On the local PC there is only a 32-bit version. On the server there are both 32 and 64 bit. if I compare the 32-bit versions I see that they are different. The PC is 14.0.3026.27 and the server is 14.0.3238.1. I'll continue to look in to this, but open to ideas.
    – Appleoddity
    Commented Nov 14, 2019 at 16:45
  • 32 vs 64 deployment shouldn't be an issue, nor minor point differences. I'd be looking for something like (AWS box has it in 150, remote computer has 130,140, & 150 - in that order)
    – billinkc
    Commented Nov 14, 2019 at 17:10
  • And so why this major version stuff matters - SSIS is always forwards compatible. I can take a 2005 SSIS package and if the 2019 tooling (editor, deployment, or package runner) uses that package, the first thing it does is upgrade to that version's standard and then performs edit/deploy/run task. For deployments, it was hi-larious! when we'd unwittingly use newer tooling as the ispac (zip) file is going to get stored into the database just fine. It's only when the package runs, that the tooling will notice this is incompatible with current
    – billinkc
    Commented Nov 14, 2019 at 17:14
  • 2016+ addressed backwards compatibility but in a hamhanded way. The Editor can change the target of the project but running packages is still done by having a dtexec.exe per version of SQL Server (as it's tightly bound to SQL Server libraries)
    – billinkc
    Commented Nov 14, 2019 at 17:16

1 Answer 1

Reset to default
2

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.