0

We are moving an existing system from a SQL Server 2005 system. A component of that system was an SSIS project created with VS2005.

We installed SQL Server 2016 STANDARD EDITION in January. The database data files are on the E: drive.

We installed Visual Studio 2017 in January but didn't use it at that time.

We recently installed Visual Studio 2019 on the db server due to problems with file access when running VS2019 locally and using a VPN to access the network.

We just installed SSIS from the SQL Server Setup, realizing we were missing that component.

As a result of all these installations, dtinstall.exe & dtexec.exe are in the following folders:

C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\Common7\IDE\CommonExtensions\Microsoft\SSIS\140\Binn

C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\Common7\IDE\CommonExtensions\Microsoft\SSIS\150\Binn

E:\Program Files\Microsoft SQL Server\130\DTS\Binn

E:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn

E:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn

E:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn

E:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\CommonExtensions\Microsoft\SSIS\150\Binn

The dtexecui.exe is found ONLY in the following Folder:

E:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\CommonExtensions\Microsoft\SSIS\150\Binn

The two Visual Studio 2019 packages were developed in the C: drive on the server. They compile and execute correctly from within VS.

Each package has 3 Configuration Files (for test, preview and production scenarios).

Configured the 2 Packages' TargetServerVersion to "SQL Server 2016", due to a compatibility issue discovered upon installing the Deployment Utility.

Created the Deployment Utility using the Legacy Package Deployment Model.

Copied the Deployment Utility files from the C:...\Deployment folder to a folder on the F: drive on the server.

Ran dtinstall.exe to install the packages on the server. It created the files on the same drive where the database data files are:

E:\Program Files (x86)\Microsoft SQL Server\130\DTS\Packages\Web Site Synch SSIS Project.

Executed the packages using dtexecui.exe, which as mentioned above, is ONLY found in the \150\Binn folder.

Here is the DTexec.exe command line that is generated by dtexecui:

/FILE "\"E:\Program Files (x86)\Microsoft SQL Server\130\DTS\Packages\Web Site Synch SSIS Project\Products Upload.dtsx\"" /DECRYPT /CONFIGFILE "\"E:\Program Files (x86)\Microsoft SQL Server\130\DTS\Packages\Web Site Synch SSIS Project\Test Products.dtsConfig\"" /CHECKPOINTING OFF /REPORTING EW

Upon execution (from within dtexecui), it gives an error in the final step, which executes WZZIP.exe to zip the Flat Files that were created in the preceding steps.

dtexecui error in WinZip step

The error message says: "Error: To run a SSIS Package outside SQL Server Data Tools you must install Standard Edition of Integration Services or higher."

I didn't really understand this message: We do have SQL Server Standard Edition. I assume the SQL Server Setup for this would install the Standard Edition of Integration Services.

I researched this error and found that it was often due to an incompatibility between the package version and the tool version, but I was at a loss as to how to resolve this in my scenario. The dtexecui error message gave no hints.

LindaK
  • 19
  • 1
  • 3
  • Hi LindaK, and welcome to SO! At StackOverflow it's perfectly acceptable to post a question and then also submit and accept your own answer. I would encourage you to modify your "question" post above to just contain info on the actual question. Then below you should see a "Your Answer" submission form. Put all the info related to the solution there and hit "Post Your Answer". Then after you post it, you can select "Accept this answer". This will show other people on StackOverflow that your problem has a solution already. It will also be more beneficial for future visitors. – digital.aaron Apr 29 '20 at 19:47
  • Thank you digital.aaron. I have done as you encouraged. – LindaK Apr 30 '20 at 16:55

1 Answers1

1

In the course of carefully documenting my issue for posting it here, I realized the problem might be that since I was using the \150\ version of dtexecui.exe, it is probably running the \150\ version of dtexec.exe, and that is the incompatibility.

I copied the command string generated by dtexecui and then ran it using the \130\ version of dtexec. It ran without error.

Here is the command prompt session:

E:>cd E:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn

E:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn>dtexec /FILE "\"E:\Program Files (x86)\Microsoft SQL Server\130\DTS\Packages\Web Site Synch SSIS Project\Products Upload.dtsx\"" /DECRYPT /CONFIGFILE "\"E:\Program Files (x86)\Microsoft SQL Server\130\DTS\Packages\Web Site Synch SSIS Project\Test Products.dtsConfig\"" /CHECKPOINTING OFF /REPORTING EW

Microsoft (R) SQL Server Execute Package Utility

Version 13.0.5026.0 for 32-bit

Copyright (C) 2016 Microsoft. All rights reserved.

Enter decryption password:

Started: 2:44:57 PM

Warning: 2020-04-29 14:45:01.31

Code: 0x80049304

Source: Unit Type SSIS.Pipeline

Description: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console.

End Warning

DTExec: The package execution returned DTSER_SUCCESS (0).

Started: 2:44:57 PM

Finished: 2:45:06 PM

Elapsed: 9.344 seconds

E:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn>

Now I know that in my calling routines (stored procedures and the other application), I need to call this specific version of dtexec.exe.

I really hope this helps someone!

LindaK
  • 19
  • 1
  • 3