The Datachix Blog











{December 2, 2011}   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:

    1. Right Click the Environments folder under your Parent SSIS Catalog folder. Select create environment. Give the environment a name. Click OK. I called my first environment Dev.
    2. On the environment you just created, right click properties. Select the variables tab. Type a name to create a new variable and select a logical datatype. I called mine BikeServer
    3. Create another environment following the same process. I called mine QA.
  1. Create an environment variable in the new environment that has the same name and datatype as the environment variable you created in the other environment. So my second environment variable would also be called BikeServer.
Diagram 1. Creating the Dev Environment
 

Diagram 1 – 3. My two Environments– Dev and QA

Diagram 2. My Environment Variable called BikeServer I gave it the value of the BikeServer on the Dev server which is Dev42. For my QA Environment, I created the same variable, but gave it the value of the QA server, QA42.

So you’ve now got your environments and they each have an environment variable called BikeServer, which has its value properly set. We are going to use the environment variable to change the server name according to which Environment you use. You’ll see in a minute.

To configure the project to use the Environments and the Environment Variables:

  1. Select the Projects folder. Right click your Project and select Configure
  2. Select the references page in your project configuration dialog box.
  3. Click add. Select an environment from “Browse environments window. You can add any or all of the environments you’ve created in the project folder. The diagram shows that I already added Dev and am about to add QA.

Configuring the Project to use the Environments. Start with the References tab to add the Environments

4. Now go to the parameters page. I am using the Environment Variable to control my server name, so I’m going to go to the Connection managers tab inside the Parameters page.

 

On the Parameters tab. I’m using my BikeServer Environment Variable to change the value of the ServerName in my connection manager.

5. Click the ellipses beside the value you want to configure. In this case, the ellipses beside the ServerName.

6. Click radio button for use environment variable in the Set Parameter value window which comes up. “Set Parameter Value”

7. Select the environment variable you will use, in this case BikeServer. Click OK. Click OK to exit the configure dialog box.

Setting the parameter value.

Configuring the Execution.

Now the ServerName in your connection manager is going to use the value of one of the environment variables you configured. To choose which one, you must configure your execution by doing the following:

1. Right click on your entry point package. Select Run (this is configuring your execution, not running it yet).

2 . When the Run Package dialog box opens, note that you now have to choose an environment, or explicitly supply a value for ServerName, because in the project it has been configured to use an environment variable. We will click the check box by Environment and select Dev. Recall that the value of BikeServer in the Dev Environment was Dev42.

You made me this way, now you have to choose an environment!

3. Click ok. This will start the manual execution of your project. The value of the server name in the connection manager will be DEV42 in this execution, because that is the value of the Environment variable inside the Dev Environment in the SSIS catalog.

Using Agent to schedule jobs with SSIS Catalog projects:

It’s nice that we have a more convenient manual way of executing packages; however, most of the time the goal of packages is to automatically run them on schedules. To achieve this with SQL Server Agent, the SSIS Catalog is now available as a package source when configuring agent jobs.

Agent Package Source

Well kids, thanks for hanging in there through this whole class. The SSIS Catalog is very nifty, but also very different than what we are used to. I hope this post will keep someone from falling in the same holes I did while I was learning.

For more excellent reading on SSIS 2012, please check out Jamie Thomson’s blog posts on the topic. Jamie also has a good set of links to other useful articles on SSIS here. Also check out the official SSIS team blog. Thanks to Matt Masson (T) of that team for his advocacy for SSIS 2012. Matt rocks!

Thanks to Jorge for starting SQLU, and for inviting me to present on a topic I find really exciting. Be sure to check out Audrey’s week next week at SQL U, she’ll be covering Data Modeling.



SQL Nikon says:

Thanks for these two (three) lessons on SQL2012 SSIS. I just deployed my first package and the parameters and environments are working out great!



You’re so welcome. Glad to hear it. Thanks for reading–Julie



This was a very helpful post. Thank you, Julie! :)



[...] Use Environments to configure Environment Variables to hold connection string info such as server names for each different environment (i.e. ServerA for the FinancialServer for development, ServerB for the FinancialServer for QA).  Again this removes the need to use package configurations: SSIS 2012 Deployments, Lesson Two — Part B [...]



Anil says:

Thanks for these two (three) lessons on SQL2012 SSIS. can you also please post how to schedule the deployed packages in SQL Server Agent! that would help me a lot. Thanks again.



Hi Anil, Here is the link on MSDN for how to schedule the packages using sql server agent: http://msdn.microsoft.com/en-us/library/gg471507.aspx . If you are using the steps in this blog series, you are using the SSIS Catalog. So in the link I’ve given here, pay attention to all the steps referring to the SSIS Catalog. If the steps for actually scheduling the job is what you were after, that info is here: http://msdn.microsoft.com/en-us/library/ms191439.aspx . Hope that helps! Thanks for reading.



Hi, quick question. How do you enable the “Environment” checkbox when setting up the SSIS package execution through the SQL Server Agent Job? Mine for some reason is disabled :(

Any help would be greatly appreciated. Thanks,



Hi Michael, I wonder if maybe the package you are calling from the agent job has any environments set up? I went into two job setups, one where i called a package that had an environment created under its folder in the SSISDB catalog and the other that didn’t and the one without the environment had Environments disabled in the configurations section of the agent job on the step which called it. Hope that helps!

Thanks for reading,
Julie



yaniv levy says:

Thanks , very usefull, exactly what ssis 2012 beginner need.



Russell Young says:

In our 2008 packages we have an OS environmental variable that tell the package what server the SQL configuration table is on. When the package run the package configuration go to that table and get variable that every project uses(such as workspace). I am trying to implement this in 2012(SSDT) but all it talks about is environmental variables that are unique to the project. I was wondering if your stored procedure could be used to get those variable that all projects will share. Or do you have a better solution for 2012.



Julie Smith says:

Hi Russell, You can use the environment variables of pre-2012 SSIS by converting the project to Package Model. At one point this was called legacy and i think that is how I referred to it in my posts. Thanks for reading, hope that helps
Julie



Leave a Reply

et cetera
%d bloggers like this: