Dynamic Named Sets in SQL Server 2008 Analysis Services (SSAS 2008)

Introduction:

SSAS is having a nice & useful feature of defining the calculations (Calculated Members & Named Sets). These calculations can then be used in MDX or by any other client tools etc. These calculations come very handy to allow the user to use them to analyse their data with required parameters, attributes, & custom calculations. Using Named Sets, we can predefine the member(s) which are based upon certain conditions and then user can directly use them to see the measure/data against those members.

Let’s say we want to offer the user with a set of last 5 days/dates which contains transactional data, even though there may be lot of other later dates but with no fact data.

So for this we can create a regular Named Set on Calculation tab, something like:

 CREATE SET ActualSalePeriods AS

Tail

(      NonEmpty([Date Order].[Calendar].[Month].[Date].Members,

                        Measures.[Sales Amount]),

5);  

 Every calculated member/named set created here is session scoped & created with keyword ‘Create’ behind the scene.

 These Named Sets are evaluated and created every time the cube is processed so at the time of next process, if cube gets data with new dates, it will refresh the result set of this create Named Set as well accordingly. So we can say, they are static is nature and contents will remain intact until cube is reprocessed.

Dynamic Named Set:

These regular Named Sets are Static in Nature (only possible option till 2005 version) to improve the query performance by not automatically refreshing/querying it against cube every time. And in fact, works well in the situations like getting latest 5 dates as mentioned above since as in when new dates get added with data in the cube, it will be reprocessed & above set would also be updated with latest members then.

However, this may result a serious issue with respect to the actual requirement which (let’s say) is to get the latest Top 10 employees with highest sales on monthly basis. Created Named Set (static), evaluated at the time of processing, will give the correct result set with those employees. But now in case user wants to slice/dice the result set based upon some other dimension say region, product etc, again user will be offered with the same result set generated earlier at the time of processing, i.e. wrong data information.

 To cope up with such issues/requirements, SSAS 2008 has come up with one more option for creating the Named Set. It’s named as ‘Dynamic’. So developer can either select Static (default) to have the regular Named Set or can select Dynamic to give the Named Set a dynamic behaviour. And by selecting Dynamic option, SSAS will add a keyword ‘Dynamic’ before SET keyword to make it dynamic named set.

Dynamic sets are not calculated once. They are calculated before each & every query, and very important, in the context of that’s query WHERE clause and sub-selects. 

Thus the above requirement of getting Top 10 sales employees will work absolutely fine with Dynamic Named Set.

 Imp: Dynamic named set not only resolves the above mentioned issue but also adds capability to MDX which was not able to generate the desired results with regular named set. This is in regards with the issue while using the multi-select dimensions in the sub-select rather than where clause (which is case with excel wherein it always converts where to sub-select). So using Dynamic sets, multi-select statements are evaluated properly even being used in sub-select statements.

How to remove duplicate records from Flat File Source (SSIS)

Problem:

Since populating the data warehouse or any other database may involve various disparate source-types like SQL Server, Excel spreadsheets, DB2 etc. It can also be a Flat File/CSV file source as well. 

In case of data sources like SQL Server, Oracle wherein we generally get the data by querying the tables, views etc, we can actually put the logic to remove the duplicate probably by selecting the DISTINCT values or so. But in case of a Flat File data source we don’t have much control in terms of querying the data from it and still we want the duplicate records to be removed before loading them into the target database/file.

 Solution:

Let’s take an example where we have a flat file with four columns (A,B,C,D) and some records (100,200,300,400 etc) as shown below:

 Source File

 

 

 

 

And now say we want all of the columns to be loading into the target database, but with duplicate records (lines) removed.

Approach:

 

 

 

 

 

 

 

 

 

Steps:

1. Configure the Flat File Source as we required by selecting the file, delimiters (columns & header) etc.

2. Now drag ‘Aggregate’ transformation following Flat File Source and configure it to have ‘Group By’ on the deciding columns/fields for the record to be unique.

3. Configure destination by mapping the columns from Aggregate transformation to the target database/file.

Run the package, and you will see no duplicate record inserted into the target database/file.

Dest File

How to draw a line based upon data values in Dundas Chart – Customizing Dundas Chart using Code Editor (SSRS)

Introduction:

Dundas has come up with the adding new abilities to Reporting Services (SSRS) in terms of more featured & customizable controls. Not only they look better with lot of UI options to customize, but also have powerful features which are very interesting and useful for analysis. The most popular among these is Dundas Chart control. Existing SSRS charts can also be into Dundas chart just by few right clicks.

Though there are lot of in-built options to select which are quite enough to fulfil the reporting requirements, it also lets developer to apply his/her imagination in designing the chart with the help of writing custom code. And this can be done using the Code Editor of the Dundas control. It provides some event handlers wherein .net (C#/Vb.net) code can be put as per the requirement e.g. PostPaint, PostApplyData, CustomizeChart, CustomizeLegend etc. These are called at some specific point in time & serve a specific purpose.

Drawing a line on Dundas chart for some specific values:

Here we will try to understand the use of Code Editor in Dundas chart with the help of a very simple example. There may be some business need to show a line or any shape based upon certain data conditions on the chart. You need to be a little bit familiar with the .net coding as well.

We will take a simple dataset with some dummy values. 

DataSet: DS_Chart

————————————————

SELECT     1000 AS Value, 1 AS Rate

UNION

SELECT     1400 AS Value, 1.5 AS Rate

UNION

SELECT     2100 AS Value, 2 AS Rate

UNION

SELECT     1450 AS Value, 2.75 AS Rate

UNION

SELECT     2300 AS Value, 3 AS Rate

UNION

SELECT     3400 AS Value, 3.5 AS Rate

UNION

SELECT     2340 AS Value, 4 AS Rate

UNION

SELECT     4440 AS Value, 4.5 AS Rate

UNION

SELECT     4200 AS Value, 5.5 AS Rate

UNION

SELECT     3400 AS Value, 6 AS Rate

UNION

SELECT     9600 AS Value, 6.5 AS Rate

UNION

SELECT     5300 AS Value, 7 AS Rate

UNION

SELECT     5600 AS Value, 9 AS Rate

 Order By Rate

————————————————

The idea is to draw a line chart wherein value (Y-axis) will change overt the rate (X-axis). First we will configure the Dundas Chart control for a basic chart requirement.

1. Right click the control & select properties.
2. Give a title to the chart & go to Data tab.
3. Select ‘DS_Chart’ as dataset.
4. Add a Value series and there select
   1. rate on X-axis
   2. sum(value) on Y-axis.

1. 5. Add one CategoryGroup with Expression as Rate & label as also Rate.

6. Click Ok. Go to Preview tab to see the report. It will look a like as:

Now adding the custom code where 3 < Rate > 4 & Value > 3000.

 7. Go to properties and click on Advanced tab.

1. 8. Click on ViewCode button there.
2. 9. Select PostPaint event & put the below code inside the code editor.

// Chart Graphics object

ChartGraphics graphics = e.ChartGraphics;              

 

// Check what chart element fired the event

if(sender is Series ){

      Series series = (Series) sender;

               

      //Can also check for the series name using series.Name property

            double xValue;

        double yValue;

       

        // Data points loop

      foreach( DataPoint point in series.Points ){

     

        // Get relative position of the point, which will be the center of the ellipse.

      xValue = graphics.GetPositionFromAxis( “Default”, AxisName.X, point.XValue);

      yValue = graphics.GetPositionFromAxis( “Default”, AxisName.Y, 0);

      // Get absolute point.

      PointF absPoint = graphics.GetAbsolutePoint( new PointF( (float)xValue, (float)yValue ) );

       //Draw a line for data

       if(point.YValues[0] > 3000 && (point.XValue > 3 && point.XValue < 4))

       {

            Pen blackPen = new Pen(Color.Black, 3);

            PointF endPoint = graphics.GetAbsolutePoint( new PointF((float)xValue, (float)40) );

            graphics.DrawLine(blackPen, absPoint,endPoint);

       }

    

      }   

 }

 

10. Click Ok.

11. Now go to Preview tab to see the report. It will look similar to the following:

As shown, a line has been drawn at the specified condition’s location.

Extending SQL Server Reporting Services 2005 with Custom Data Extensions (SSRS)

Custom Data Processing 

 There are many components of SQL Server reporting services which can be customized as per the requirements & one of them is how data is being fetched/processed. Though, SSRS provides quite a good number of inbuilt data providers to connect to various types of data sources (inbuilt data processing extensions), there may be a need to do some custom processing before the data actually gets delivered to SSRS report. 

Also sometimes one may have requirement to merge/join data from multiple data sources (say from a RDMS database & from a cube), which at the first sight looks like a limitation of SSRS. But there is what it offers the facility to extend the services by implementing a Custom Data Extension. 

 Well by CDE, it means you can write your own custom code (in .net) which will take the dataset query as its input and will give you the result-set as per the custom logic implemented in the CDE code. So basically doing so we are extending/customizing the ‘Data Processing Extension’ 

 For creating the custom libarary for it there are certain interfaces which need to be implementing. 

These include:   

• IDbConnection
• IExtension
• IDbCommand
• IDataParameter
• IDataParameterCollection
• IDataReader
• IDbTransaction

Details about Required Extensions:  http://msdn.microsoft.com/en-us/library/ms153967.aspx 

Data Extension Process Flow  

Before developing your custom data extension, you should understand how the report server uses data extensions to process data. You should also understand the constructors and methods that are called by the report server. 

 The step-by-step process flow of a data extension that is called by the report server 

 

Also there are certain dll references to be added in the library solution: 

• Microsoft.ReportingServices.DataExtension
• Microsoft.ReportingServices.Interfaces

Implemention of various Interfaces and their methods/properties: 

Class	Implements Interface	Description
Connection class	IDbConnection, IExtension	This is the entry point of the data extenson.
	Methods – IExtension
	SetConfiguration(string config)	For getting the connections defined in RSReportDesigner.config and using them in the code. The whole xml string gets passed from SSRS.
	Methods – IDbConnection
	Open()	Called at the beginning to open the required db connections.
	CreateCommand()	Called to create an object of IDbCommand to form the command to be executed.
	Close()	To close the connections at the end.
Command Class	Implements Interface
	IDbCommand, IDbCommandAnalysis (optional)	This class takes care of preparing the command based upon the commandtext/query passed from the report dataset & retruns the resultset in the form of DataReader.
	Methods – IDbCommand
	CreateParameter()	Creates a new instance of an IDbDataParameter which takes care of parameters valuations.
	ExecuteReader()	To prepare the resultset based upon the parameters and the query passed.
	CommandText : Property	This gives the command/query passed from report dataset.
	Methods – IDbCommandAnalysis
	IDataParameterCollection GetParameters()	This method creates a collection (IDataParameterCollection) of IDataParameters. We can also write our own logic here to prepare the IDataParamCollection and return.
Parameter Class	Implements Interface
	IDataParameter	This contains some functions like ‘Value’ to get the value of a parameter.
Parameter Collection	Implements Interface
	IDataParameterCollection	This contains the function like ‘Add’ to add the parameters to a collection and contains properties like ‘IndexOf’ to get/set the index in the collection.
Data Reader Class	Implements Interface
	IDataReader	This class comes into picture when ‘ExecuteReader’ method is being executed. This method works in conjection with the DataReader class to form a resultset.
	Methods – GetName(), GetOrdinal(), GetValue() & Read() etc
Transaction Class	Implements Interface
	IDbTransaction	To handle transactions.

Once the data extension library (.dll) is compiled, it has to be placed into foler at: C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies 

Registering the CDE with report designer & making SSRS report datasource able to access the CDE (RSReportDesigner.Config & RSReportServer.config) 

 (Generic names are used here)

<Extension Name=“MyExtension“ Type=“NameSpacename.ClassImplementingIDbConnection, AssemblyName“>

<Configuration>

<connections>

<description><environment>MyCustomExtension </environment></description>

<connectionstring>

    <name>connectionname1</name>

    <value>Data Source=servername;Initial  Catalog=DBName;Provider=MSOLAP.2;</value>

</connectionstring>

</connections>

</Configuration>

</Extension>

There can be n number of connection defined, all wil be passed to CDE as a XML string.

 Now when creating a Data Source in SSRS, this extension will be available to be selected as a type of connection:

Importing data from Excel having Mixed Data Types in a column (SSIS)

When we use Excel (Excel source in case of excel 2003 & OLEDB Source in case of excel 2007) as our data source in SSIS, we come across the fact that SSIS decides over the data type of a column of excel sheet based upon the contained metadata. Data source scans the metadata of the excel sheet and determine the data type of columns.

But in this process, there are certain other parameters or settings which also play an important role in this and once decided; all other values with other data type in the same column are imported as NULL.

Example: If we have string and numbers values in the same column then either it will take it as string or number while configuring the excel data source. But again if it considers it as number then string values would be imported as NULL and in case no. of string values are more (in first 8 cells) then it would take string data type which would allow both the values to be imported.

And interesting point to be noted here is, even if we forcefully select ‘DT_WSTR’ (string) data type in the first case, then also it would behave the same.

There are the following settings which cause this unwanted output & these are also the ways to configure them to have the correct results:

• Setting “IMEX=1” in the connection of the Excel sheet. This can be set in the connection string in case of Excel Source (Excel 2003) and can be set using ‘Extended Properties’ in case of OLE DB Source (Excel 2007). This setting tells that excel sheet is having mixed data types and thus import it as ‘Text’ values.

Then we can forcefully select ‘DT_NTEXT’ data type in the data source. 

• Registry setting “TypeGuessRows=8”: This setting/value tells the connection manager to consider the first 8 rows of a column and decide over its data type based upon the maximum no. of values type in those 8 values. So if we have first 5 rows/cells of a column of type string but rest of the 100 values are numbers then it would import all the numbers as NULL since data type in this would be String (DT_WSTR). This will be applied for all the packages i.e. on server level.

So if we use a higher value for TypeGuessRows (say 20) then it would take it as Integer.

But again here also it would import rest of the string values as NULL. 

• Using ‘SELECT * FROM [SheetName$]’ query instead of direct table/sheet name. This will allow all type of values to get imported as ‘DT_WSTR’ which is quite acceptable because anyhow it’s getting all the values of a column. This is really a good option to select.

Note: In all the cases where in we are getting string data (along with number data) be means of some or the other setting, we have to have String column in the destination.

How to read a Flat File with data delimited by any wild card character like ‘#’, ‘$’ (SSIS)

Requirement:

Generally we deal with Flat Files which are comma (,) separated i.e. CSV files. And in SSIS by making use of ‘Flat File Source’ component we can read them very easily. These files can contain results/data from various sources like IBM, Mainframe, SAP etc. They may also be manually created or generated through some custom applications. And thus due to some specific requirements or limitations; they may have data which is not comma (,), bar (|), semi colon (;) etc delimited. These are among the few possible options available in Flat File connection manager used while reading the Flat Files in SSIS.

Apart from that there may be the requirement of having data containing comma (,) in their values itself. And thus we cannot just replace all the delimiters like dollar or # with a comma to have a comma separated file.

So to make it possible & read the records from a Flat File which can have any known separator for its column values, we need to do some kind of exercise explained below.

 Solution:

Since we don’t have any inbuilt option to select ‘#’ or ‘$’ etc as a delimiter for Flat File in connection manager, we would take the whole line/record into one column and then would split out all the values as per the present delimiter, using a Script Component.

Let’s take an example:

We have a .txt (Flat) file (say InputFile.txt) with three columns A, B and C. These headers are comma separated but the values under these columns are ‘#’ delimited.

Data into the file:

A,B,C

100#200#300

500#600#700

 Steps for the SSIS Package:

1. Take a Data Flow Task (DFT).
2. On the data flow tab, Drag one ‘Flat File Source’, edit it and click on ‘Flat File Connection Manager -> New’.

• Name it as ‘SourceFileConnectionManager’.
• Select the ‘Inputfile.txt’ in the File Name column by browsing the file.
• Select Format as ‘delimited’.
• Select ‘Header row delimiter’ as ‘Comma {,}’.
• Select the check box for ‘Column names in the first row’.

   

 

 

 

 

 

 

 

 

 

  

 

• Go to Column tab of this connection manager.
• Select ‘{CR} {LF}’ as Row delimiter.

Screen would look like as:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  

As you can see all the data is now coming into a single column named ‘A,B,C’. 

• Click OK.
• Go to ‘Columns’ tab of the Flat File Source.
• Rename the Output column as ‘A’.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

• Click Ok.

 3. Drag a Script Component & select to use it as a Transformation while adding it.

• Point the output of Flat File Source to this script component.
• Edit its properties and select “A” as an input column there.
• Go to ‘Input and Output’ tab and add three output columns (col1, col2 & col3) to the existing output ‘Output 0’. Select ‘four byte signed integer’ as their data type.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

• Modify the ‘SyncronizeInputId’ of Output0 to 0. This is to make the script component asyncronized.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

• Go to ‘Script’ tab and click on ‘Script Designer..’ to edit the script (VB.Net).
• Select ‘Pre Compile’ as False.
• Write the following script in the ‘Input0_ProcessInputRow’ event handler:

         Dim values() As String

        values = Row.A.Split(“#”.ToCharArray)

         Output0Buffer.AddRow()

        Output0Buffer.Col1 = Convert.ToInt32(values(0))

        Output0Buffer.Col2 = Convert.ToInt32(values(1))

        Output0Buffer.Col3 = Convert.ToInt32(values(2)) 

• Close this and Click Ok.

1. Take one Flat File Destination and with the following shown setting configure its connection manager (DestinationFlatFileConnection).

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

• Go to Advanced tab and modify the name of the columns from col1, col2 & col3 to A, B & C.
• Go to the ‘Mappings’ tab of the flat File Destination and do the below shown column mappings:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Now execute the package to have the following output in the ‘OutputFile.txt’ file.

 A,B,C

100,200,300

500,600,700

———————————————————————————————–

Multi-language reports in SSRS (SQL Server Reporting Services)

Introduction

In this world of globalization, everyone wants to have projects, reports etc being accessed by users across countries/cultures around the world. And thus one needs to support SSRS reports in multiple languages.
Different-2 applications provide multi-lingual support in different-2 manners. Though, SSRS does not have inbuilt support for this, we can achieve it using some code based technique.

Background

Since, in general report contains numeric data which are to be analyzed based upon some textual headers/groups. So to actually say that a report has multi-lingual support, all that textual information should be dynamically changing as per the culture/language of end user.
And since these fields basically are the labels/textboxes displaying textual information, we actually have to manage these labels/textboxes for multi-language support.
Also we must have some mechanism by which we can detect the language/UI culture of end user and can present the page/report in an acceptable format.

Solution:

In any way of its implementation, the resource information as what should be displayed in what language /culture, it has to be stored somewhere. This can be maintained in database or in a resource file etc. The structure of the resource storage table would be like:

Field Name	Value	Language
Column1	Total Amount	En-US
Column1	Somme totale	French

Now while displaying this column1 on the report, rather than hard coding its value, at runtime we have to detect the language of interest & display the corresponding value. We can make use of the global variable of SSRS report ‘User!Language’ to get the current language been set in the client’s browser and then pass it to the data table.
This can be done either by using a custom code (DLL/Assembly) in which we can have a function and this function will return the related value of fields.

Function: GetValue(fieldname, language)

Or we can use dynamic query & directly make join to the resource table in our main data set for all the required fields. In this query we can then pass the language retuned by User!Language.

Steps:
1. Create a hidden parameter (CurrentLanguage) of type string.

 

 

 

 

 

 

 

 

 

 

 

 

 

 2. Use expression to assign a non-queried default value to this parameter as =User!Language

3. Use dynamic query for the main data set as:

  SELECT ColumnName, measure1, measure2

FROM MainTable MT JOIN resourceTable RT

ON MT.FieldName = RT.ColumnName (say ColumnName = Column1)

AND RT.Language = @CurrentLanguage

 

Now at runtime @CurrentLanguage will get the language of the client & display the lables & all in the correct language.

  

Creating & Using a Package Template (SQL Server Integration Services)

When we create an Integration Services solution, a package (package.dtsx) gets added to the solution automatically. This package uses the default package template, which is empty with no control flow task. Also when we add a new package (SSIS Packages folder) or add a new item & select new package, a package with the default template gets added.

Now sometimes like in a big project, we may need to have reusable package tasks, connection managers etc and want to use them as a template for all the newly added packages. This may be required to have the pre & post execution tasks like making entry in the transaction log at the beginning & at the end of the package to impose consistency across packages. So in this case we would want to have a template package which should get added when we add a new package and which we can modify or add other tasks to as per the requirements.

Save & Use a package as a package template:

As stated above, we can use custom packages as templates in SSIS. There are few steps to be followed to save & then use a package as a template.

1. Create a package (MyPackagetemplate.dtsx) to be used as a template.
2. Copy this package to the DataTransformationItems folder residing at the location:

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject \DataTransformationItems

      3.   Now while adding a new item to the integration services solution as shown below, the copied package will be available as a template in the templates pane:

Add Template

 

 

 

 

 

 

 

 

 

Select Template

And doing so, the newly added package will be added with all the tasks/transformations etc existing in the template package. And thus we need not to worry about the location of the reusable package while adding as it is always available as a template.

Point to be specified:

When we add an existing template or package, the package ID of the newly added package would remain same as that of the template package. This might be an issue if we are referring them in the log/error (sysdtslog90) tables. But we can change the Package ID by selecting ‘Generate New ID’ option as shown below.

Dynamic columns mapping – Script Component as Destination (SSIS)

Requirement:

In Data Flow task of SSIS, we have to define a Data Source as well as a destination to extract & load the data respectively. And as per the functionality of data flow task (DFT), metadata of the source fields has to be defined at the design time. That’s the input columns from the source must be mapped to the destination columns and for that we must know the schema of the input as well as the destination/output columns.

But in many business scenarios we come across the requirement where at runtime only we come to know about the structure of the input columns and thus at runtime only we have to decide for the destination columns, this is called dynamic mapping.

Solution:

This type of dynamic mapping for source to destination can be achieved using script component. This script component can be used in three ways in data flow task:

1. As a Transformation,
2. As a Data Source
3. As a Destination

When we drag & drop the script component from tool box, a dialog box will appear to choose the required type.

Script_Component_Type

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1.  Transformation: Script component can be used to do some transformations on the input data, processing each & every row one by one. We can write custom code/script in it to make the required operations on the data. In this case total number of output columns would be same as that of input columns.

2.  Source: If we select script component to be used as a Data Source, we can configure the component to generate the required number of rows as the output. In this case we can add as many columns we want in the output of the script component and then at runtime we can write code/script to add the rows in the output. We can also have multiple asynchronous outputs with different-2 names which can be loaded to different-2 destinations.

3.  Destination: Sometimes we don’t want to dump/load the input data directly to the destination like OLEDB destination or Flat file destination; rather we want to decide for the number of columns/rows somewhere before actually loading the data. Here we can choose script component as a destination.

Script component should be used as a source, in case we are sure about the schema/columns of the input records. But in the component’s script editor we can process the data & generate the output rows based upon certain conditions.  But here also we have to map the output of this component to the destination columns at design time only.

Now in case; we want to have dynamic mapping at runtime, we can configure Script component as a Destination. In this mode of component we can look at the input data & decide about the structure of the fields to be loaded into the final destination, based upon certain conditions which can be stored in database or variables.

Configuring Script Component as a Destination

Adding Connection Managers: Since in the script component itself, we are going to load data into the final destination, we need the connection manager(s) to connect to the final destination e.g. Flat File connection manager, OLE DB connection manager etc. On Connection Managers page of the Script Component Editor, we can add the already created connection manager or also can add new connection managers. All the added connection managers would be available in the script via Me.Connections. connectionname (vb.net) & this.Connections.connectionname (C#).

Input/ Output columns and Variables: We can configure the input columns, output columns as well as variables as is done in the normal script task or component.

Scripting the Destination Component: Its main configuration is the custom script (vb.net or C#) which is used to process the input data, decide over the number/ type of destination columns and then load them into the final destination.

There are many functions/subroutines which we can override in the script to acquire the added connection managers, process the input data, form the query at runtime & load the data into the destination etc.

Example: Let’s take an example in which we add one ADO.Net connection manager ‘myConnection’ to execute a query at the SQL server destination.

Note: Here we will connect to SQL Server via a managed connection manager (ADO.Net) because AcquireConnections function works for these only. Otherwise we’ll have to create connection in the script only; using the connection string of the defined connection manager.

Imports System.Data.SqlClient

Public Class ScriptMain

    Inherits UserComponent

Dim conMgr As IDTSConnectionManager100

    Dim sqlConn As SqlConnection

    Dim sqlCmd As SqlCommand

 Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

  conMgr = Me.Connections.MyConnection

  sqlConn = CType(conMgr.AcquireConnection(Nothing), SqlConnection)

     End Sub

     Public Overrides Sub PreExecute()

         sqlCmd = New SqlCommand(“INSERT INTO MyTable(Col1, Col2) ” & _

            “VALUES(@Value1, @Value2)“, sqlConn)

        sqlParam = New SqlParameter(“@Value1“, SqlDbType.Int)

        sqlCmd.Parameters.Add(sqlParam)

        sqlParam = New SqlParameter(“@Value2“, SqlDbType.Int)

        sqlCmd.Parameters.Add(sqlParam)

     End Sub

     Public Overrides Sub MyAddressInput_ProcessInputRow(ByVal Row As MyAddressInputBuffer)

        With sqlCmd

            .Parameters(“@Value1“).Value = Row.ID

            .Parameters(“@Value2“).Value = Row.Count

            .ExecuteNonQuery()

        End With

    End Sub

     Public Overrides Sub ReleaseConnections()

         conMgr.ReleaseConnection(sqlConn)

     End Sub

 End Class

 Thus we can process the input columns, make connections and based upon required conditions; we can build our own queries to dynamically map them to the destination. In the similar way we can write to Flat file destination as well.

Calculation tab not working – SQL Server 2005 Analysis Services

Problem/Bug:

In Business Intelligence Development Studio for SQL Server 2005 analysis services, when we click on ‘Calculation’ Tab to define some calculated measures & all, we can get the following error:

‘Unexpected error occurred: ‘Error in application.’

Also having this error visual studio IDE will crash & will be closed automatically.

This issue may not get resolved by applying SP2 or other hot fixes. And you may also not find this error logged into the event viewer.

Cause:

This problem occurs due to the compatibility issue i.e. version difference between the binaries/ DLLs.

These DLLs are 1. Msmdlocal.dll & 2. Msmgdsrv.dll

Solution:

Version of the assemblies ‘msmdlocal.dll’ & ’Msmgdsrv.dll’ must be the same of the ones installed into “%ProgramFiles%\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\” and the ones installed into “%ProgramFiles%\Common Files\System\Ole DB” location.

If it is not same for any or both of these DLLs, then replace the ones in ‘PrivateAssemblies’ with the ones from ‘ole db’.

 Happy SSAS Cube Designing..!

Digital Signing a SSIS package (SQL Server 2005 Integration Services – Security)

General theory about Digital Signing & data security:

In SSIS we have some options to secure our package’s content by applying certain protection levels. These defined protection levels protects the package from being viewed by unwanted users. By using these options we can save sensitive information like username, password etc in our package without letting other users to see them. These all come under protection levels in SSIS like ‘EncryptSensitiveWithUserKey’, ‘EncryptSensitiveWithPassword’ etc. In this we can protect the whole package or some part of it by means of ‘User Key’ or some password or using both.

But none of these options are sufficient to provide protection against attacker who are educated enough or having rights to open up the package & do some modifications to that. But if we can come to know about the changes done to some package by some unauthorized user, we can restrict them to deploy those packages to be executed.

To achieve this we make use of a great concept called as ‘Cryptography’. This works with some keys to encrypt & decrypt the data. This can either be Symmetric or Asymmetric in terms of their usage. In symmetric encryption\decryption we use one unique key called as Public Key. This public key is used for both encryption as well as for decryption process. On the other hand, in case of asymmetric encryption\decryption we use a pair of keys, a public key for encryption and a private key for decryption of the data. Also in this case, public key is what anybody can see & use it to encrypt the data but only owner of the private key decrypt that data. So it provides the security for the data.

But to be sure about and to trust the provider of the data, we somehow have to validate the data provided by the source provider. Here we come up with Signatures, in fact Digital Signatures. Signature is the HASH data generated by applying the cryptographic function using the private key of the owner. This hash data (signature) is then attached to the actual data to be passed over or shared across. The public key holder can determine if any modification has been made to the actual data by generating the Hash code again by decrypting the data using its public key. Then this new hash code can be compared with the existing hash code (i.e. signature) which is attached along with the data, as this signature cannot be modified.

Digital Signing of a SSIS package:

Now the important idea is to recognize whether any change is made to a package or the data integrity has been violated or not. I mean we must have some mechanism to know about the authenticity of the owner from which we are getting the encrypted data. And here come Certificates into picture.

Certificates are digitally signed statements that contain key information intended for secure data exchange. Certificates are issued following a request from a client who wants to make such exchange possible, by an entity known as Certificate Authority (or simply CA) – a reputable organization, whose responsibility is to verify credentials of its applicants, evaluate legitimacy of their request, as well as revoke certificates if, for some reason, such as a key compromise, they are no longer considered to be valid prior to their expiration date. CA creates a unique key pair, with a private one delivered in a safe manner to the client and a public one packaged in the form of a public certificate, which also contains the name and contact information of its owner and issuer, its purpose as well as expiration date. The content is digitally signed with a private key that belongs to the CA. The public certificate is then made available to anyone who wants to participate in a secure communication with the certificate owner. And this way user can check whether the user associated with the source data is genuine or not.

Code Signing – SSIS

SSIS packages can also be secured using the same kind of digital signatures i.e. digital certificates. These become very useful when we want not to allow any unwanted user to load or execute the package if package has been modified by an authorized user. And one cannot deploy that package which is not digitally signed.

This can be done by either:

1. From Business Intelligence Development Studio, using Digital Signing option in the SSIS tab.
2. OR using DTUtil command line utility combined with the /SIGN switch.

Also we need to set two addition configuration settings to control the behavior  of digitally signed package. These are: 1. CheckSignatureOnLoad  and 2. BlockedSignatureStates

1.    CheckSignatureOnLoad indicates whether certificate is checked during the package load.

2.    BlockedSignatureStates is the registry entry of type DWORD to be created in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS key.  It can have four possible values: 

• 0  - Does not impact the loading process, regardless of the signature status
• 1 – Prevents from loading (and executing) packages with invalid signatures
• 2 – Prevents from loading and executing the packages with invalid or un-trusted signatures (issued by non-trusted CA).
• 3 – Prevents from loading and executing the packages that are unsigned or have invalid or un-trusted signatures.

Applying the certificate to SSIS package:

The certificate to added/used can be requested from a Certificate Authority (CA). This can be a third party issuing certificates or it can be a Windows Server that acts as a CA. Details of how to get the certificates using windows server can be seen from:

Now once we get the certificate in our personal folder, it can be added to the SSIS package as shown.

 

 

 

 

 

 

 

 

 

 

 

If some certs are available in the personal folder, those will be shown here to be added.

 

 

 

 

 

 

 

 

 

 

One more important setting which is to select the CheckSignatureOnLoad checkbox to enable the checking for the signature each time it is loaded.

Note: Before signing the package, create the BlockedSignatureStates entry in the respective key in the registry using REGEDIT.exe utility. Set the value of this entry as per the requirement.

Example:

The above mentioned functionality can be checked using a certificate for a SSIS package. Set the BlockedSignatureStates to 3 and check CheckSignatureOnLoad option. Now if the user who is the owner of the certificate & have rights on the personal folder of the certificate makes some changes then it would apply the same digital signature automatically to the package. But on the contrast, if somebody else makes the changes say by using some other means like .dtsx file (XML file) or so and saves it. Then digital signature would not be applied automatically to that and thus subsequent loads or execution of that package would fail saying that signature is not valid since content of the package has been modified.

How to handle Nested Transactions in TSQL (SQL Server 2005)

In SQL Server 2000, we used to handle the errors while performing the DML queries by means of global variable @@Error. These errors may occur due to many reasons like data type mismatch, referential integrity violation etc. The @@error variable tells us whether the last statement (immediate before the @@error variable is used) caused any error or executed successfully. We could check its value for the same. If it is more then 0, then there is some error. But using @error variable & handling the error this way was not handy in solving many problems/requirements.  For example if we have multiple DML statements being executed in a sequence and we want to take some action as soon as any of these statements causes any error. In that case after each & every transaction we have to check for @@error variable.

In SQL Server 2005, TRY-CATCH block was introduced to enhance the error handling process of TSQL. In this we can specify a TRY block and can catch the error(s) in CATCH block as caused by any of those queries defined in the TRY block. And this way we can either COMMIT or ROLLBACK them in TRY & CATCH respectively.

Now sometimes we need nested transactions to be configured. Nested transactions mean we may require having some inner transactions with one or more outer transaction. In that scenario, we must have to be very careful in Committing or rolling back the transaction.

Points to be remembered:

1. A COMMIT statement in the Outer transaction will commit all the open inner transactions.
2. A Commit statement in the Outer transaction will not commit the changes rolled back by the inner transactions.
3. A Rollback statement in the Outer transaction will rollover all the inner transaction, irrespective of the Commit/Rollback fired in the inner transaction(s).

SELECT * FROM TESTTABLE

initial result

 

 

 

 

 

BEGIN TRANSACTION  –Outer TRAN

BEGIN TRY

BEGIN TRAN A   –INNER TRAN

   BEGIN TRY                  

       INSERT INTO TESTTABLE VALUES (11111)                   

       COMMIT TRAN A

    END TRY

    BEGIN CATCH

       ROLLBACK TRAN A

     END CATCH

   ROLLBACK TRAN  –Rollback all

END TRY

BEGIN CATCH

  ROLLBACK TRAN

END CATCH

Result: 

Final result

 

 

 

 

 

 

Though we have committed the inner transaction (‘A’) after insertion, all data got rolled back by the Rollback statement of the outer transaction.

Note: In general, only open transactions can be committed or rolled back. So check it XACT_STATE() <> 0 before commit or rollback.

Accessing SharePoint Lists with SQL Server Integration Services SSIS 2005

As now a days, sharepoint has become very propular and is widely used. And people come acorss the requirements to make share point communicate with SQL server; to & fro. And there may be need to load data from share point applications to some database and vice versa; without much of extra coding efforts.

In other words we may want to access data of share point application to manipulate/operate on, which can be in some other formats other that simple database tables like lists/docs etc.

I found a very nice & simple aritcle which manages to access the list data of sharepoint Lists using SSIS, so that we can do whatever operations we want to do with those data in SSIS by using in built SSIS features  like transformations etc.

Here is the link

 

http://www.mssqltips.com/tip.asp?tip=1723

How to retain same data connection across multiple tasks in SSIS

In SSIS for each task; we use; in control flow pane of a package, we need to specify some connection to execute that task. This may be an OLEDB connection, a File connection etc and the tasks using these connections may be an ‘Execute SQL task’, a ‘File System task’ or a ‘Data Flow task’.

Now as we as have to provide connection for each & every task separately, it means for each task a new/separate connection is opened every time. This connection gets closed after the completion of that particular task. This is useful as in a SSIS package; we are dealing with distributed data sources or data servers.

But sometimes we may require to have a transaction opened in one connection in one task & after doing some operation we have to either commit or rollback that transaction using the same connection but in a later task. This can be achieved using “RetainSameConnection” property of the connection manager defined.

You might be thinking of the distributed transaction support available in SSIS to have the same functionality, but let me clarify that available distributed transactions like ‘supported’ etc comes into role if we want to either commit or rollback whole set of transaction based on success or failure. This cannot be controlled based on some logical conditions. But here by retaining the same connection across all the tasks participating in a transaction, we can achieve this.

Let’s take an example:

We will begin a transaction using a ‘Execute SQL task’, then will perform some operations using a DFT. In that DFT we will set a variable ‘SomeCondition’ to either 0 or 1, based upon some condition. And then by looking at the value of this variable we will either Commit or Rollback that transaction; using another ‘Execute SQL task’.

But Before that we need to create an OLEDB connection to use it across all the tasks.

Set RetainSameConnection to TRUE.

 

 

 

 

 

 

 

 

 

 

———————————-

SQL Queries used:

 In ‘EXEC_SQL_BEGIN TRANSACTION’ execute sql task:

 BEGIN TRANSACTION

 INSERT INTO CheckRetainConnection

VALUES(100, ‘John M’)

In ‘EXEC_SQL_COMMIT_ ROLLBACK’ execute sql task:

Use expressions tab to set the SQLStatementSource for the below query:

“IF 1=”+ (DT_STR, 5, 1252) @[User::SomeCondition] +

“  COMMIT TRANSACTION

ELSE

  ROLLBACK TRANSACTION”

———————————-

So if we set variable SomeCondition as 1, insertion performed in first execute sql task will be committed else it would be rolled back.

How to set multiple values as Default Values for a multi-valued parameter (SSRS)

In most of the cases when we deal with parameters in SSRS reports, we provide some default value to those parameter (which can be a textbox, a drop down list etc) so that when users browse the report first time, report shows results with that default value of the parameter. And after that user can choose different values from the parameter list or enter different value in case of textbox to get the desired results.

 

This feature of having default value(s) can also be implemented in case of multi-valued parameter list. As in case of multi-valued parameter, user can select multiple values from the drop down list by selecting multiple checkboxes. So if we need to provide some default selection of values from this drop down list, we can provide a list of these default values for this parameter. This can be done using a Dataset for default values & then selecting this dataset as the query input for the default value.

 

Let’s suppose we have a multi-valued parameter named “Color” for displaying products based upon the selected list of colors.

Dataset for this report parameter will give complete list of colors.

 

SELECT DISTINCT Color FROM DimProduct

 

Now we’ll create another dataset only for the default values i.e. to select some of the colors by default to show the initial results on the report. This can be done by selecting some of the desired values based upon some condition or we can just have the predefined hard coded values.

 

SELECT ‘Blue’ As D_Color

UNION All

SELECT ‘Black’ As D_Color

 

And in the parameter “Color”, we can map these as: