I have made some ssis packages dtsx into my local system, and wanted to execute using a powershell script. I tried this code
dtexec /File c:\ssisExample.dts
This resulted in an error of
unable to load the package as XML because of package does not have a valid XML format
First Scenario: Directly executing a file using command in PowerShell
1) Dtexec /FILE ‘\FILE_PATH_NAME\ssisPackage.dtsx’
Errors recorded:
1) The XML is not in correct format/Unable to load the packages
2) Specified File path is not proper
3) At least one of the DTS, SQL, ISServer or File options must be specified
Resolutions
1. Make sure you put path in single quotes as dtexec /FILE
‘FILE_PATH/ssisPackage.dtsx’. Copy the path from the properties
under SSIS package which is created in Visual Studio.
2. Give access to SSIS packages to be remotely acess from a third
party. For that, run Dcomcnfg.exe (requires Local Administrator)
a) Go to Component Services->Computers->DCOM Config->Microsoft SQL Server Integration Services 13.0 (whatever version is installed).
b) Right click ->properties->Security tab->Launch and Activation permission -> Check remote launch and remote activation
c) Same for Access Permission
3. Make sure that system has Microsoft.SqlServer.ManagedDTS package
a) To check that try going to folder In run command C:\Windows\assembly\gac_msil
b) Move to folder name Microsoft.SqlServer.ManagedDTS and check for the package versionr
c) Once done try giving access to these DTS packages
d) Run Dcomcnfg.exe (requires Local Administrator)
e) Go to Component Services->DCOM Config->MsDtsServer100
f) Right click to properties and onto the security tab press Edit for Launch and Activation Permission. Allow for Remote Launch and Remote Activation. Close
g) Press Edit for Access Permission -> Allow Remote Access
Your package should have the .dtsx file extension. Try this
dtexec /File c:\ssisExample.dtsx
/De[crypt] password ! If you saved your package on local file system and used the wizard to create them MS tools usually encrypt the password on file. make sure decrypt the password with /de argument
You should try this it works for me.
EXEC xp_cmdshell 'dtexec /f "c:\ssisExample.dtsx"'
