SQL Services Hot Tip: So what does the SSIS Service do? 01 Jul 2010
|
Microsoft Integration Services (SSIS) is a platform for building enterprise-level data integration and data transformations solutions. You use Integration Services to solve complex business problems by copying or downloading files, sending e-mail messages in response to events, updating data warehouses, cleaning and mining data, and managing SQL Server objects and data. The packages can work alone or in concert with other packages to address complex business needs. Integration Services can extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations.
One common misunderstanding is the requirement to have SSIS installed in order to execute SSIS packages. We have observed a number of occasions where clients have installed SSIS to try and get around various package execution errors. In all of these cases the installation of SSIS was irrelevant.
Running the Integration Services service provides the following management capabilities:
- Starting remote and locally stored packages
- Stopping remote and locally running packages
- Monitoring remote and locally running packages
- Importing and exporting packages
- Managing package storage
- Customizing storage folders
- Stopping running packages when the service is stopped
- Viewing the Windows Event log
- Connecting to multiple Integration Services servers
The DTExec.exe tool is installed with SQL Server and is used to execute SSIS packages whether they be executed from the SQL Server Agent, the command prompt or the DTExecUI.exe.
The DTExecUI.exe provides a nice interface to build the command line arguments for DTExec. Simply double-click the .dtsx file and DTExecUI.exe will open a window for executing the package. From this window you can specify the various command line arguments.
If you select the various options such as specifying a configuration file and then select the command line option, you can copy the command line arguments used for execution of your SSIS package. You simply need to prefix the commands with DTExec.exe.
SQL Services Ltd has significant experience with SSIS development and deployment including converting DTS packages to SSIS.
This Hot Tip was provided by Luke, one of our Nelson based SQL consultants. |
Click here to return to the news items... |