Tuesday, 2 June 2009

Using Dynamic Query in SSIS Data Reader Task

There are certain requirements in which user has to load data for a certain time period or based on some condition which changes dynamically.
For OLEDB task we have option of parameterized Query but what about Data Reader Task ???

Here I am taking an example in which user has to data between two dates, present in a table.
Following are steps for it:-

1. Declare two variables type String as shown in figure.

2. Use Execute SQL Task to get execution dates into SSIS variables. Write following Query to get dates from ExecutionDates table.

select convert(nvarchar(20),Convert(nvarchar(10),ToDate,120)+ '  00:00:00') as 'To',
convert(nvarchar(20),Convert(nvarchar(10),FromDate,120)+ ' 00:00:00' ) as 'From'
FROM dbo.ExecutionDates

ExecutionDates table having two cloumn name FromDate and TwoDate for defining Executiondates.

3. Configure the Execute SQL Task according to the following screen

4. Now drop a data flow task, Connect Execute Sql Task to data flow task. Inside data flow task drop a data reader Source from ToolboxData Flow Sources. Paste following Query. Now you can put query without where clause because it’s going to change dynamically. So condition doesn’t matter.

SELECT * FROM Sales.SalesOrderHeader
WHERE ModifiedDate = getdate()

5. Now go to data source properties.  Expression  [DataReader Source].[SqlCommand] property
Following Screen will show you how you can configure it for Dynamic Query.

The final Query expression will be like

"SELECT * FROM Sales.SalesOrderHeader  WHERE ModifiedDate >" +  @[User::From]  + " AND ModifiedDate <=  " + @[User::To]

6. This way you will be able to evaluate query at the time of package execution.

This is how we can set Dynamic Query for Data Source reader.

Gaurav Gupta

Sunday, 31 May 2009

Use of Date Range in PPS Reports

Its very common scenario when customers ask us to develop a report based on a particular date range.
Like list of students join between 12th January 2009 and 2nd February 2009.

These kind of reports are easy to make using SQL Server Reporting Services.
But what if we want to use Microsoft Performance Point.

Following are the screen shot and steps require building a report using a date range.

1. Creating a simple report (in screen shot I am taking [Time].[Year - Half Year - Quarter - Month - Date].[Date] on the Y-axis so that we can see that filters are working properly.)

2. On the Query tab adding two parameter Range1 and Range2

3. Editing MDX as shown in Screenshot

Changing the [Time].[Fiscal Day].Allmembers to [Time].[Fiscal Day].[Fiscal Day] so that we don’t have column named “all” in the report.
Inserting where clause -
WHERE {<> :<>}

4. setting default values for the parameter Range1 and Range2

Here I am giving [Time].[Date].&[2008-05-21T00:00:00] as default value for one of the parameter.
The reason of taking different attributes in parameter and on Y-axis is that for same attribute it won’t work.

5. Now on the Design tab we can see that it is showing only those columns which comes under Date Range.


6. Now creating two filters named From and To in dashboard, Filters are member selection filter based on [Time].[Date]

7. Linking the reports to the filter. Remember that’s Dashboard item end point for from is Range1 and for To is Range2. Source value should be Member Unique Name.

8. Publish dashboard and Preview.

In preview we can see that it is showing data between a date ranges.

But taking Tree or Drop down Box is not useful as its not have ease of use.
We can even use calendar instead of it.

For using calendar in filters. We have to use Time Intelligence Post Formula Filter.

For using any Time intelligence filter you have to configure the time settings of data source.
Go to data Source --> Time Tab ... See the screen shot.

Configure it in a way it is showing in Screen shot.

(For more information on configuring time Intelligence Filters Please refer http://office.microsoft.com/en-us/help/HA102408431033.aspx )

After configuring data source. We create two Time intelligence Filter From and To.
Then link the filter as per the screen shot

After that click on the Filter Link Formula…

And insert Formula by typing “Day“.

Formula “Day” means we want day level data. Other options like Month, Quarter can be used.

After it publish and Preview it.

In the Screen shot you can see that how can a calendar control works.

Gaurav Gupta

Saturday, 30 May 2009

Using Script Component Instead of Cursor

There are many scenarios where we use cursor in our DW building logic.
But as all we know that cursor is nothing but a curse for a developer as its impact on performance is huge.
We always try to avoid it. But cant refused to use it.

Here I am taking a scenario which I faced in a project.

Supposed you have to dates like Order date and Ship Date and you have to count the no of days between order and shipment.
Yes you can simply use DateDiff Function,
But the problem with this is like If someone want to see the no of days on the date which is in between Order and Shipment date. Then??

For cases like these where you need to insert dates in between two dates I used cursor.
But the performance of cursor was too bad.

Then I thought of alternative approach …which brought me to the Script Component.
Yes I tried Script Task instead of Cursor and I found it very fast and well resource utilized.

Following is the script you can use:-

Here I am taking Script Component as Source.
Output records can be stored in any table by using any of the SSIS destination components.

Imports System

Imports System
Imports System.Data
Imports System.DateTime
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.SqlClient
Imports System.Data.Odbc
Imports System.String
Imports System.Text

Public Class ScriptMain
Inherits UserComponent
Dim connMgr As IDTSConnectionManager90
Dim dateinsrt As DateTime
Dim loopend As DateTime
Dim Sqlconn As SqlConnection
Dim Sqlcmd As SqlCommand

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
Dim connectionString As String
connMgr = Me.Connections.STGConnection
Sqlconn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)
End Sub

Public Overrides Sub CreateNewOutputRows()
Sqlcmd = New SqlCommand("SELECT SalesOrderID,OrderDate,ShipDate FROM Sales.SalesOrderHeader", Sqlconn)
Dim reader As SqlDataReader
reader = Sqlcmd.ExecuteReader()
Do While reader.Read
With MyOutputBuffer
dateinsrt = CDate(reader.Item(1))
If IsDBNull(reader.Item(2)) Then 'In Case if shipment date is NUll ie not yet shipped
loopend = Today()
loopend = CDate(reader.Item(2))
End If
While dateinsrt <= loopend  'loop period 
'Output Records
.SalesOrderID = CInt(reader.Item(0))
.OrderDate = CDate(reader.Item(1))
.ShipDate = CDate(reader.Item(2))
.NoOfDays = 1
.DateKey = dateinsrt
dateinsrt = dateinsrt.AddDays(1) 'Adding days
End While
End With
End Sub
End Class

Your Feedbacks are valuable ............ :-)

Gaurav Gupta