Integration Services is a new component of SQL Server 2005. It is created as a platform for developers to create solutions for extraction, transformation, and load of data (in data warehouse and other scenarios). This technology is a successor of Data Transformation Services that was introduced in SQL Server 7.0. Solutions created with it are wrapped and stored as SSIS packages. Naturally, their design and management are beyond the scope of this book, but I will show you how to run an SSIS package from a stored procedure.
SQL Server 2005 includes the dtexec.exe utility, which allows you to execute SSIS packages from the command prompt. We can simply call it using xp_cmdshell. To execute an SSIS package stored as DW_ETL.dtsx file, use
exec xp_cmdshell ' dtexec /f "c:\SSIS\DW_ETL.dtsx"', NO_OUTPUT
To execute an SSIS package that is stored in SQL Server and uses Windows authentication, use something like this:
exec xp_cmdshell ' dtexec /sq DW_ETL /ser myServer', NO_OUTPUT