Archive for May, 2012

SQL Server 2012 Environment Variables

May 20, 2012

There are several new features introduced in SQL server 2012.  There have been some very compelling improvements to SSIS. The new SSIS catalog is one of the most important because it means that SSIS now ships with it’s own Performance Framework.

Another feature and the purpose of this BLOG post is to introduce Environment Variables. This is a cool feature because now you don’t have a dependency on environmental variables in the operating system.  Some Network Admin won’t let BI developers access the OS. Environment Variables also simplify disaster recovery because you don’t have to recreate the environmental variables.

I am not going to give a step-by-step on how to step environment variables. Plenty of tutorials have already been created on the web. What I would like to do is demonstrate  that Environment Variables can be referenced by anything that Environmental Variables can reference this includes project and package level SSIS objects like connection managers, parameters, and variables.

I created a demo package below with project and package level parameters and connection managers:




I created a catalog and folder on SSISDB and deployed it to the server:


Now environment variable can be created and reference the any SSIS object property in the project: (make sure the data types match)


Now you can create the reference which ties the SSIS object property to the environment variable.


Then choose the parameter or connection manager and set it’s value to the environment variable


Notice how you can choose from the package level or project level.


CDC for in SQL 2012

May 13, 2012

The new SSIS CDC Data Flow Task is one the best and little known features for 2012. If you have  a data source that is either SQL or Oracle now you have no excuse not to do CDC.  Along with CDC I would highly recommend that you give the ETL the ability to do good old fashion RBAR (row-by-row) delta detection. I have already built  an example SQL 2008 CDC Framework.

The following post from Rakesh Paria greatly helped me understand how to configure the Oracle server for SQL 2012 CDC:

Configuring your Oracle server


Before creating the service, you must ensure that the following configuration steps have been performed on your source Oracle database:

1. The database is set to ARCHIVELOG and is OPEN

2. The user that is going to connect to the Oracle database has DBA privileges on the database (since you will look up the log miner data from the database)

You should also install the Oracle client and configure the service names on the machine where you will be installing the CDC services on.

Installing the CDC Service Configuration and Designer MMCs

The MMCs are not installed by default when you install your SQL Server instance. You must install them separately by installing them using their MSIs.

The MSI installer for CDC Service Configuration MMC is named AttunityOracleCdcService.msi and the installer for CDC Designer is named AttunityOracleCdcDesigner.msi. They can be found in your installation media under the folder \Tools\AttunityCDCOracle. In the folder you would find x64 or x86 folders which correspond to the different processor architectures. Within the x86 or x64 folder you would find folders for various locales including 1033 which corresponds to English (US).

Thus, to install the English (US) x64 version you must go to \Tools\AttunityCDCOracle\x64\1033 folder.

Once installed, you would find a shortcut created under the start menu at Start>Program>Attunity à Change Data Capture for Oracle>CDC Service Configuration

Note You must install the same bit version of the MSI as the Oracle client installation you have on the local system. Thus, if you have installed the x86 version of Oracle client then you must also install x86 version of the Oracle CDC service.

Setting up your service

You can set up a new CDC Service using the CDC Service configuration MMC. But before you create a new service, you will have to prepare a local SQL Server instance. You can do so by using the actions pane in the MMC as shown below.


Once the local SQL server is prepared you can use the New Service action to create a new CDC service as shown below.


Couple of things to note here:

1. The service account you use should have logon as service policy assigned to it

2. The master password will be used to create a asymmetric key which will be used to encrypt the Oracle credentials which will be provided later

Once the service is created you can see it under the services node.


Creating a CDC instance

You can create a CDC instance using the CDC designer configuration MMC which you installed earlier. A CDC instance is defined as an object consisting of:

· The source Oracle database connection information

· Credentials and properties to connect to the source Oracle databases

· Source tables for which you need to capture CDC. You can even configure the instance to capture only a subset of the columns of each table.

· Mirror SQL Server database name which will contain the mirror tables for the source tables/columns defined in the previous step

You can create the instance as shown in the following steps

1. First you should connect to the SQL Server instance on which you created the service


2. Now you can see all the services available for the instance


3. Now you can create a new instance using the right click context menu for a service


4. The Oracle CDC Instance wizard opens up and the first pane allows you to define a service name. It will also ask you the name for a mirror database that would be created on the same SQL Server instance as step 1. You can rename the mirror database as well.


5. The next steps will ask you to input the Oracle connection string as well as the user credentials for connecting to the Oracle database. Note that it takes the Oracle connection string in the following format: host[:port][/service name]

Note that you should have installed the Oracle client and setup the service naming before this step.


6. Now you can select the tables you want to include in your CDC instance.


7. You can also edit the instance and select specific columns as well as specify a CDC role to be used when creating the CDC instance on the local SQL server database


8. In the next steps you would asked to run a script on your source Oracle database which essentially sets up Supplemental logging

9. You can click through the wizard to Generate the Oracle CDC instance

Operating a CDC instance

Once you have created the CDC instance you can now start and monitor the CDC instance using the designer MMC. By default the Service would be in INITIAL (stopped) state. You can start it as shown in the figure below.


The service will be started and the status would inform you of the current state of the service, transactions’ SCN for Oracle and the number of transactions being processed at that instance as shown below.


You would notice that a database would be created on the SQL Server instance you specified and the mirrored table would be setup. CDC would have also been enabled on the table and a corresponding _CT table would have been created capturing the changes from the mirrored table as shown below.


This post has been an introduction on how to get up and running with the new CDC for Oracle features introduced in SQL Server 2012."

Installing SSAS 2012

May 6, 2012

I don’t usually write posts like this but there have been a few substantial changes to how SSAS 2012 is installed as compared to the previous versions.  The changes also underscore the changes that have occurred to SSAS with the introduction of the Tabular Model.

I am not going to take you through all the steps the. But when you get to the Analysis Services Configuration screen you can now choose to install SSAS in  the traditional Multidimensional Mode or Tabular Mode. You cannot install both at the same time but you must install them one at a time.

In the example below I choose to install the Tabular Model first:


I am not going to walk through the rest of the steps. If you want to install the Multidimensional you will have to re-run the installation again and choose the Multidimensional Mode. See the next three screen shots:




If you have run through the installation two times, one for each mode, then you should see something like the following in SSMS. Notice the two different instances:


The above only serves as an introduction and provides a context to some of the differences you will encounter when working with a Tabular project in SQL Server Data Tools (SSDT) aka the new BIDS.

When you first fire up SSDT (I will miss BIDS because it sounds so much smoother;) and create your first Tabular project see below..


You will be confronted with a strange new question:


You will be asked to choose a workspace server. What this means is that you must choose a previously installed instance of SSAS in Tabular mode. The workspace server will be used during development of the Tabular project in SSDT.  And in fact SSAS automatically creates a new database with the project’s name with a GUID appended to it.


This means in order to develop a Tabular project in SSDT you must have first installed a SSAS instance in Tabular mode. (Don’t be surprised if you see this on a test sometime see my linkedin profile). With that said  I like this change because unlike the old multidimensional project in BIDS which runs in 32 bit during debug, you can now develop a Tabular project in SSDT in 64 bit mode and then deploy it to your 64 bit server. This eliminates the headache of having deal with both 32 bit and 64 bit code when deploying a project.

Additional Resource:

Cathy Dumas: Configuring a workspace database server