Running SQL Server 2005 Integration Services (SSIS) Packages


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 




Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL &  .NET
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
ISBN: 0072262281
EAN: 2147483647
Year: 2006
Pages: 165

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net