I was testing an SSIS package (Developed using SSIS 2005 version) using DTEXEC command line tool. But it got crashed and showed me long list of exceptions/errors occurred during the “TRY” of execution. (I used the word TRY here because I have logging enabled in my SSIS package, but it did not log anything, that means it did not even start.) And at the end I see this message – “DTExec: The package execution returned DTSER_FAILURE (1)”. That means package was failed.
The main error description was “The task “SCR Get Task Start time” cannot run on this edition of Integration Services. It requires a higher level edition.”
But apart from this, there were few internal components/sources who threw exceptions while execution. Following is the list of those sources:
- SCR Get Task Start time
- SCR Get Task Start time
- SCR Concatenate ErrorMessage
- SCR Define Queries to log package & tasks
- SCR Get Task duration
So I open that SSIS project from the Source Control (TFS), and debug the SSIS package. It ran wonderfully but as soon as I ran it again using DTEXEC, it failed.
On the machine where I was trying to execute SSIS package using DTEXEC command line tool, it was missing “SSIS services runtime” installation. But that machine has all clients tools installed like BIDS, DTEXEC, and DTEXECUI etc. So I am able to develop new SSIS package and run it from BIDS but not able to execute packages using DTEXEC tool.
Run the SQL Server Installation DVD/Source and select “Integration Services” on the “Feature Selection” page while installation. That’s it.
In installation dialog box, the “Business Intelligence Development Tools (BIDS)” option installs the Integration Services components required to design a package, but the Integration Services service is not installed and you cannot run packages outside of BI Development Studio. So to execute your SSIS package using DTEXEC command line, you must select Integration Services on the Feature Selection page.
DTEXEC utility is a client tool for executing SSIS package But SSIS execution is carried by “Integration Services Runtime” and not by “Integration Services Service”. If you track-back to basics of SSIS, you will see that “Integration Services Runtime provides a core functionality for executing the package including logging, configuration, debugging etc.”.
So in short, if you wanted to execute SSIS package using DTEXEC or DTEXECUI then you have to install the server components for SSIS Service on your machine where you want to run the package. Remember, SSIS package you can create even in Notepad (provided you are well-versed with XML/XSL/XSLT etc.) but execution will be done only by “Integration Services Runtime” (if outside the BIDS) or using BIDS (because BIDS has “Integration Services Object Model” which interacts with “SSIS Runtime” for executing packages).
Hope you will find this interesting.