3

I am trying to run the SSIS package from a batch file,but it's throwing an error

To run a SSIS package outside of SQL Server Data Tools you must install Standard Edition of Integration Services or higher.

But in Visual studio i can able to execute successfully. My code in batch File

set DtexecFileLoc=C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\Dtexec.exe

::@echo Batch File Loc: %BatFileLoc%
@echo SSIS Package Location: %SSISPkgLoc%
echo.

@echo Please enter the variable values for which you want to refresh DEV environment.
set /p EffDate=Enter the Month-End EffectiveDate in MM/DD/YYYY format:%=%


@echo SSIS Package is going to run for EffectiveDate '%EffDate%' . 
@echo If these variable values are not correct then close the window and re-run the batch file again.
@echo DO NOT CLOSE this window while the SSIS Package is running.
echo. 
pause 

"%DtexecFileLoc%" /File "%SSISPkgLoc%" /SET \Package.Variables[User::z_Dest_ServerName].Properties[Value];"%DestServer%" /SET \Package.Variables[User::Z_Dest_UserName].Properties[Value];"%DestUserName%" /SET \Package.Variables[User::Z_Dest_Password].Properties[Value];"%DestPassword%" /SET \Package.Variables[User::z_Email_Server].Properties[Value];"%EmailServer%" /SET \Package.Variables[User::z_Source_ServerName].Properties[Value];"%SourceServer%" /SET \Package.Variables[User::Z_Source_Username].Properties[Value];"%SourceUserName%" /SET \Package.Variables[User::Z_Source_Password].Properties[Value];"%SourcePassword%" /SET \Package.Variables[User::z_Email_Recipient].Properties[Value];"%EmailRecipient%" /SET \Package.Variables[User::y_EffectiveDate].Properties[Value];"%EffDate%"  > "%SSISLogLoc%"


@echo SSIS Package execution is now complete, you can now close this window.
echo. 
pause

Here is my environment configuration

enter image description here

I have tried switching different paths for executable, but it's not working in my case.and here is my log here is the log file below error message below

Microsoft (R) SQL Server Execute Package Utility Version 14.0.3026.27 for 32-bit Copyright (C) 2017 Microsoft. All rights reserved.

Error: 2019-12-11 15:47:30.29 Code: 0xC000F427 Source: Update ErrorList Description: To run a SSIS package outside of SQL Server Data Tools you must install Standard Edition of Integration Services or higher. End Error

Hadi
  • 36,233
  • 13
  • 65
  • 124
srikanth
  • 41
  • 1
  • 6
  • Does this answer your question? [To run a SSIS package outside of SQL Server Data Tools you must install Move File to Archive of Integration Services or higher](https://stackoverflow.com/questions/35469249/to-run-a-ssis-package-outside-of-sql-server-data-tools-you-must-install-move-fil) – Joe C Dec 11 '19 at 21:07
  • Nope.i have tried the solution but it doesn't works for me.i am thinking may be it's version difference between SSDT and Sql server integration services but i am not sure how to resolve. – srikanth Dec 11 '19 at 21:13
  • 1
    Do you have the SQL Server Integration Services Service (Control panel, admin tools, Services) listed in your services list? – billinkc Dec 11 '19 at 22:21
  • There's no indication in your question anywhere that you actually have installed _Standard Edition of Integration Services or higher._ – Nick.Mc Dec 12 '19 at 11:04

3 Answers3

0

Something to check:

  • Make sure that you have installed SQL Server Integration Services (not SSDT) from the SQL Server installation (Make sure that it is checked within the feature list).
  • If SSIS is installed, make sure that the Integration Services service is running
  • If service is running, make sure that the TargetServerVersion of the package you created matches the installed SQL Server version

Helpful links

Hadi
  • 36,233
  • 13
  • 65
  • 124
0

I'm suspicious of suggestions that this is some kind of software installation, which version of software do you I have, kind of problem. I installed VS 2019 Community and the VISX for SSIS. When I dtexec a package that has an Excel Destination I get the error message in this thread. When I dtexec a package that copies an ms sql table to a flat file, it runs fine as a dtexec. In my mind, if it was some huge software version kind of problem, you'd get this error with every dtexec.

renaissanceMan
  • 385
  • 2
  • 7
  • Running packages with dtexec.exe is unsupported and unlicensed without SQL Server Integration Services. However interactively running packages with SSMS and Visual Studio is supported and allowed. The technical limitation that prevents you from running dtexec is imperfect, however. So it sometimes works. – David Browne - Microsoft Feb 15 '22 at 23:26
  • David, thank you, that's interesting. So does that mean you shouldn't try to use dtexec.exe unless you have a paid for copy of SQL Server Integrations Services? dtexec.exe doesn't work with "Community" versions? – renaissanceMan Feb 17 '22 at 01:33
  • It works with developer edition for dev/test, but otherwise yes. – David Browne - Microsoft Feb 17 '22 at 01:48
0

Related: DTExecUI.exe is under "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\CommonExtensions\Microsoft\SSIS\150\Binn"

Will
  • 419
  • 4
  • 13