We recently delivered a SQL Server 2012 Reporting Services solution to a client with an additional capability for client’s business users to be able to design, develop and publish ad hoc reports using Report Builder 3.0. During unit testing we had one of the Business Analysts (BA) in our team simulate business user experience by attempting to create reports using Report Builder. Although the BA could connect to datasets and design a report accordingly, he couldn’t run the newly designed report from Report Builder. The error that he got is as follows – “Failed to preview report. The permissions granted to user ‘*selectSifiso\Sifiso’ are insufficient for performing this operation” (for the rest of the article the error will be referred to as ‘this error’). The error basically relates to user permissions. I have gone through the web to find possible workarounds to this error and as it turns out this error is not so uncommon in the world of Business Intelligence. Thus, this article will demonstrate how to resolve such an error should you encounter it too.

*For obvious security reasons, I replaced the original BA account with my dummy account.

Requirements

• Software Requirements:
  • Microsoft SQL Server 2012
  • SQL Server Reporting Services
  • Report Builder 3.0
• Conceptual Understanding of:
  • SQL Server Reporting Services
  • Report Builder 3.0
  • Report Manager Configuration

**Please note that the above requirements are only necessary for fixing the user permissions error as it is this article main objective. There are further requirements for developing an SSRS Reporting Solution.

Article

According to this msdn article, in Reporting Services there is a task referred to as Execute Report Definitions (ERD) which allows for the processing of a “report definition that is not currently stored on the report server”.

Now going back to the issue we are trying to resolve, assigning our BA a role that includes the ERD task would obviously solve our problem.

Unfortunately, or fortunately (depending on your user role in a given report server) the ERD task is available to users assigned to system roles i.e. system administrator and system user.

Therefore, in order to prevent a situation whereby you have all business users assigned to system administrator roles, the best practice would be to setup another system role with only an ERD task.

Thereafter you can merely assign business users, in our example the BA, to the role with an ERD task.

In the following steps we will demonstrate the creation of a system role with ERD task and assigning users to that role.

Step 1: Create System Role

1.1. In an administrator mode, Start SQL Server Management Studio (SSMS) and connect to Reporting Services

1.2. Navigate to System folder, collapse it, then right click to System Roles sub-folder and choose New System Role… (If you didnt launch SSMS as an administrator, the option of adding a New System Role would appear greyed-out)

1.3. In an New System Role dialog box, for the purposes of this exercise let’s assign RunReportBuilder into the Name textbox. Then check the Execute Report Definitions task.

Step 2: Assign Users to RunReportBuilder System Role

2.1. Again, preferebly launch your Roport Manager as administrator (in order for that to happen, launch Internet Explorer/preferred browser as administrator)
2.2. Click Site Settings – usually found at top right side menu in browsers.
2.3. Still in your Site Settings page, click Security tab
2.4. The Security tab will return a list of users/groups with their respective roles, click on New Role Assignment

2.5. After a New Role Assignment page loads, on the Group or user name textbox type the account of the user you would like grant access to the ERD task. Thereafter check the the RunReportBuilder role.

Conclusion

You can now inform the user to test run a report using Report Builder to check that the role assignment have taken effect.

Till next time folks, cheers.

Sifiso.

This blog post is in response to a request/comment from Thato in this previous article. Hence, I have taken much of the logic used in the aforementioned article and changed it from static to dynamic file compression. Thus, I am still using SQL Server Integration Services‘s Execute Process Task to compress dynamic files into a .rar format using 7-Zip.

Requirements

• Microsoft Visual Studio 2005 or later
• 7-Zip

Article

Let’s begin by launching Microsoft Visual Studio and create a new Integration Services Project which is located under Business Intelligence Projects category. (If you are using SQL Server 2012 or later, you will have to launch SQL Server Data Tools – SSDT).

After you have assigned a project name, lets add some variables that will be used to store the dynamic content. I setup the variables as follows:

• var_FileName (is of type String and stores file name and extension)
• var_WorkingDIR (is of type String and stores path for working directory)
• var_NewFileName (is of type String and stores a derived archive file name)
• var_Exec (is of type String and stores the path to the executable 7-Zip file)

Now let’s click and drag the ForEach Loop Container (FLC) into Control Flow pane from toolbox (SSIS Toolbox in SSDT).

Right click FLC and click on “Edit”. Under the Collection tab, ensure that Enumerator is Foreach File Enumerator. Setup Directory as expression and map it to the var_WorkingDIR variable. Lastly, make sure that Name and extension is checked under the Retrieve file name group box. In summary, the Collection tab should be configured as below:

Still on FLC, under the Variable Mappings tab map variable var_FileName to the first index 0 as shown below:

The next step is to drag a Script Task (ST) into the FLC. The ST will be used to initialize variable var_NewFileName. Under the Script tab of the ST ensure that you have configured the read/write variables as shown below:

Still on ST, click Edit Script. Reference the System namespace as shown below:

• Using System

The rest of the code in the Main class should be as follows:

The final step is to drag and drop the Execute Process Task (EPT) into Control Flow pane from toolbox (SSIS Toolbox in SSDT).

Right click EPT and click on “Edit”

Under the General Tab, you can assign relevant Name & Description. In this case I have decided to name the task as “EPT – Compress Dynamic Files” whilst Description remains unchanged.

In the Process Tab, the EPT properties must be set as follows:

• RequiredFullFileName : True
• FailTaskIfReturnCodeIsNotSuccessValue : True
• SuccessValue : 0
• Timeout : 0
• WindowStyle : Hidden

Still on the EPT, we now need to configure expressions. Thus the expression tab should look as follows:

Basically some of the noticeable things we have done above is to hide the exe window that appears when the file is being compressed. The Executable and WorkingDirectory properties are set via variables. The Arguments property is also set using variables and we have further hard coded the extension of the compressed file to be that of type rar. Luckily 7-Zip supports various file compression types, so you can change the rar extension to an extension of your choice. Of course depending on the extension you have provided the size of the new compressed file will vary.

PS: You can download a copy of this package here.

It’s that simple.

Till next time folks, cheers.

Sifiso.

CodeProject

The growing popularity of the use of social networks by businesses indicate a possible change in ETL requirements. Part of this change is that developers had to revise existing ETL model such that it caters for sourcing of data off the internet in addition to the traditional flat files or OLE/ODBC Connections. In this article, I will demonstrate extracting user tweets using Twitter’s REST API inside an HTTP Connection Manager.

Requirements

• Microsoft Visual Studio 2005 or later
• SQL Server 2005 or later
• Microsoft .NET Framework 3.5 or later
• Tweeter User Account that has posted at least 100 tweets
• An Active Internet Connection

Article

Let’s begin by launching Microsoft Visual Studio and create a new Integration Services Project which is located under Business Intelligence Projects category. (If you are using SQL Server 2012 or later, you will have to launch SQL Server Data Tools – SSDT).

After you have assigned a project name, proceed to click and drag the Data Flow Task (DFT) into Control Flow pane from toolbox (SSIS Toolbox in SSDT). I decided to name my DFT as DFT – Retrieve Tweets

Add the following two connections:

• HTTP Connection Manager
• OLE DB Connection

I have configured my HTTP Connection as follows:

• Server URL = http://api.twitter.com/1/statuses/user_timeline.rss?screen_name=mafiswana&count=100

Note that you can replace the value of parameter screen_name with your twitter user account instead of mafiswana.

I have configured my OLE DB Connection as follows:

• Server = Localhost
• Database Name = selectSifiso

So far the package should look as below:

Let’s go back and edit the Data Flow Task.

Add a Script Component (SC) which is located under Data Flow Transformations.

Select Source as a script component type.

Edit the script SC and click on the Connection Managers tab on the left of the Script Transformation Editor. Under the Connection Manager column click to select the HTTP Connection Manager (it should be the only connection available).

Let’s move on to the Inputs and Outputs tab in the Script Transformation Editor. In here we will configure the source output columns.

Collapse Output 0 node and click on Output Columns. Click Add Column and the following columns with properties configured as below:

• Name = sS_Tweets; DataType = string [DT_STR]; Length = 200
• Name = sS_TwitterDate; DataType = string [DT_STR]; Length = 200

Now let’s move on to the Script tab in the Script Transformation Editor.

In Object Explorer, right click on References and Click Add. Under .NET tab add the following references:

• System.ServiceModel
• System.ServiceModel.Web

If one or none of above references are not available for selection, please ensure that you have .NET 3.5 Framework selected. You can do that by clicking on Properties – Application – Target Framework – .NET Framework 3.5

In addition to the already referenced namespaces, add the following namespaces:

• using System.Xml;
• using System.ServiceModel.Syndication;

Declare the following variables in the public class ScriptMain : UserComponent

• private SyndicationFeed sS_Tweets = null;
• private XmlReader sS_XmlReader = null;

Under the PreExecute() method, add the following:

• sS_XmlReader = XmlReader.Create(Connections.Connection.ConnectionString);
• sS_Tweets = SyndicationFeed.Load(sS_XmlReader);

Under the public override void CreateNewOutputRows() method, add the following:
if (sS_Tweets != null)

{

foreach (var item in sS_Tweets.Items)

{

Output0Buffer.AddRow();

Output0Buffer.sS_Tweets= item.Title.Text;

Output0Buffer.sS_TwitterDate= item.PublishDate.ToString();

}

Output0Buffer.SetEndOfRowset();

}

The complete script code can be found here – tweets_ssis

Now that we have configured the Script Component as Source let us add an OLE DB Destination Component (ODD) and connect Script Component to the Destination Component.

I have configured the OLE DB connection manager of the ODD to use the selectSifiso connection. The data access mode is a Table or view – fast load. Under Name of the table or the view, click New and create and output table. Click on Mappings tab and ensure that the source-to-destination mappings are correct.

Your complete package data flow task should look as follows:

Conclusion

All left to do is to run the package and your tweets will be extracted and stored in SQL Server.

It’s that simple.

Till next time folks, cheers.

Sifiso

CodeProject
 

In this blog post I will demonstrate using SQL Server Integration Services‘s Execute Process Task to compress a csv file into a zip format using 7-Zip.

Requirements

• Microsoft Visual Studio 2005 or later
• 7-Zip
• 
  SIFISO_Test

Article

Recently I have been involved in a project whereby I have been required to extract data into csv files, compress the files into a zip format and email the newly compressed files. This blog post is a first of several posts to come whereby I will be demonstrating how to fulfill such a requirement.

Let’s begin by launching Microsoft Visual Studio and create a new Integration Services Project which is located under Business Intelligence Projects category. (If you are using SQL Server 2012 or later, you will have to launch SQL Server Data Tools – SSDT).

After you have assigned a project name, proceed to click and drag the Execute Process Task (EPT) into Control Flow pane from toolbox (SSIS Toolbox in SSDT).

Right click EPT and click on “Edit”

Under the General Tab, you can assign relevant Name & Description. In this case I have decided to name the task as “EPT – Compress File” whilst Description remains unchanged.

In the Process Tab, the EPT properties must be set as follows:

• RequiredFullFileName : True
• Executable : C:\Program Files\7-Zip\7zG.exe
• Arguments: a “SIFISO_Test.zip” “SIFISO_Test.csv”
• WorkingDirectory: C:\Test\Excel\20120930\
• FailTaskIfReturnCodeIsNotSuccessValue : True
• SuccessValue : 0
• Timeout : 0
• WindowStyle : Hidden

Basically what we have done above is, among other things, set the Executable property to the .exe location of the compressision software we are using; set the Arguments such that file “SIFISO_Test.csv” is compressed into “SIFISO_Test.zip”; specified the working directory where the csv file will be located and zip file created; hid the exe window that appears when the file is being compressed.

It’s that simple.

Till next time folks, cheers.

Sifiso.

CodeProject

SQL Server Integration Services 2012 comes with a new API for scripting packages which is called MOM – Managed Object Model. In this article, I will demonstrate the scripting of Integration Services Catalog packages using C#.

Requirements

• Microsoft Visual Studio 2010 or later

Article

We begin by launching Microsoft Visual Studio and create a new Integration Services Project which is located under Business Intelligence Projects category.

After you have assigned a project name, proceed to click and drag the Script Task into Control Flow pane from toolbox.

Right click the script task and click on “Edit”

Under the Script Task Editor change the “ScriptLanguage” to “Microsoft Visual C# 2010″.

In Project Explorer, ensure the following references are added:

• System
• Microsoft.SqlServer.Management.IntegrationServices
• Microsoft.SqlServer.Management.Smo
• Microsoft.SqlServer.Dts.Runtime

It is possible that you might not find the Microsoft.SqlServer.Management.IntegrationServices.dll under .NET references in visual studio, in which case I suggest you click here for an article on referencing the missing assembly file.

Back to the code window, ensure that the following namespaces are declared:

• using System;
• using Microsoft.SqlServer.Management.IntegrationServices;
• using Microsoft.SqlServer.Management.Smo;
• using Microsoft.SqlServer.Dts.Runtime;

Then under your main class create an instance of SMO Server as well as integration services object as shown below:

The next part is to create an SSIS Catalog database followed by CatalogFolder, Project, then SSIS packages. It is possible, however, that you might have (or an administrator) created the SSIS Catalog. It is also possible that the CatalogFolder might have been created already. In order to address these possible scenarios, I have created a function with a set of parameters that can be called based on a particular scenario. The function is called:

• sS_SsisObjCreate(int condition, IntegrationServices sS_Is, Catalog sS_Ct, CatalogFolder sS_Cf)

Voilà! Now you can open SQL Server Management Studio and connect to the Server you provided in the above and you should find your newly created packages as shown below:

Cheers.

Sifiso.

CodeProject