SSIS Error "To run a SSIS package outside of SQL Server Data Tools you must install Standard Edition (64-bit) of Integration Services "

3
votes

I am using Visual studio ultimate 2012, SQL Server 2017 and Sql Server Data tools 2012. I am working with windows 10 64 bit.

My package consists of script tasks(c#) and Sql commands and it runs successfully from SSDT, but while trying to run my package from CMD i am getting

To run a SSIS package outside of SQL Server Data Tools you must install Standard Edition (64-bit) of Integration Services.

If i make another empty package and try to run it then it runs successfully too. Please help.

3
sql-servercmdssissql-server-data-toolsssis-2012
You need Standard Edition of SQL for execute some components in DTSX through CMD like Script Task, DB connections that aren't SQL, SMTP Task, etc... – Santiago
@Priyanshu does the links i provided solves the issue?? – Hadi

3 Answers

1
votes

(1) Integration Services (Shared feature) installation

I think that the issue is that you have installed SQL Server Data Tools (SSDT) for Visual Studio but you didn't install the Integration Services (Shared feature) from the SQL Server Installation which is necessary to execute .dtsx packages outside of visual studio.

For more information check the following link:

(2) Execute using dtexec(32-bit)

Another thing you can try is to execute package via 32-bit dtexec.

As mentioned in the following Microsoft Documentation:

On a 64-bit computer, Integration Services installs a 64-bit version of the dtexec utility (dtexec.exe). If you have to run certain packages in 32-bit mode, you will have to install the 32-bit version of the dtexec utility. To install the 32-bit version of the dtexec utility, you must select either Client Tools or Business Intelligence Development Studio during setup.

By default, a 64-bit computer that has both the 64-bit and 32-bit versions of an Integration Services command prompt utility installed will run the 32-bit version at the command prompt. The 32-bit version runs because the directory path for the 32-bit version appears in the PATH environment variable before the directory path for the 64-bit version. (Typically, the 32-bit directory path is :\Program Files(x86)\Microsoft SQL Server\100\DTS\Binn, while the 64-bit directory path is :\Program Files\Microsoft SQL Server\100\DTS\Binn.)

More info at:

Also, similar issues was mentioned in other links, you can check them for more information:

1
votes

Adding to Hadi's answer, the change would be to use the right version which in my case was to use C:\Program Files\Microsoft SQL Server\130\DTS\Binn> Instead of C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn>

0
votes

I had the same error and my integration services of 2017 was installed, so the suggested solution was not relevant.

i tried many options including uninstall and re-install, what solved finally was an upgrade to ENTERPRISE EDITION!

I found out that in a server where the dtexec ran successfully an ENTERPRISE VERSION was installed, while in the problematic server there was not an ENTERPRISE edition. How did i find it? i ran the following in both servers:

  1. run setup.exe file of SQL SERVER 2017 installation
  2. on the left menu you will see "tools" - press the link
  3. find: "installed SQL server features discovery report"
  4. you will see in edition column an empty cell vs. "Enterprise Edition" which is the successful one!

how did i upgrade in the problematic server?

  1. run setup.exe file of SQL SERVER 2017 installation
  2. on the left menu you will see "maintenance" - press the link
  3. find: "edition upgrade"
  4. follow the instructions, in my case since my organization had an automatic updated key i just pressed "next" a few times until a successful upgrade

good luck!