SSIS 2012 Package Deployment Configurations Part 1

Posted by Jeff Rix on September 20, 2013 under SSIS | Comments are off for this article

SSIS 2012 Package deployment method allows shared connection managers and project parameters. I am going to demonstrate how these features can be used to switch environments easily for an ETL function.

I am going to place 3 files in a Dev and QA folder and will write to a Dev and QA database. We will use Windows Authentication for now.

The first step we will take is to right click the project and change the Security Protection Level to EncryptSensitiveWithPassword. You will then enter the password and confirm it. All packages is this project have to have the same security protection setting and the same password.

image

The second step is to add a Project Parameter to read the files. Double click Project.params. We are going to add a parameter called Environment with a string data type. This parameter will be accessible to all packages in the project. Set the value to Dev. The description will be useful in the Integration Services Catalog so go ahead and fill it out as well.

image

The third step is to add a connection manager. This connection manager will automatically be applied to the whole package. We will start with the Dev database connection and then rename it to Connection.conmgr.

image

Let’s create 3 packages (PackageA, PackageB, and PackageC). Notice the security protection is already set and the connection is already available in the connection manager.

Take File A and add a flat file connection. Connect to the Dev folder’s FileA.txt.

image

Add a Connection Manger Expression to replace the Dev part of the connection string with the environment project parameter.

"C:\\SSIS\\"+@[$Project::Environment]+"\\FileA.txt"

Create a data flow with the flat file as the source and the shared connection as the destination. Hit the new button on the destination and name the table FileA. Once this works do this for Package B and PackageC.

image

Now let’s switch to the Database we will deploy to. Go to the Integration Services Catalogs and right click > Create Catalog. We will keep the default name of SSISDB. Type a password and then Retype the Password and click okay.

image

Now right click SSISDB and create a folder called Deploy_Demo.

image

Switch back to SSDT. We are now going to deploy the project. This will deploy all the packages, the connection managers, and the project parameters. Right click the project and click deploy.

The Select Source should already be populated like shown below.

image 

Now we will specify our destinations Server Name and Path.  The server is where we setup the Integration Services Catalog in the previous step. The path is the folder we created in the previous step.

image

Click next and then deploy. If everything passed you have now deployed the project.

image

Switch back to the database engine and refresh the Integration Services Catalog. You should now see the tree structure shown below.

image

Right click Connection_Parameters_Demo and click Configure. We see that the Parameters tab has our Environment parameter set the the default “Dev” value. Click the Connection Managers to see all the connections. Notice that the Connection (project database connection) has a Container of the project while the flat file’s have a Container of their respective packages. In other words the database connection is for the project while the files are contained at the package level.

image

We are going to load the Dev FileA to the Dev database and the QA FileA to the QA database. The QA file has one less record so we can compare counts.

To execute PackageA with it’s default (Dev) settings right click > Execute > and click okay. Click no on the pop up.

Now we are going to execute PackageA again but we will first click the ellipses button on the Parameter environment and change to “QA”.

image

 

Now click the Connection Manager tab and change the InitialCatalog(destination database) to QA. Notice after you change the values it makes them bold.

image

Now click ok and click no on the pop up. When I run the below SQL command I see that there is one less record in QA which validates that it ran a different file based on the parameter.

select count(*)
from Dev.dbo.FileA

select count(*)
from QA.dbo.FileA

This covers a basic example of the project deployment method and how it can be used to switch between environments.

Production Dashboard Trend and Forecast

Posted by Jeff Rix on December 15, 2014 under Spotfire | Comments are off for this article

I was tasked with creating a table that would compare yesterdays production versus Forecast as well as compare yesterday’s production versus the previous day (day before yesterday/two days ago).

I allow the user to pick one production measurement to compare against using a drop down list property control.

image

They are also allowed to pick a specific Forecast for comparison.

image

The last dropdown selects the Well level hierarchy we want to view. “Well” is the lowest (base) level all the way up to highest level in the hierarchy “Asset Area”.

image

Production versus Forecast

Our first objective is to add an icon axis to determine how yesterdays production was versus yesterdays forecast.

I used a star with a traditional stoplight color scheme (green –above forecast 105% or greater, yellow – close to forecast 95%-105%, red – below forecast 95% or less).

imageimage

To just get yesterdays value I will do a calculated ‘dashboard ranking’ column.

Rank([Production Date],”desc”,[Well Name],[Forecast]),null)

Since my most recent value is yesterday I limit my data to pick just yesterdays production. Do this in the axis and check ‘specify separate data limiting’.

Data Limiting

[DashboardRanking] = 1  AND ([Forecast] = (${Forecast}) or [Forecast] = ‘Production’)

Now we will enter the calculated Icon Expression.

Expression

case

when “${ProductionAttribute}” = “BOE Gross” then Sum([BOE Gross Production])/Sum([BOE Gross Forecast])
when “${ProductionAttribute}” = “BOE Net” then Sum([Net BOE Production])/Sum([Net BOE Forecast])
when “${ProductionAttribute}” = “Oil Gross” then Sum([Oil Gross Production])/Sum([Oil Gross Forecast])
when “${ProductionAttribute}” = “Oil Net” then Sum([Net Oil Production])/Sum([Net Oil Forecast])
when “${ProductionAttribute}” = “Gas Gross” then Sum([Gas Gross Production])/Sum([Gas Gross Forecast])
when “${ProductionAttribute}” = “Gas Net” then Sum([Net Gas Production])/Sum([Net Gas Forecast])
when “${ProductionAttribute}” = “NGL Net” then Sum([Net NGL Production])/Sum([Net NGL Forecast])
end

To check our calculations we will add a calculated value axis for forecast and production.Use the same data limiting values.

Forecast

Data Limiting

[DashboardRanking] = 1  AND [Forecast] = ${Forecast}

Expression

case

when “${ProductionAttribute}” = “BOE Gross” then Sum([BOE Gross Forecast])
when “${ProductionAttribute}” = “BOE Net” then Sum([Net BOE Forecast])
when “${ProductionAttribute}” = “Oil Gross” then Sum([Oil Gross Forecast])
when “${ProductionAttribute}” = “Oil Net” then Sum([Net Oil Forecast])
when “${ProductionAttribute}” = “Gas Gross” then Sum([Gas Gross Forecast])
when “${ProductionAttribute}” = “Gas Net” then Sum([Net Gas Forecast])
when “${ProductionAttribute}” = “NGL Net” then Sum([Net NGL Forecast])
end

Production

Data Limiting

[Forecast] = ‘Production’
AND
[Production Date] < Date(DateTimeNow())
and
[Production Date] >= DateAdd(‘dd’,((${LastDays}+1) * -1),DateTimeNow())

Expression

case

when “${ProductionAttribute}” = “BOE Gross” then Sum([BOE Gross])
when “${ProductionAttribute}” = “BOE Net” then Sum([Net BOE])
when “${ProductionAttribute}” = “Oil Gross” then Sum([Oil Gross])
when “${ProductionAttribute}” = “Oil Net” then Sum([Net Oil])
when “${ProductionAttribute}” = “Gas Gross” then Sum([Gas Gross])
when “${ProductionAttribute}” = “Gas Net” then Sum([Net Gas])
when “${ProductionAttribute}” = “NGL Net” then Sum([Net NGL])
end

We can now check to see that we got the correct icon results

image

Production Trend

Now we would like to compare yesterday versus the previous day.

To do this we will create an icon graph and limit the data to the prior two days.

Data Limiting

[DashboardRanking] in (1,2)  AND [Forecast] = ‘Production’

We will now calculate icons by taking yesterday minus the previous day.

Expression

case

when “${ProductionAttribute}” = “BOE Gross” then
sum((If([DashboardRanking]=1,[BOE Gross],0)) – (If([DashboardRanking]=2,[BOE Gross],0)))
when “${ProductionAttribute}” = “BOE Net” then
sum((If([DashboardRanking]=1,[Net BOE],0)) – (If([DashboardRanking]=2,[Net BOE],0)))
when “${ProductionAttribute}” = “Oil Gross” then
sum((If([DashboardRanking]=1,[Oil Gross],0)) – (If([DashboardRanking]=2,[Oil Gross],0)))
when “${ProductionAttribute}” = “Oil Net” then
sum((If([DashboardRanking]=1,[Net Oil],0)) – (If([DashboardRanking]=2,[Net Oil],0)))
when “${ProductionAttribute}” = “Gas Gross” then
sum((If([DashboardRanking]=1,[Gas Gross],0)) – (If([DashboardRanking]=2,[Gas Gross],0)))
when “${ProductionAttribute}” = “Gas Net” then
sum((If([DashboardRanking]=1,[Net Gas],0)) – (If([DashboardRanking]=2,[Net Gas],0)))
when “${ProductionAttribute}” = “Net NGL” then
sum((If([DashboardRanking]=1,[Net NGL],0)) – (If([DashboardRanking]=2,[Net NGL],0)))
end

A positive result means that the trend is up, if it is negative trend is negative. We will be using green upper arrows for a positive trend and a red downward arrow to show a downward trend.

image image

To test this we will create a calculated column axis that just shows the previous days production.

Data Limiting

[Forecast] = ‘Production’
AND
[Production Date] < Date(DateTimeNow())
and
[Production Date] >= DateAdd(‘dd’,-2,Date(DateTimeNow()))
and [Production Date] < DateAdd(‘dd’,-1,Date(DateTimeNow()))

Expression

case

when “${ProductionAttribute}” = “BOE Gross” then Sum([BOE Gross])
when “${ProductionAttribute}” = “BOE Net” then Sum([Net BOE])
when “${ProductionAttribute}” = “Oil Gross” then Sum([Oil Gross])
when “${ProductionAttribute}” = “Oil Net” then Sum([Net Oil])
when “${ProductionAttribute}” = “Gas Gross” then Sum([Gas Gross])
when “${ProductionAttribute}” = “Gas Net” then Sum([Net Gas])
when “${ProductionAttribute}” = “NGL Net” then Sum([Net NGL])
end

We can now check to see that we got the correct icon results

image

Full Results

image

SSRS Matrix Export Page Break Solution

Posted by Jeff Rix on December 5, 2014 under SSRS | Comments are off for this article

Exporting a SSRS matrix report to Word by default will not break up the pages correctly. (example below).

clip_image002

If we would like to control how many Row Groups to show on each page to avoid splitting like show above we can create a column in the data source to control this.

Let’s say I would like to show 4 wells on each page because I know four wells can fit on a landscape word page correctly. Since I know I never have more than 60 wells per Route (my parameter) I can include this statement in my stored procedure. You could rewrite this more dynamically on your own time but using this to show what’s happening.

clip_image004

Essentially I am using the dense_rank() function to assign wells to a certain page number.

Now on the report side I add that as my first column in the Matrix and make the ranking column the first field in my Row Group.

clip_image006

I then go into my Row Groups – Ranking and set the Page Breaks to break between each instance of a group.

clip_image008

Now a page ends on a Well.

clip_image010

And starts on a well.

clip_image012

And will continue with each page having four wells per page for the whole report.