Month: July 2017

SSRS Report Migration 2012+ – Method 1

Karthik VLeave a comment

As Microsoft goes on rampage shortening their release time and sending not one but 3 versions in the last 6 years (2012, 2014, 2016!), it is time for us to have a look at various methodologies available to migrate reports from one ReportServer to another ReportServer. The reason I have brought this up is because for all my working career, the only tool that I had used to migrate reports is RSSScripter.exe. The original link is archived and is available from the following link – RS Scripter. This one works good for till SQL Server 2008 R2. What about the versions after that?

That’s the answer I want to solve through this post. In my quest to find out how to build a a package ready for deployment and further automating it, one of the first that I have got hands on is this – SSRS Powershell Deploy.

It’s bunch of powershell scripts that can be utilized to deploy the reports. The biggest downside to this method though is you can’t pick and chose the reports to deploy. You either deploy all or none at all. If the solution can be analyzed well enough, we may come up with a workaround for that. For now, let’s deep dive into, how to set the solution up and steps involved in deploying –

Once you open the link, click on ‘SSRS-1.3.0.zip’ folder. Follow the below steps –

1. Download the .zip from https://github.com/timabell/ssrs-powershell-deploy/releases/latest
2. Right-click the zip file in windows explorer, click “properties”, and then click “Unblock”.
3. Create folder ‘Documents\WindowsPowerShell\Modules\’
4. Open up the zip file, copy the SSRS folder, paste it into
`Documents\WindowsPowerShell\Modules\`. (Or somewhere on your
`$env:PSModulePath`)

You can test if you are having the modules imported or not by running a simple powershell command as shown below –

1_SSRSPowershellImport.png

As the names imply, if you want to deploy the SSRS Project (i.e. .rptproj file), then use Publish-SSRSProject else if you want to deploy the SSRS Solution (i.e. .sln file) then use Publish-SSRSSolution.

Using this let’s do one sample report deployment. I will first show a simplest way to deploy i.e. by using pre-filled Configuration data.. I have a project OpenDataReports containing one report ‘Top 10 Products.rdl’.

Once the solution is opened, go to Project-><<ProjectName>> Properties. The properties page opens up.
Perform the following changes as shown below –
Configuration – Set it to Release
TargetServerURL – http://<<MachineName>>/ReportServer<<_InstanceName>&gt;
In addition to that the other settings can be set as well, i.e. TargetDatasetFolder, TargetDataSourceFolder etc.

2_SSRSProjectProperties.png

With the properties set, now right-click on the Project and click on ‘Rebuild’ as shown below. Ensure it succeeds. –

3_SSRSProjectRebuild.png

Go to the project path and /bin/Release folder to ensure all the required reports and Data Sources are present. In my case here are the contents in the folder –

4_SSRSProjectbinReleaseContent.png

Finally the next step is to run the powershell command as shown below –

The command to run is a simple one –

Publish-SSRSProject -Path "<<FilePath>>\OpenDataReports.rptproj" -Configuration Release -Verbose

5_SSRSProjectDeploy.png

That’s the simple way to go about deploying reports. Of course, one need not do the pre-config settings, if you look at all the parameters provided for the script, each of the project parameters can be set at run time. I will posting a example of that in the next post.

Meanwhile, do leave a comment on how deployments are happening in your environments.

 

 

Bye Bye SQL Profiler – Welcome Extended Events

Karthik V3 Comments

SQL Profiler for long has been THE go to tool for tracing the queries and if you are a SQL Developer it would be a miracle if you haven’t used it at all. In every facet of BI stack, this comes into play be it a SSIS package that is currently run, or in understanding a blank SSRS report or a Cube that is getting processed at the background one can hook up a trace as the first line of debugging.

The biggest problem with this though is that it ALWAYS had to be used within a limited time. You extend it longer than intended and all the activities on the SQL Server tend to slow down as it is a resource-intensive operation.

Sensing this I believe, Microsoft first came out with Extended Events with SQL Server 2008 version. It was horrendous to say the least. At that time this had to be entirely done through bunch of scripts, joining multiple tables with addition of XQuery to grab the actual data that we need. The learning curve to get this done was huge. I admit I had read various tutorials, did some practice but when I really wanted to use it I used to get cold feet. Without googling at least twice, this was a no-go and I used to fall back on SQL Profiler.

SQL Server 2012 onward, Microsoft has introduced GUI for Extended Events making it now a real easy breeze to work with. This has now been the de-facto tool that I use for tracing the queries now.

Here is how one can go about setting one up and using it to trace your SQL queries –

Log on to SQL Server and go to Management->Extended Events->Sessions. Right-Click on ‘New Session Wizard’ as shown below –

1_Opening_NewSessionWizard.png

Click ‘Next>’ on the Introduction screen. In the ‘Set Session Properties’ tab give a name to the Session say – All SQL Queries as shown below and click on ‘Next>’

2_SetSessionProperties.png

In ‘Choose Templates’ tab click on ‘Use this event session template:’ and in the drop down select ‘Query Batch Tracking’ as shown below and then click on ‘Next >’ –

3_ChooseTemplate.png

In the ‘Select Events to Capture’ tab across the ‘Selected events’: you can remove error_reported, rpc_completed and only have sql_batch_completed in it as shown below and click on ‘Next >’. You can remove the other two that come by default by just clicking on them and using the ‘<‘ arrow button –

4_SelectEventsToCapture

Keep the defaults as they are for the subsequent screens and click on ‘Finish’ in the ‘Summary’ page. In the ‘Create Event Session’ page that pops-up, to start seeing your results immediately, you are provided with two options. Enable them and click on ‘Close’ as shown below –

5_CreateEventSession.png

Upon doing that the trace is now up and running waiting for the queries to start. Here is the output of showing one query that was run against one database in the server –

6_QueryResult

As can be seen this one is more cleaner and more easier to read without the unnecessary redundant information that used to come along from SQL profiler.

This is just a tip of the iceberg from what we can achieve using Extended Events. There is an ocean out there to explore but for those who are seeking to trace out a query this should be a good start.

 

 

 

 

SSIS Lookup Gotcha – Test Fully

Karthik VLeave a comment

The task I had on my hands was simple. I had to perform lookup on a target table with my source data and get all non matched data. The data that I was fetching for the lookup from target had additional string manipulation done to obtain the required lookup data. I then went on to write a query to fetch that data and testing on a sample subset (using TOP 10) to check for it’s correctness and went on to implement.

I set everything up and when I ran the package the lookup was just now working. The rows that I expected to have a match were simply getting redirected to no-match. When I started debugging, it then became apparent that the query that I had used to retrieve the dataset in the lookup was an incorrect one. This time around I ran the query without the subset and the query failed to get executed showing me the actual error.

Moral of the story is one should not rely on ‘Preview’ results offered from the ‘Lookup’ transform or use a subset when sampling the data. It should be checked to see if the query works for the entire set.

Let me illustrate this with an example.

Below is a sample of Source Data –

 

FileName
Test1.txt
Test3.txt

Here is the data that I am doing a lookup on from a table say dbo.LookupTable (a simple Id and FilePath column) –

1_TargetDataSample
The string manipulation query for this is as you might have already guessed, to get FileName from the ‘FilePath’.

select Id,RIGHT(FilePath, CHARINDEX('\', REVERSE(FilePath)) -1) as 'FileName'
from dbo.LookupTable;<strong>
</strong>

I have a simple Data Flow Task which does the following –

  1. OLE_SRC – Get Source Data – Gets the source data as shown above.
  2. LKP – Get Id – Using the query above, it fetches the Id and FileName. Note that the moment you put that query and go to ‘Columns’, it will throw up an error. For the purpose of this illustration just ignore it and do the mapping.
  3. Trash Destination – A priceless open-sourced transformation from Konesans and a must  have development aid.

Here is how the package looks like after running it-

2_DataFlowTaskContents

As can be seen only 1 row gets shown as matched even though there are 2 matching rows.

The data in this sample illustration is very small but the original data that I had about 100k records and it was difficult to debug on why this error occurred.