SQL University – SSIS 2012 Deployments, Lesson Two — Part B

Welcome back. Today we’ll finish up this whole series by Executing packages with the SSIS Catalog in SSIS 2012

This is the conclusion of my series on Deploying and Executing Projects and Packages in SSIS 2012. If you are just joining us, you may want to read Lesson One and Lesson Two, Part A.

We’ve deployed the project to a folder in SSIS Catalog, now let’s create Environments and Environment Variables inside the SSIS Catalog:

Continue reading

SQL University – SSIS 2012 Deployments, Lesson Two — Part A

Lesson Two. Deploying Projects in SSIS 2012 (Today’s class is longer than I expected, we’ll take a break and finish tomorrow.)

Before we get started, a big thank you to Jorge Segarra (blog|Twitter) for inviting me to write for SQL University .


PreRequisite: Lesson One

By the end of this part of the lesson you should understand the following processes/terms.

  1. Building SSIS Projects
  2. Ispac files
  3. The SSIS Catalog
  4. Deploying ispac files

By the end of the second half of the lesson, the following will be covered:

  1. SSIS Catalog Environments
  2. SSIS Catalog Environment Variables.
  3. SSIS Executions
  4. Building Agent jobs with SSIS Catalog projects.

So you’ve got your Project with a Parent package. (by the way, you should designate Parent Packages as Entry Point packages inside a project by right clicking them and selecting “Entry Point Package”). You’ve got some child packages. You’ve got some variables. You’ve got project level parameters for values which should be available to all packages inside a project (like say, a directory for importing/exporting files, a client to process, a business unit to process, etc. ) and you’ve got package parameters for values which need to be passed from parent packages to child packages. You’re ready to Build, Deploy and Execute in SSIS 2012 utilizing all of its new toys features.

Oooo shiny new SSIS Project—Ready for Deployment

Continue reading

SQL University– SSIS 2012: Deploying Projects –Lesson One

Lesson One, where I introduce tons of prerequisite new concepts in preparation for Deploying and Executing in Lesson Two.  The main concepts and terms covered in this lesson are:

  1. Projects
  2. Project Deployment Model
  3. Legacy Deployment Model
  4. Project Reference reference type –Execute Package Task
  5. Ispac files
  6. Parameters, subdivided into the two following subtypes:
  • Project  Parameters
  • Package Parameters

It will help if you are familiar with the following concepts inside SSIS (pre 2012 versions) before attempting this course 😉

  1. Execute SQL Task
  2. Execute Package Task
  3. Connection Managers
  4. Variables
  5. Parent/Child packages

SSIS in 2012 brings a great number of significant changes.  It is the first version since its initial release with SQL Server 2005 to look significantly different.  There is a more modern look and feel to be sure.  However, the really big change to SSIS in 2012 is in the way in which it is deployed.  There is a new method of deployment called “Project Deployment”, which has as much to do with how and where a package is deployed as it does with how a package is bundled during development.  Let me go back in time a bit and explain. Continue reading