Resolving Missing Records in SSIS from Oracle Source

Thanks: First, a big thanks to Jim Benton who did the research to find the solution discussed here.

Overview:  An update to Oracle connection string, when retrieving data using SSIS, to avoid missing records when there’s a constraint on a date field.

Level:  Familiarity with the Oracle data providers in SSIS is presumed.

The Problem – aka What?  Where’s All The Data?

Recently we were working on an SSIS package.  Its source data was a view in Oracle (9.x).  The source query within SSIS wasn’t complex, but there was a constraint on the Last Update Date in Oracle.  Hold that thought.

As usual, I worked on the source query in SQL Developer first, to become acquainted with the data & finalize the query before it went into SSIS.  Let’s say SQL Developer returned 400 rows of data.  I put the source query into SSIS, with the standard Oracle OLE DB connector we use for dozens of packages, and ran it.  It succeeded all right, but only returned 250 rows of data.  After several Oracle developers looked into the view itself, they deemed there was nothing unusual about it.

At this point I admit my brain went through the stages:  denial, a smidgen of anger, and finally acceptance that we have a problem to solve.  Before we jump to the solution, let’s chat a bit about the OLE DB Oracle connectors…

The Two OLE DB Options for Connecting to Oracle

When you create a new connection in SQL Server Integration Services, you’ll see two Oracle options in the standard list.   The one we use is the Oracle Provider for OLE DB because it’s 64-bit and fully supported.

    

The connection string for Oracle Provider for OLE DB looks like this: 

Data Source=DataSourceNameHere;User ID=UserIDHere;Password=PasswordHere;Provider=OraOLEDB.Oracle.1;Persist Security Info=True;

The other option in the list is the Microsoft OLE DB Provider for Oracle, which is deprecated and only works in 32-bit mode.  The connection string for it would be like this:

Data Source=DataSourceNameHere;User ID=UserIDHere;Password=PasswordHere;Provider=MSDAORA.1;

Just  quick sidenote – since the Microsoft provider is 32-bit, if you want to run it inside of BIDS, the “Run64BitRuntime” property in the SSIS Project Properties needs to be updated to False.

We ended up trying the Microsoft provider & it returned all 400 rows we were looking for.  That’s great.  It actually worked for a short while as an interim solution.  However, the problem with saying that we’d just use the Microsoft provider for this one package is that it’s only 32-bit.  Every other package in the entire environment was 64-bit including the Masterload, so running this troublesome package via the normal Masterload & via the normal SQL Server Agent job wasn’t going to happen.  Thankfully we found a final resolution…

The Solution – Specifying “UseSessionFormat” in Connection String

The final resolution turned out to be specifying one additional attribute in the connection string called “UseSessionFormat.”  The connection string for the connection now looks like this:

Data Source=DataSourceNameHere;User ID=UserIDHere;Password=PasswordHere;Provider=OraOLEDB.Oracle.1;Persist Security Info=True;UseSessionFormat=True;

The UseSessionFormat=True allows OraOLEDB to override the default NLS (National Language Support) session format for the duration of the session.  The default value for this attribute is False.  The NLS settings affect not only languages, but dates & times as well.  If you recall, the source query had a constraint on the Last Update Date.  So there was something a little different about the Last Update Date in the source view which caused SQL to to change its behavior.

Finally, worth noting is we didn’t want to affect the dozens of other packages already in the same environment.  So for this solution we set up a second connection string entry in the configurations table – just to be on the safe side.

Finding More Information

MSDN Data Access Technologies Blog:  Every Bug is a Microsoft bug until proven otherwise

Technet Article:  SSIS with Oracle Connectors

Oracle Developer’s Guide:  What’s New in Oracle Provider for OLE DB?

Oracle National Language Support Guide:  Understanding Oracle NLS

Cancelling a Long Running SSAS Query

Overview:  Quick tip for cancelling a long-running SQL Server Analysis Services query.  Screen shots & statements are from SQL Server 2008 R2.

We’ve all been there…waiting in BIDS for a report to render.  You begin to think uh-oh.  After a bit longer you know it’s uh-oh. 

  

There’s multiple ways to accomplish cancelling the session; here’s one way to cancel a long-running SSAS query.  One caveat: this technique does require elevated privileges.

Step 1:  Use a DMV to find the SPID

First, you need to find the SPID associated to your long-running report query.  We’ll use a Dynamic Management View (DMV) for that.  First let’s launch an MDX window in SQL Server Management Studio:

  

Obviously you can modify the select statement here to whatever columns you wish to see. 

Query:

SELECT

session_spid

, session_user_name

, session_last_command

, session_current_database

, session_cpu_time_ms

, session_elapsed_time_ms

, session_start_time

, session_last_command_start_time

, session_last_command_end_time

FROM $system.discover_sessions

WHERE session_status = 1

AND session_user_name = 'DomainName\MCoates'

ORDER BY session_start_time desc

Results:

  

In the above query, I restricted the results to just my user name.  Using the results, find the query associated to your long-running report.  Note the Session_Spid (i.e., the Server Process ID).

Step 2:  Use XMLA Command to Cancel the SPID

Now that we have the SPID, we need to pop over to an XMLA window (rather than the MDX window we were in for Step 1 above).

  

Command:

<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<SPID>185325</SPID>

</Cancel>

After executing the Cancel Command, you should immediately see the message in BIDS that the operation has been cancelled.  Mission accomplished.

  

A Final Word on Permissions

I’m lucky enough in the current environment to be able to do this on my own.

DMV Select Permissions:  Requires VIEW SERVER STATE or VIEW DATABASE STATE permissions.

XMLA Cancel Command Permissions:  Requires administrative permissions.

Finding More Information

MSDN – Use Dynamic Management Views (DMVs) to Monitor Analysis Services

MSDN – Discover Sessions

MSDN – Cancel Element (XMLA)

Announcing SQL Saturday and BI User Group in Charlotte

For a while now, we’ve been having a bit of fun on Twitter with the #BeersAndBI hashtag.  For the past few months several of us have been getting together for some fun conversation & to make “plans.”  Now we’re ready to make a couple of very exciting announcements.

SQL Saturday BI Edition is Coming to Charlotte

We are thrilled to announce that SQL Saturday – BI Edition – is scheduled for October 27th in Charlotte.  It’s going to be held at CPCC, a local community college in South Charlotte (the Levine Campus in Matthews).  This is an excellent venue where the Carolina Code Camp is held annually.

Got some great info to share?  Submit a session!  The call for speakers is open until Friday September 14th (and we’ll be sure to announce the schedule very quickly after that).

Want to help?  Check the “Would you like to volunteer to help conduct the event?” when you register.  We’d love to have ya!  And we won’t work ya too hard.  Well, probably not.  <grin>

Ready to learn some new stuff?  Be sure to register soon!  And please pass the word to your friends & colleagues about the event!

New BI User Group Coming to Charlotte

In January 2013 we will hold the first meeting of a new BI User Group in Charlotte.  It will be a Local PASS Chapter.  As details become available, we’ll announce them.  Although I don’t have a lot of specifics yet, I’d like to share what our vision is for the group:

Free education about Microsoft BI

Useful info about Microsoft BI, PASS, local happenings

Networking & socialization

You got it - the “fun” element is part of our charter.  We think that if you choose to spend an evening with us, we should make it fun, as well as informative, and help you get to know other IT pros in the area. 

Who Are the Crazy Kids Behind All This?

From left: Javier, Rafael, Melissa, JasonYou probably caught that I said “we” a few times above.  I am a very, very, very lucky girl to have 3 good friends to cook up SQL Saturday and User Group adventures with (and partake in a few beers, of course, as well as a lot of laughs).  So now you know the real meaning of the #BeersAndBI hashtag.

Our group of organizers includes the following BI professionals:

    Rafael Salas     | Blog | LinkedIn | Twitter

    Javier Guillen  | Blog | LinkedIn | Twitter

    Jason Thomas | Blog | LinkedIn | Twitter

    Melissa Coates | Blog | LinkedIn | Twitter

We hope to see you at SQL Saturday #174 and the BI User Group very soon!

Why Would You Want to Edit an SSAS Database in Online Mode?

I recently had my first “legitimate” purpose for opening and modifying a production SQL Server Analysis Services database in online mode.  I needed to add a new cube calculation to one of the existing production cubes.  As you’d expect, I opened up the project, added the calculation, deployed it to the test environment, and was happy with what I saw so I proceeded to check in my changes to the project.  The project is integrated with TFS source control.  Now it’s time to get the new cube calculation migrated to production. 

Here’s where things got interesting.  Also checked into TFS were other changes to the SSAS database, made by another BI developer, which weren’t scheduled to be deployed for another couple of weeks yet.  Since you must deploy an SSAS database in its entirety, deploying in its current state wasn’t an option.

One choice would be to temporarily roll back the other developer’s changes from source control and deploy from the project – that would be appealing if I had quite a few changes to deploy & if the sequence of events in a multi-developer environment made reverting to a prior changeset possible.  However, since my change was one very small calculation, I chose to open up the “online” version of the SSAS database and add the calculation directly.  The change was also checked into the project file, so next time it’s deployed my change won’t be lost.

Let me just be clear this is absolutely not the first choice – there’s a much bigger risk of error when you make the change twice (i.e., once in the online version & once in the project).  And working in the online version just shouldn’t be a habit.  However, in a pinch it’s nice functionality to have in the ‘ol tool belt.

Opening an Analysis Services Database in Online Mode

Opening an SSAS Database in SSDT (SQL Server 2012) or BIDS (SQL Server 2008 R2 and prior):

If you’ve connected in online mode previously, the server(s) and database(s) will be listed in the middle box.  If not, enter them at the top to connect:

When you are working in online mode, it will tell you so within the name of each tab across the top:

As soon as you make your changes and save, you're done.  Saving changes while in online mode can be a bit slow.

Finding More Information

Technet – Working with Analysis Services Projects and Databases During the Development Phase

Reverse Engineering a SharePoint Integrated Mode SSRS Report for Use in BIDS

Overview:  Discussion of how to alter the RDL code of a Reporting Services (SSRS) report stored in SharePoint (Integrated Mode), so the report can be executed within BIDS.

A Little Background

Recently I was tasked with updating an SSRS report.  So I did a Get Latest in our Team Foundation Server environment & proceeded to open up the SSRS project in BIDS.  I open the report & run it before making any changes.  Huh.  Wait.  Something looks different.  So, I launch my browser & go run the live report, which is stored in SharePoint 2010 running in SSRS Integrated Mode.  Something IS different between the reports!  How can this possibly be?!?

Well, I will not name any names to protect the innocent, but one of the developers had been in a rush to get the report updated so he used Report Builder to change the live report directly in SharePoint.  I proceeded to do much “tsk, tsk, tsking” and looking over my glasses at the poor fella.  So, at this point, my first step is to update BIDS with the latest report code.  We have our BIDS environment integrated with TFS for source control.

Data Sources in BIDS versus SharePoint Integrated Mode

Before we try to sync up the RDL files, let’s do a quick refresher of how data source file extensions are just a bit different when we’re dealing with SharePoint integrated mode:

• BIDS or Report Manager (Native Mode):  RDS file format
• SharePoint Integrated Mode:  RSDS file format

BIDS takes care of this conversion for us when we publish from BIDS to SharePoint.  However, the difference in file formats makes it so we cannot just download the RDL and use it in BIDS – i.e., going backwards requires us to make one modification to the RDL code before the report will execute properly in BIDS.

Steps to Reverse Engineer the Report For Use in BIDS

Step 1.  Download the file.

• Within SharePoint, click the drop-down arrow > Send To > Download a Copy.  Save the RDL file wherever you like.

Step 2.  Copy contents of the file.

• Open the RDL file you just downloaded in your favorite text editor, such as Notepad.  Select all text and copy it onto your clipboard.

Step 3.  (Optional)  Reproduce the error.

Just for grins, let’s try to run the RDL in BIDS, without modifying it, so we can see the error that BIDS returns in this situation.

• Find the outdated version of the report within your BIDS project.  (Note: in my situation, the original RDL did already exist in TFS; it just wasn’t current.  If you need to create a new report, that's fine; the rest of the steps are still the same.)
• Within BIDS, right-click your report in Solution Explorer & choose View Code.  Select all of the XML code & delete it.  Replace it with the RDL code from your clipboard (i.e., the version from SharePoint).
• Now, try to preview the report in BIDS.  You should see an error to the effect of:

      An error occurred during local report processing. Could not find a part of the path … Path\DataSourceName.rsds.rds.

Hmmm, based on the error message we can see that SSRS is looking for the rsds file.  Well, that makes sense since we downloaded it from SharePoint.  The good news is we can fix that very easily!

Step 4.  Update the RDL file.

• Within BIDS, right-click your report in Solution Explorer & choose View Code. 
• Do a Ctrl-F to find the <DataSources> XML tag.  Find <DataSourceReference> & change the “http://url.rsds” to be the name of your data source as you have it set up in BIDS.  Here’s an example:

Before (SharePoint Integrated Mode):

<DataSources>

     <DataSource Name="MelissaDW">

          <DataSourceReference>http://reports.sqlchick.com/Reports/MelissaDW.rsds</DataSourceReference>

          <rd:SecurityType>None</rd:SecurityType>

          <rd:DataSourceID>a1eb2cb6-a837-475c-a924-ff4b4a195c04</rd:DataSourceID>

     </DataSource>

</DataSources>

After (BIDS & Native Mode):

<DataSources>

     <DataSource Name="MelissaDW">

          <DataSourceReference>MelissaDW</DataSourceReference>

          <rd:SecurityType>None</rd:SecurityType>

          <rd:DataSourceID>967ece07-fbff-4580-9968-97ebdd74b6bf</rd:DataSourceID>

     </DataSource>

</DataSources>

• When you preview the report in BIDS now, it should execute properly.  If it doesn’t, then find another existing report in BIDS that uses the same data source.  Copy & paste the whole contents from <DataSource> to </DataSource> and you should be good to go!
• Check-in to TFS, or whatever source control system you are using.

Lastly, it goes without saying, but any time you are modifying the RDL code directly – be super careful!

Improving the Performance of a Calculated Field in SSRS When Reporting from SSAS

Overview:  A quick tip re: using SQL Server Reporting Services (SSRS) Dataset Properties to derive a concatenated field.

Level:  Familiarity with SSRS parameters & datasets is presumed.

Recently I was working on an SSRS report.  One parameter required its label to be formatted Number – Name (for example:  80 – Revenue).  In my SSAS data source, I had the Number field and the Name field available, but I did not have them concatenated together.  That I would need to derive in the report.

A quick reminder about parameters in SSRS:  a parameter looks for 2 fields:  a value, and a label.  This blog entry is focusing on the label that the user sees when he interacts with the parameter.

Option 1 – Derive in the MDX source query

Deriving within the source query is what I did initially.  However, in my situation, I had about 4,000 possible options that could be returned by the parameter query (if the user had full security to the data, like I do).  After adding the WITH MEMBER, this query ran extremely slowly.

To translate my situation into an Adventure Works example, here’s what I did first:

WITH
MEMBER [Measures].[AccountParameter_Label] AS
[Account].[Account Number].CURRENTMEMBER.NAME + " - " + [Account].[Account Type].CURRENTMEMBER.NAME

SELECT NON EMPTY
(
[Measures].[AccountParameter_Label]
)
ON COLUMNS
,NON EMPTY
(
[Account].[Account Number].[Account Number].MEMBERS
,[Account].[Account Type].[Account Type].MEMBERS
)
ON ROWS

FROM [Adventure Works]

Since the performance of the above query was not acceptable, I looked for an alternative.

Option 2 – Derive in the SSRS Dataset

This second option, deriving the field within the Dataset Properties, performed significantly faster.  To set it up:

Step 1:  Create the basic Parameter results query

WITH
MEMBER [Measures].[AccountParameter_Value] AS [Account].[Account Number].CURRENTMEMBER.UNIQUENAME

SELECT NON EMPTY
{
  [Measures].[AccountParameter_Value]
}
ON COLUMNS
, NON EMPTY
(
[Account].[Account Number].[Account Number].MEMBERS
,[Account].[Account Type].[Account Type].MEMBERS
)
ON ROWS

FROM [Adventure Works]

Step 2:  Add a Calculated Field in the “Fields” pane of the Dataset Properties

First, create a Calculated Field:

Then, give it a Field Name and a Field Source.  The expression for the Field Source is:

=Fields!Account_Number.Value & " - " & Fields!Account_Type.Value

Step 3:  Create (or modify) the parameter

What the user sees is the concatenated label we created:

Conclusion

Usually I prefer to derive fields within my dataset, just to keep the logic consolidated in one place.  However, in this particular situation having the Report Server perform the concatenation performed much, much faster for me – sub 1 second as opposed to 30 seconds execution time.  Doing it this way won’t always be the best answer – but it’s a possibility to keep in the ‘ol toolbox.