If you couldn't tell by the title, we're going to go beyond CRM 101 and get a little more technical in this post. Specifically, let's talk about a few important new features of SSIS 2012 that can help BI developers. SSIS 2012 introduced a new feature called Integration Services Catalogs, which means that now we can deploy projects/packages to SSISDB database in Integration Services Catalogs.

The Integration Services Catalogs must be created for the first time to use it. To create it, login to the SQL server 2012 database engine, right click on Integration Services Catalogs, click Create Catalog, and follow the wizard.

Once the catalog is created, we can now deploy packages to the catalog from SQL Server Data Tools (i.e. SSDT) which used to be called BIDS in earlier versions.

Now, launch SSDT and create a new Integration Services Project. In SSIS 2012, we can create two deployment model projects.

1. Project Deployment Model
2. Package Deployment Model

With the project deployment model, we can use the new feature of SSIS 2012 and deploy the whole project to the SSIS catalog, whereas with package deployment model, we cannot deploy the project to the SSIS catalog. But there is a simple and easy to convert feature in SSDT that can help convert projects between these two models.

If your project is in Package Deployment Model—which you can find in the project as below—you can convert it to project deployment model easily.

To convert the project to Project Deployment Model, click the project menu and choose Convert to Project Deployment Model. Follow the on-screen instructions.

Parameters

Another new feature of SSIS 2012 is parameters. We can create parameters with two types of scopes in SSIS 2012. Parameters can be created specific to a package, called package parameters; or they can be for the whole project, called project parameters.

Here's how to create project parameters: Open Project.params and in the design mode, click on the Add parameter button as pointed below. I added four parameters which I need to use in my script component later for connecting to CRM org. Also note that MSCRM_Pwd is marked as sensitive and the text is replaced with ***. Isn't that cool? Hope you will like this.

Remember, I could have created all the above parameters as package parameters but I chose to scope them to project because I might need to reuse these parameters if I decide to add more packages later. Package level parameters are available to all packages in that project.

For demonstration purposes, the screenshot below shows how to create package level parameter. Open the package in design mode, go to the Parameters tab and then Add parameter.

Now, as with any development, develop the package. In my case, I am reading records from CRM accounts and updating a staging table for use by another system. Here is my flow:

In the flow, I have a script component that actually authenticates with CRM and retrieves Account records. For that I need to pass the CRM connection information to the script component. But my connection information is stored in Parameters and not in variables. How do I pass them? Simple! I can now treat the parameters exactly like the variables. For identification purpose parameter names begin with $ sign. Unlike variables, I cannot WRITE values back to the parameters.

Now that I have completed the package development, let me now deploy it to catalog. Right click on the Project name in the solution explorer and then click Deploy. It will validate the package and will guide me through the process of deployment.

Type in the SQL Server 2012 name, browse the Integration Service Catalogs, and then pick a folder if not already created or create a new folder. In my case, I am deploying this project to the DemoParameters folder. After the deployment is complete, log in to the SQL server instance via management studio and navigate to Integration Services Catalogs. I can see the project and the package(s) in the SSISDB database.

In SSIS 2012 catalog, I can define environments and create environment variables and map those variables to the project or package parameters and control the values from one place without having to open package in the SSDT. This way I can store information securely in a database and not in XML file like dtsConfig.

Let me create an environment called Development and then create bunch of variables to map to the parameters.

I added development environment, added four variables and assigned the values.

Now I can configure the project to map the parameters to the Environment variables. Doing this would overwrite all the parameters values that I assigned in my development environment and get the values from the environment variables during run time. This creates a one point solution for any updates or changes in these values.

Right click on the project and then click Configure:

Go to the References navigation and then add Development environment.

Now that the project is configured to use an environment, we can map the parameters to environment variables.

In the above window, click Parameters in the left navigation. Select the parameter and click browse and then map it to the environment variable like below.

After I map all the parameters that I want to reassign the values from the environment variables, I am ready to schedule this package.

As usual, I can go to the SQL server agent and create a job and in the job step, point it to the package from the SSIS Catalog (new in SSIS 2012) and schedule it.

When configuring the Step property, click on the configuration tab and designate an environment:

For now, I am designating Development environment, but if my production URL and user info changes for my CRM, I can create Production environment with mapped variables and assign production environment here to simply read data from production CRM. This makes it really easy to switch between development, test and production environments.

Now I am all done. If any values for the package parameters change over time, I have one place to update i.e. Environment variables in SSIS catalog. Package maintenance is easy and anyone who does not have knowledge on visual studio or SSDT can still maintain the package from management studio SSIS catalog.

There are other features that come with SSIS catalog like querying SSISDB and built in reports for performance and other package execution related data. Keep exploring new features of SSIS 2012, and happy CRM'ing!