Data Warehouse latency is often a complaint I have heard from end users when trying to access data via either Reporting Services reports or Excel.  Generally, I promise 24 hour latency unless the job mandates updates hourly or even sooner.  Screenshots for this blog are available at my regular blog site

With these complaints in mind I decided to create a report that could kick off the SQL Agent job that processed my Data Warehouse load and Cube update. It is a pretty simple report to create. Here are the steps I did:

Step One

Create a Data Source that points to MSDB on the server that the SQL Agent job that you want to run is located.

Step Two

Create a Dataset that runs the system stored procedure sp_start_job with the name of the job.

Step Three

Add some text! Let the user know what’s going on after they click on the report otherwise it will just show a blank report. Drag a textbox over and add the appropriate text.

Deploy the report and test!

There are some circumstances where you would not want to use this method:

· Running the job in the middle of the day could severely cripple a transactional system that the Data Warehouse load pulls from.

· The job takes longer than just a few minutes to process. Remember you are trying to improve latency. You don’t want to expose a poorly performing load process (even if the performance time is due to the size of the load not bad code)

· You haven’t trained your end users in what the report does. You don’t want end users clicking this report over and over again because it is running a major process. 

Reports that use an Analysis Services cube as a data source can often have performance problems during pre-execution.  Before you even see the classic Report is being generated sign the report is slow to start.

The most common reason I’ve found for this performance lag has to do with parameters that are filtering the main dataset.  For example, you have a report that uses a sales cube and you want the user to be able to select the date range that the report will return results for.  This would mean you would add a filter to the dataset using a date dimension and check the box to make it a parameter.

The reason this can be a performance problem is because you’ve now added a parameter that includes every date that exist in your date dimension.  So before the report can even begin to generate it has to populate all the records in the parameter first.  Depending on how many dates you’re storing in the dimension this could be a major issue.  Not to mention if you are using cascading parameters that depend on the previous parameters results. 

It’s likely the end users of the report aren’t interested in seeing 70 years worth of data.  It’s much more likely that the user would want a rolling years worth of dates to choose from.  So here’s the solution:

Step One

Show Hidden Datasets by right clicking on the report Data Source and clicking Show Hidden Datasets.  When you check to include parameters on a dataset it creates hidden datasets behind the scenes. 

Step Two

Open the properties to the newly shown dataset that is used for the date parameter and select Query Designer.  Add the following MDX where clause to the query.  This query may vary greatly depending on the design of your cube.  This will just give you a starting point.

Now when I view this report I will only have the past years worth of dates available in the date parameter.  Lag(366) is bringing back all dates more than a year old to Lag(0), which is the current date.  Again this could be very different for you depending on how the date dimension is setup in your cube.

When developing Reporting Services reports that use Analysis Services as a data source you may find that it is difficult to dynamically set default parameter that are passed into the dataset being used.  It turns out this is a lot simpler to do then many people think.  In this post I’ll walk you through the steps of setting a default date value in a dataset’s parameter. 

Step One

When creating a dataset that uses Analysis Services you will select Query Designer and then drag over the measures and dimension attributes that are needed for the report where it says “Drag levels or measures here to add to the query” .  To add a parameter to the dataset you need to add a filter above where you just dragged over your report fields.  In my example I am filtering by the year 2008.  If I want to make this a parameter I just check Parameter box.  Now when you hit OK and then OK again to return to your report you will have a new parameter added.  When I made 2008 the Filter Expression it automatically sets that value as my default parameter value.

Step Two

What I really want to do is setup this parameter so that it dynamically changes.  In my report I want it to always show the current year by default in the parameter.  So instead of 2008 the report should display 2009 and automatically change to 2010 on January 1st.  You can do this using the SSRS expression language in the default value of the expression.  The tricky part about it is that the default value of the parameter is using a MDX value.  If you open the report parameter and select the Default Values tab you can change the value used for this parameter to use a SSRS expression so it dynamically changes to the current year no matter what the date is.  The expression used for my example was ="[Year].[Year].&["+CSTR(Year(Today))+"]".  This is using the Today function and only returning back the year using the Year function.  Lastly it converts the value into a string. 

This is a very basic example but I could use a similar expression if I had used a date hierarchy instead of just the year attribute.  The key is to find out how the MDX that is being passed into the value looks like and then you can duplicate them using the SSRS expression language.  Now every time I run the report It will have the current year populated by default in the parameter.

Today I gave a two part session at SQL Saturday in Orlando.  When originally planning this years event Andy Warren said many people who have attended previous events were interested in sessions that were split into two parts.  So I decided to submit an Introduction to SSIS that was split into a part 1 and 2. 

I was a little unsure on how to split an Intro to SSIS session into two parts but it turned out I actually preferred it this way.  I decided to have the first session cover general understanding of the development tool, Control Flow, and Event Handlers.  The second session was on the Data Flow, Variables, and deployment.  I think this worked out well and it made me not feel as rushed on the topics. 

Andy knows how to run these events and I think it’s interesting to see how the event has transformed over the years now.  This event as a whole was stacked with talented speakers.  I think this may have been one of the most talented group of speakers I’ve seen to date at a SQL Saturday.  Not only does SQL Saturday have seasoned speakers but it is an opportunity for new and local speakers to share with others the knowledge they’ve gained from their experience.  I think a lot of people forget about how much great information they’re getting out of this free event.  Make sure you make it to the next event near you.  www.sqlsaturday.com

It’s that time again!  SQL Saturday has returned to Orlando with an all star group of speakers http://www.sqlsaturday.com/schedule.aspx.  The event will be October 17th and you can find all the important information at www.sqlsaturday.com.  If you’ve never attended one of these free events make sure you make this one.  It’s well worth attending even with a couple hours of a drive like me!  The topic I will be speaking on is introduction to SSIS in a two part session.  Hope to see everyone there!

SQL Server 2008 has a new Change Data Capture feature that allows you to track Inserts, Updates, and Deletes on a table. A lot of the features I’ve used in the past to detect changes are generally very expensive operations so I’m always looking for alternatives.  There are several caveats you need to know about before attempting to use CDC.  View this blog with screenshots at my regular blog http://blogs.pragmaticworks.com/devin_knight/.

1.  You must be using either Enterprise, Developer, or Evaluation editions of SQL Server 2008

2.  You must have a sysadmin fixed server role

3.  You must have a db_owner fixed database role

4.  CDC can only be applied to user databases

5.  SQL Server Agent must be running

All of the stored procedures used for CDC can be found on msdn http://msdn.microsoft.com/en-us/library/bb500244.aspx.

Step One

CDC must be enabled on each database you decide to check for changes.  To enable CDC on a database using the following script:

Use AdventureWorks2008
exec sys.sp_cdc_enable_db
select name, is_cdc_enabled from sys.databases

The select statement shows which databases have Change Data Capture enabled.

Step Two

Create a table to test CDC:

Create Table dbo.CDC_Test
(ID int,
Description varchar (50))

Step Three

CDC must also be enable on each individual table:

exec sys.sp_cdc_enable_table 
    @source_schema = 'dbo', 
    @source_name = 'CDC_Test',
    @role_name = 'CDCRole'
select name, is_tracked_by_cdc from sys.tables

Here I’ve created a new role called “CDCrole” that didn’t previously exist.  This stored procedure has many parameters but I’ve only used the required ones here.  View all the other available parameters here http://msdn.microsoft.com/en-us/library/bb522475.aspx.  The select statement displays all the tables that have Change Data Capture enabled.  You may have also noticed that two SQL Agent Jobs were created (cdc.AdventureWorks2008.capture – watches for changes in transaction logs, cdc.AdventureWorks2008.cleanup – automates cleanup). 

Step Four

Insert a new record into the test table:

INSERT INTO AdventureWorks2008.dbo.CDC_Test
           (ID
           ,Description)
    Values(1, 'CDC Test' )

Step Five

Update the record in the table:

UPDATE AdventureWorks2008.dbo.CDC_Test
   SET ID = 3
      ,Description = 'NewTest'
 WHERE ID = 1

Step Six

Use the function created by CDC to return which columns are Inserted, Deleted, or Updated

DECLARE @from_lsn binary(10), @to_lsn binary(10)
SET @from_lsn =
   sys.fn_cdc_get_min_lsn('dbo_CDC_Test')
SET @to_lsn   = sys.fn_cdc_get_max_lsn()
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_CDC_Test
  (@from_lsn, @to_lsn, N'all')

How is this useful?  The _$operation column is used to indicate the change (1 = Delete, 2 = Insert, 3 = Update with row filter option set to “all update old”, 4 = More typical Update).  So my example shows one insert and one update.

Step Seven

Use the following procedure to view all tables with CDC enabled.

exec sys.sp_cdc_help_change_data_capture

Step Eight

Disable Change Data Capture on table and database.  To disable just the individual table run the following:

exec sys.sp_cdc_disable_table 
  @source_schema = 'dbo', 
  @source_name = 'CDC_Test',
  @capture_instance='dbo_CDC_Test'

Run the following to disable CDC for the database:

exec sys.sp_cdc_disable_db

You don’t need to do both if you want to disable the table and database just run the disable database stored procedure.  You will also notice the SQL Agent Jobs are removed when the last procedure is ran.

The SSIS Package Store is a nice option available for storing SSIS packages.  The Package Store lists packages stored on the server in the MSDB folder and all packages stored on the file system as long as they are in the default folder (C:\Program Files\Microsoft SQL Server\100\DTS\Packages for SQL 2008) in the File System folder.  View my regular blog to see screenshots included http://blogs.pragmaticworks.com/devin_knight/.

It is possible to adjust the Package Store to look for packages in different file system folders or different server instances.  To do this you must change the MsDtsSrvr.ini.xml file in the C:\Program Files\Microsoft SQL Server\100\DTS\Binn (folder is in SQL 2008 only). 

It’s a pretty simple change that you can even use notepad to do.  Here are the steps to follow to add a new File System folder to the Package Store.

1. Navigate to the file C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml and open it notepad or any program that can edit an xml file.

2. To add a second File System folder Package Store add the following underlined code with the file path you want between <StorePath> and </StorePath> (Be careful when making these changes.  You may want to make a copy of the file before making changes):

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
  <TopLevelFolders>
    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>.</ServerName>
    </Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>My Packages</Name>
      <StorePath>C:\SSIS</StorePath>
    </Folder>
  </TopLevelFolders>
</DtsServiceConfiguration>

3. If you want to change to a different server instance then type the new server instance between <ServerName> and </ServerName>.

4. After you have made these changes and saved the file restart the SQL Server Integration Services in the SQL Server Configuration Manager.

Your new folder will not appear until you restart the service.  You can connect to Integration Services in Management Studio and find your new folder.

I recently worked on a project that involved loading a Data Warehouse from a DB2 source.  In this project we used the Microsoft OLE DB Provider for DB2 Data Provider for the ETL process in SSIS. 

Unfortunately, one of the limitations to using this Data Provider is the the OLE DB Source does not allow you to pass in parameters which I do frequently in Data Warehouses for incremental loading.  The work around I did for this was to store the query I wanted to use in my source in a table on the SQL Server side with a place holder for for my parameter, something like:

Select *

From DimEmployee

Where RowStartDate > %parameter% 

Then I used a stored procedure with a REPLACE function to change my placeholder with the value I want in SSIS. 

After doing the prep work of storing the query and writing the stored procedure I used SSIS to execute the stored procedure in an Execute SQL Task and pass in the parameter I want using the task.  Store the results in a variable and then you can use that variable as your source query in the OLE DB Source.

Opening DTS packages on a 2008 SQL Server requires several steps to be able to view the packages in a DTS designer.  Here’s a link to these steps on a 32-bit machine or on a 64-bit machine.  After following these step you will be able to open your DTS packages in the DTS Designer.

These steps are great for opening DTS packages but I ran into a problem recently where I was not able to import packages on my server which shouldn’t require as many prerequisites like opening packages does.

When I attempted to import a package I received the following error:

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. (Microsoft OLE DB Provider for SQL Server)

After doing some research I found the problem in a blog by Xinwei Hong.  He has several possible problems listed, which luckily was solved by his first solution.  It is was a simple fix and here are the steps I followed to fix my problem:

1. Open the SQL Server Configuration Manager

2.  Expand on SQL Server Network Configuration and select Protocols for MSSQLSERVER

3.  Enable TCP/IP.  Not sure why this was disabled to begin with but that was my problem

A very common need in SSIS is to check to see if a file exist before you run what could be a very long process in your package.  There are no native tasks inside SSIS that can do this check but you can accomplish this using a Script Task.  Here are the steps to check to see if a file exist.  To view this blog with screenshots visit my regular blog at http://blogs.pragmaticworks.com/devin_knight/.

1.  Setup two variables.  The variable named strFileLocation has a string data type with the value being the location of the file I want to check for.  The variable bolFileExists has a boolean data type with the value changing based on whether the file exist or not.  If the file is found the value will be changed to True otherwise it stays False.

2.  Use a Script Task in the Control Flow and set the ReadOnlyVariables to use the strFileLocation variable and the ReadWriteVariables to use the bolFileExists variable then select Edit Script.

3.  There are two methods for writing this script.  The first method you must first add the namespace System.IO.  The second method does not require this.

4.  Scroll down the editor until you find the green commented out text that says Add your code here.  Replace that with the following code then save and close the editor :

Dts.Variables("bolFileExists").Value = File.Exists(Dts.Variables("strFileLocation").Value)

The second option for the script is (Remember this option is not using the Imports System.IO step) :

Dts.Variables("bolFileExists").Value = My.Computer.FileSystem.FileExists(Dts.Variables("strFileLocation").Value) 

5.  Now that you have the script done you can use things more familiar to you in SSIS like precedence constraints with expressions to get the desired results.  Connect the completed Script Task to a path that you want the package to move if the file does exist.  Open the precedence constraints editor by double clicking on the line. change the Evaluation operation to Expression and Constraint and add the expression @bolFileExists==True.  Remember the double equals is a comparison.  If it was a single equal sign it would be trying to set the value of the variable to True.  Click OK once complete.  If you want a path for if the file is not found then follow the same steps with the precedence constraint but the expression should be @bolFileExists==False.

I’m using script tasks just as placeholders here for how the rest of the package may look.  If my file did exist it would go down the left path.  If it did not exist my package flow would go right.

While in the process of developing SSIS packages it’s likely you will deal with errors occasionally unless you’re the Superstar developer that never makes mistakes.  Of course your first steps to deciphering the problem is to look at either the progress/execution results tab in BIDS.  The problem with viewing error messages here is that more than likely the error you receive does not fit on the single line and goes off the screen.  So what do you do?  Right-click select Copy Message Text and paste it in something like the notepad is what most people do.  This is an quick way to view a full error message, but I’m going to share with you another option that can be used during development to view your full error message.  To read this blog post with screenshots visit my regular blog at http://blogs.pragmaticworks.com/devin_knight/.

This method involves using a little bit of scripting and taking advantage of Event Handlers.

1.  Create an event handler on the package level that will kick off a Script Task when an OnError event occurs.

2.  Drag a Script Task from the Toolbox onto the Event Handler design surface then double-click to open it’s editor.

3.  Select the System::ErrorDescription Variable From the ReadOnlyVariables list.  (This variable is only available in the Event Handler tab)  Then click Edit Script.

4.  Once the Editor opens scroll down to where it says Add your code here and replace that line with the following code:

MsgBox(Dts.Variables("ErrorDescription").Value)

This code is looking waiting for this system variable ErrorDescription to be populated and when it is a message box will appear with it’s content.

Now when your package is run if it encounters an error that causes the package to fail you will receive a message box with the same error you would find in the progress/execution results tab.  If you have multiple errors you will receive multiple message boxes.  This message can easily be copied from the message box by using the CTRL+C if you will wish to copy this to a notepad.

Saturday I had the great opportunity to speak at SQL Saturday in South Florida.  The event was great.  It was very well organized and the facility was perfect for the amount of people.  There was a total of about 350 people who made it.   

I spoke on Performance Tuning SSIS, which is one of my favorite topics.  Due to an absent speaker I also took over another session after lunch which was an Introduction to SSIS.  I wasn’t really prepared to do this session but I think it still went well.  If you were in my Performance Tuning session here is a link to download my slide deck and packages used in demos.

http://www.pragmaticworks.com/DevinKnight/PerformanceTuning.zip

Here are some steps to fixing an issue I ran into with Window 7 and Reporting Services 2008.  The problem was when I attempted to access the Report Server or Report Manager from my browser I received the error HTTP 404 Not Found.  I didn’t find a direct answer on the web for this but I did see a lot of people being directed to change the port used to 8080 without any explanation why.  I don’t think this is an issue exclusive to Windows 7 but it happened immediately after installing it (maybe someone could shed some light on this part for me).  I didn’t have this same problem while using Vista.  I did the following steps to solve the problem.

1.  Open Reporting Services Configuration Manager

2.  Changed the Web Service URL to use TCP Port 8080 instead of the default 80.

3.  Changed the Report Manager URL Advanced properties to use TCP Port 8080 instead of the default 80.

4.  Restarted the service and I was good to go.

I’m not sure why this fixed the problem maybe someone can leave me a comment to help me out on that.

Are you tired of the same old Microsoft news feed when you open Visual Studio (BIDS)?  Why not go ahead and update it so you every time you load VS you get news that you’re actually interested in.  I’ve changed my news feed to display all the SQL Server Central blogs so now instead of ignoring the news feed I actually read through each.  I’m sure many people have blogged about this before but I want to give credit to Scott Gleason for showing me this nice feature several months ago.  Read my regular blog to see these steps with screenshots. http://blogs.pragmaticworks.com/devin_knight/

1.  Open Visual Studio (BIDS)

2.  Use the Tools Menu and select Options

3.  Under the Environment list select Startup

4.  Type in the news feed of your choice.  I used SQL Server Central blogs http://www.sqlservercentral.com/blogs/MainFeed.aspx 

For those that are new to SSIS, using expressions may seem very foreign and unpleasant.  Not only is it a new language for you to learn but new data types to understand as well.  If you can get past this unwelcoming feelings you’ll realize using expressions is a great method for making your SSIS packages dynamic.  Read my regular blog if you would like to see screenshots with this post http://blogs.pragmaticworks.com/devin_knight/.

I will tell you about the basics you need to know before starting to use expressions.  If you’re interested in a crash course on writing expressions read this White Paper by Brian Knight.  http://www.pragmaticworks.com/downloads/ssisexpressionswhitepaper/

Using expressions isn’t nearly as difficult as many believe.  Provided to you is the Expression Builder that will save many hours of research on the web trying to determine which function you need for what you’re trying to accomplish.  Luckily you can skip this research step because all the possible functions you can use are located in the Expressions Builder.

There are a variety of places in SSIS where you have the ability to make a package dynamic using expressions.  Connection managers and tasks have options for building expressions to dynamically change the value of a property.  For example, a very common use for expression in connection managers involves using a Foreach Loop Container with the Foreach File Enumerator.  This involves looping through a collection of files and performing the same tasks (usually loading to a table) to each file.  You would use expressions in this case to change the value of the connection string property in the connection manager that stores the file location so after each iteration of the loop you’re loading a new file.

Expressions are not exclusive to connection managers and tasks.  Variables also have the ability to use expression to change how they will evaluate.  Selecting a variable and changing the EvaluateAsExpression property of the variable to True will all for this variable to dynamically change at runtime.  This opens up many possibilities to any object in your package that may want to use variables with.

Of course it must be mentioned that there are Data Flow transforms that require basic knowledge of expressions.  Derived Column and Conditional Split are just a couple of the transforms that require the expressions language to do business.  Using expressions in transforms like these are not necessarily making your package dynamic but they could make your data dynamic.  Expression in transforms can restrict what rows are loaded, change the value in rows, or even replace the value in rows.

I will provide future blogs with more details on expressons but it's always nice to have a little understanding before jumping right in.