Friday, March 30, 2007

How to create a Report Execution Snapshot with SSIS

This last week I have been searching the internet for a solution on how to create a Reporting Services Report Execution Snapshot with SQL Server Integration Services. SQL Server Reporting Services mainly runs on XML and SOAP methods. So calling a RS function or method is relatively simple. SSIS even has a web service task that theoretically would work perfect for this situation. I tried referencing RS .wsdl file and SSIS wouldn't accept the format. After some more research I realized the web service task is pretty much useless when connecting to Reporting Services.

Then I looked at the scripting task in SSIS and that seemed too complicated. To get it to work I would have to create a RS proxy server and write a good amount of code. It seemed too difficult for what seemed to be an easy task.

I then found the rs utility. You can write reporting services script files (.rss) using to call Reporting Services web service methods and run these files from the command line. By putting the command line command in a batch file I am able to create an Execute Process task in SSIS and call the batch file. It was that easy.

So here is my solution:

Public Sub Main()
'Run this in the command line: rs -i reportsnapshot.rss -s http://server/reportserver -v reportPathParameter=%1

Dim reportPath as string = reportPathParameter
Dim schedule AS NoSchedule
Dim EnableManualSnapshotCreation as Boolean = true
Dim KeepExecutionSnapshots as Boolean = false

'Sets the credentials
rs.Credentials = System.Net.CredentialCache.DefaultCredentials

'Sets the Report History options
rs.SetReportHistoryOptions(reportPath, EnableManualSnapshotCreation, KeepExecutionSnapshots, schedule)

'Updates the Report Snapshot

End Sub

rem change directory
cd to where ever you have the .rss file saved

rem This batch file calls Reporting Services Script 'reportsnapshot.rss' and passes the report path which is the %1 variable
rs -i reportsnapshot.rss -s http://server/reportserver -v reportPathParameter=%1

rem This will indicate whether there is an error. Error = 1, Non Error =0. This is used in the Process Task to indicate whether the task fails or succeeds.

Execute Process Task
Executable: Path to where you have the .bat file saved
Arguments: The path to the report (ex. "/folder/report") Note: You need the first "/". This will be passed into the batch file as the %1 parameter.
FailTaskIfReturnCodeIsNotSuccessValue: True
SuccessValue: 0 (The EXIT [ERRORLEVEL] will return a 0 or 1. 1 is fail. This will make it so the task will fail if the batch file encounters an error.)

If you are in need of clarification or if you have questions about this you can e-mail me!

Disapointing March Madness

So I'm a little disappointed with this year's NCAA tournament. There just weren't enough upsets and I'm sick of seeing Florida win everything. I hope UCLA creams Florida. Besides, Ohio State should have lost to Xavier in the 2nd round. Had only Xavier made that last free throw, Ohio State would not be in the final four. Needless to say, my bracket is full of red font and slashes. Maybe next year!

Thursday, March 22, 2007

Internet Marketing

So I launched a free marketing campaign yesterday for I tested this tactic out last week and with in seven days I increased my visits by 18 with just one ad. I even got a sale (my second) and made $18.80. All very exciting. In fact this campaign is much better then the pay-per-click campaign that I ran a month ago. First of all it is very targeted, and it is free.

So what will be the impact? I launched 18 adds in about 45 minutes yesterday. So far today I have had 27 visits and 106 pageviews. My highest days are 32 visits and 158 page views. I think I'm on track to break that record today. I have set up a system/process to generate these ads very quickly and I plan on expanding the ads into other products. Hopefully, the net impact will be more sales.

In one week, with one ad, I generated 18 visits and made $18.80 (1 sale only). If I can maintain those same numbers for each ad I should be able to generate about 324 visits and $338.00 in net sales per week. All without spending a dime on advertising!

I am also rapidly adding new features and functionality to the site. All in hopes of increasing conversion, customer satisfaction, and search engine ranking. In the mean time though, this marketing campaign could become very successful.

Thursday, March 15, 2007

So in my web analytics class we are beginning a new and exciting project. We will be analyzing and making changes to This is a small commerce site that sells 3-5 products and generates about $12,000 a year in revenue. Our task is to anlalyze the site and make recommendations on how to improve the site's performance.

Just by initially evaluating the site I have a few ideas on how we can improve the site:

Search Engine Optimization:

  • Put a meta keywords tag on the top of each page. Make sure these keywords match content of the page. Right now there are no keywords.

  • Have a unique title for each page. Right now, the title for every page is just "Home Page".

Site Design

  • Redesign the left navigation panel. Make it more modern and user friendly.

  • Create a new logo.

  • Redesign the format so that it is not so plain. Add some structure such as lines or boxes. Maybe consider a consistent font.

As we dig into the stats of the site I'm sure we'll have some more suggestions.