CODEWITHSUNDEEP
powershellssis
Jijo Nair
Jijo Nair

Reputation: 838

How to execute ssis package using a powershell?

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

Upvotes: 0

Views: 15551

Answers (4)

Jijo Nair
Jijo Nair

Reputation: 838

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

Upvotes: 4

Red Devil
Red Devil

Reputation: 2393

You should try this it works for me.

EXEC xp_cmdshell 'dtexec /f "c:\ssisExample.dtsx"'

Upvotes: 0

Navid Golforoushan
Navid Golforoushan

Reputation: 766

/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

Upvotes: 0

Chris Albert
Chris Albert

Reputation: 2507

Your package should have the .dtsx file extension. Try this 

dtexec /File c:\ssisExample.dtsx

Upvotes: 0

Related Questions