Microsoft has introduced a new model for deploying SQL Server Integration Services (SSIS) packages into SQL Server 2012. Prior to releasing this new model, the locations to store SSIS packages were to deploying SSIS packages to:
And the methods for deploying SSIS packages into the above locations were:
One can immediately begin deploying projects, once the SSIS catalog has been created on the instance. One can upgrade the packages that have been developed in SQL Server to support the new deployment model by using the “Upgrade Wizard” which is available in Data Tools or Visual Studio. One lose some capabilities after upgrade procedure such as:
Read the 26-page whitepaper “What is the SSIS Catalog?” to learn how to create SSIS objects and projects and how to execute the SSIS packages. Examples provided in this whitepaper should provide someone new to the catalog a basis to use the SSIS catalog. For the more experienced, the whitepaper summarizes some more advanced features of the catalog and shows how to create SSIS objects and projects and how to execute the SSIS packages. In particular, the whitepaper discusses how to create the SSIS catalog, and the SSIS catalog properties and objects. Further, the whitepaper discusses the deployment and versioning of SSIS projects. Further, the whitepaper discusses executing packages via SSMS, SQL Server agent, and stored procedures. Finally, the whitepaper discusses the SSISDB database, in particular execution and operational data.
This whitepaper does not contain all the features and functionality available in the SSIS catalog but provides enough insight and examples to explore the power of the catalog. The advantages of using the SSIS catalog include:
By having access to this data, the database administrator or developer can build more robust extract, transform, and load (ETL) solutions, troubleshoot performance issues and execution failures while easily deploying projects. The examples provided here should provide someone new to the catalog a basis to use the SSIS catalog. For the more experienced, it summarizes more advanced features of the catalog.
Click here to download the PDF file.
The presenter, Stan Geiger, has over 25 years' experience with databases. Stan has been in various roles such as business intelligence (BI) architect, data architect and developer. He has designed and built large-scale data warehouses and multidimensional cube installations in various industries such as finance, healthcare, energy, and the US Department of Defense (DOD). He is the Product Manager for the business intelligence products at IDERA.
Powered by IDERA