I previously wrote article SQL SERVER – Import CSV File into Database Table Using SSIS. I was asked following question by reader that how to run the same SSIS package from command prompt.
This is really interesting question and very easy one as well. You can execute SSIS Package using command line utility.
C:\>dtexec.exe /F "C:\ImportCSV\Package.dtsx"
When you run above command it will give you start time, end time and total progress of the package as well.
There are various options of the DTEXEC available you can see that using dtexec.exe /?
In future we will see how the SSIS task can be configured in scheduled task.
Reference : Pinal Dave (https://blog.sqlauthority.com)
21 Comments. Leave new
Hi Pinal! How are you! Long time no see! Hope everything is fine.
I’m writing to you ’cause I noticed you mentioned DTExec. I’ve wrote an alternative version called DTLoggedExec that has some additional features that the original one and make life simpler to BI Developers.
It’s available on CodePlex, along with the source code, here:
Enjoy!
Thanks pinal & Thank you Davide Mauri for shring the info.
How do I execute an SSIS package from SSMS client?.
from ssms
new query window
xp_cmdshell ‘dtexec.exe /F “C:ImportCSVPackage.dtsx”‘
what are the prerequisites for running dtexec on a remote computer?
Can we run SSIS package from another computer that doesn’t have SQL server installed?
Hi Mike, I guess you need Integration Service installed on the server.
What if the package is encrypted with password?
/De[crypt] password
I need that how can we configure this SSIS package in Sqlserver agent job..
I write same command as your but it does not work u can tell how to insert query work by ssis package
Did you get any error?
Have you tried to set variable values using /SET option, it doesn’t work from the SSIS execute utility, or command line or SQL Job.
I tried all options to pass any variable and all fails, the error is:
Executed as user: DOMAINMike. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 3:04:46 PM DTExec: Could not set Package.Variables[User::EmpIds].Value value to 1. Started: 3:04:46 PM Finished: 3:04:47 PM Elapsed: 0.359 seconds. The package execution failed. The step failed.
This is all the combinations i have set to pass my variable to the SSIS package:
Package.Variables[User::EmpIds].Value
Package.Variables[EmpIds].Value
PackagemyPackageName.Variables[User::EmpIds].Value
PackagemyPackageName.Variables[EmpIds].Value
any help is much appreciated.
–Note: we met @ MVPsummit were we lined up to take our bus if you remember and we had a little discussion :)
Hi all… I have issue to excute this task ( DTExec.exe /f TEST.dtsx ) BUT, there is nothing been inserted. When I try to execute SSIS using VStudio. It’s working.
Output :
Source: Data Flow Task 1 Lookup [84]
Description: The component “Lookup” (84) encountered duplicate reference key
values when caching reference data. This error occurs in Full Cache mode only. E
ither remove the duplicate key values, or change the cache mode to PARTIAL or NO
_CACHE.
DTExec: The package execution returned DTSER_SUCCESS (0).
Thanks
Hi,
You will try below way .
dtexce /f “u r packagepackage path”
Hi all,
This is rajesh from heyderabad ,if we have any doubts in sql related question plz post.
i will give the solution for u r queries.
Than ks,
Rajesh
Hi ,
I just wanted to know whether we can FAIL A JOB IF THE PACKAGE EXECUTION FAILS???
If yes,
HOW??
Thanks
Hi,
I want to run two SSIS packages at a time by using SSMS, is it possible? please let me know ow to do that.
Hi Sir, Can I run a SSIS Package (.dtsx) remotely using C# application which is invoked using Application Server? The application Server is running from a machine which doesn’t have any SQL server instance or SQL integration service installed. If I install dtexec.exe without any tools, is it going to work? I can get dtexec.exe from my machine and can copy to Application server machine.
Nice work. How do you recommend setting the password when executing a package this way?
How can I run package from command that is deployed as project. We are getting Error 40 and an error 0x80131904 with DTExec /ISSERVER “\SSISDB\folderB\Integration Services Project17\Package.dtsx” /SERVER “.” /Envreference 2