SQL Server Business Intelligence

Moved to http://www.beeii.com/

2007-08-29T02:53:00.000-07:00

I have moved my blog to beeii.

Thanks
Sutha

Installing Proclarity Analytics Server

2007-08-20T00:48:00.000-07:00

Please note the Sever Authentication should be set to "SQL Server and Windows Authentication Mode" before installing PAS.

Default is set to "Windows Authentication Mode". You can change this by selecting Properties on the server. This setting is under "Security" tab.

Just a reminder for me more than anything else.

Thanks
Sutha

Performance Point CTP4 Available for Download

2007-08-16T06:45:00.000-07:00

Performance Point CTP4 is available for download from MS Connect site.

Thanks
Sutha

How did I build Employee dimension using historical data?

2007-08-06T01:05:00.000-07:00

I never ever had to this in the past. On my current project I had to build the employee dimension based on opportunity table in CRM system, not from an employee reference table. Opportunity table holds the following information OpportunityID, AccountManagerID, ActualCloseDate, ClosedDivision and ClosedRegion along with many other fields.

UserInfo table is the table which holds Employee details, but that table only holds current date. Going forward we will be OK as will do the type 2 on Region and Division.

The requirement was from the client is to map the Employee to an opportunity, which is fine, except the employee need to be mapped to the Division and Region which they closed the deal at. That’s why the ClosedDivision and ClosedRegion from an opportunity table comes handy.

I brought the data from source system into Stage database using query similar to the one below
SELECT o.accountmanagerid,
u.username,
o1.closed_region,
o1.closed_division,
MIN(o.actualclose) actualclose
FROM sysdba.opportunity o
INNER JOIN sysdba.opportunity_extra_1 o1
ON o.opportunityid = o1.opportunityid
INNER JOIN sysdba.userinfo u
ON o.accountmanagerid = u.userid
WHERE status LIKE 'Closed - %'
AND actualclose IS NOT NULL
AND closed_division IS NOT NULL
AND closed_region IS NOT NULL
GROUP BY o.accountmanagerid,
u.username,
o1.closed_region,
o1.closed_division

The above query will bring all the AccountManagerID (NK), Name, ClosedDivision and Region along with MINIMUM actual close date of an opportunity.

Source table example

Opp Emp ClosedDate ClosedRegion ClosedDivision
1234 ABC 01/04/1990 London South
2345 ABC 09/04/1990 Mids North
4567 ABC 06/08/2003 Mids North
3456 ABC 05/07/2007 Scotland Scotland

The Stage table will look like this

Emp ClosedDate ClosedRegion ClosedDivision
ABC 01/04/1990 London South
ABC 09/04/1990 Mids North
ABC 05/07/2007 Scotland Scotland

Now I have got the stage table populated, now how can I build the warehouse dimension table.

This package only to run one time, as it is going to build the employee dimension using historical data. This is how I built the dimension.

1. I read the stage table and assigned it to an object variable using the following query
SELECT DISTINCT AccountManagerId, ActualClose
FROM scbEmployeeHistory
ORDER BY 1,2
2. Please note that I am only selecting AccountManagerID (NK) and ActualClose (date).
3. Using Foreach Loop Container I loop through the Foreach ADO Enumerator and mapping the results to 2 variables.
4. Then I will build the dimension usual way, but the key is to pass the AccountManagerID and ActualCloseDate as parameter to the source querry.

If anyone would like to see an example feel free to drop me a mail and I will send it to you.

I would be interested to hear from others if you have done it differently.

Thanks
Sutha

How do we extract latest data from source system

2007-07-31T00:39:00.000-07:00

Until SQL Server 2008 is released and implemented we all have to work with various work around to retrieve only latest data from source systems.

In the previous projects I was fortunate in a way that source system was either Oracle or flat files with only changed / new data. Oracle and other major DB vendors had Change Data Capture for a long time.

SQL Server 2008 is going to provide the same feature and functionality. You can find out more about SQL Server 2008 CDC feature here.

Each source table has either CreateDate and ModifyDate column. If these columns are there we extract all the records anything greater than we have extracted last. I store last extract datetime stamp for each table in a control table, which is used within SSIS package to extract only the latest record.

I use a SP similar to the one shown below to update the control table with the last extract datetime stamp.

CREATE PROCEDURE [dbo].[uspUpdateExtractionDate] (@LoadId INT)
AS
DECLARE @VarName VARCHAR(50)
DECLARE @ObjectName VARCHAR(50)
DECLARE @VarValue VARCHAR(50)
DECLARE @Tmp TABLE (VarName VARCHAR(50),ObjectName VARCHAR(50), VarValue VARCHAR(50))
BEGIN TRY
INSERT
INTO @Tmp
SELECT 'strLastExtractDate', 'pkgStgOpportunity',CONVERT(VARCHAR(23),MAX(CreateDate),121)
FROM scbOpportunity
WHERE LoadId = @LoadId
BEGIN TRAN
DECLARE Tmp_Cursor CURSOR FOR
SELECT *
FROM @Tmp
OPEN Tmp_Cursor
FETCH NEXT FROM Tmp_Cursor INTO @VarName, @ObjectName, @VarValue
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE BISystem.dbo.ConfigVar
SET VarValue = @VarValue
WHERE VarName = @VarName
AND ObjectName = @ObjectName
FETCH NEXT FROM Tmp_Cursor INTO @VarName, @ObjectName, @VarValue
END
CLOSE Tmp_Cursor
DEALLOCATE Tmp_Cursor
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
EXEC BISystem.dbo.uspLogErrorInfo
END CATCH

Thanks
Sutha

Execute Package Task Might Fail Intermittently

2007-07-27T02:33:00.000-07:00

I have used Execute Package Task before in various projects without any problems.

On my current project I tried to do the same I came across funny problem. Execute Package Task started to fail Intermittently. The only difference in this project to others I have implemented was that we are using ADO.Net Connection Manager. Thats where it seems to have a problem. It doesn't seem to acquire connection for the ADO.Net connection manager time to time.

Therefore I have decided to use script task to execute the package. When I use Script Task it works fine.

I need to investigate this further and maybe even log a call with MS.

Thanks
Sutha

Loading Data Into Table via SSIS when IGNORE_DUP_KEY is ON

2007-07-25T11:09:00.000-07:00

Probably most of you aware of this issue and most definitely it is not a best practice, but if you wish to use in any unforseen reason please be aware.

For example we have a table with 3 columns. If you set a Unique Non-Clustered Index with all 3 columns and set IGNORE_DUP_KEY = ON, then you have to be aware of the following.

If you are using OLE DB Destination with OLE DB Provider for SQL Server it will fail the package even though it will insert correct number of rows by ignoring duplicate rows. If you want to populate this type of table without failing the package, while using OLE DB Destination, you would need to user Native OLEDB\SQL Native Client.

The Provider to use in this scenario is - SQLNCLI.1
The Provider NOT use in this scenario is - SQLOLEDB

Thanks
Sutha

Loading Date Dimension

2007-07-23T23:52:00.000-07:00

I am sure all of you seen this script before. More than anything I am putting this here for my reference in the future.

Last couple of client sites I have worked, their fiscal year is aligned with calendar year and therefore it made life much easier. The SP below can populate the dates as you specify.

USE [Stage]
GO
CREATE PROCEDURE [dbo].[uspPopulateDate] (@LoadId INT)
AS
DECLARE @id INT
DECLARE @date DATETIME
DECLARE @Startdate DATETIME
DECLARE @Enddate DATETIME
SET @Startdate = '1950-01-01'
SET @EndDate = '2100-12-31'
SET @id = 0
SET @date = DATEADD(dd, @id, @startdate)
BEGIN TRY
BEGIN TRAN -- Start of a transaction
WHILE @date <= @enddate
BEGIN
INSERT
INTO scbDate
SELECT @date CalendarDate,
DATEPART(dd, @date) CalendarDayMonth,
DATEPART(dy, @date) CalendarDayYear,
DATEPART(dw, @date) CalendarDayWeek,
DATENAME(dw, @date) CalendarDayName,
LEFT(DATENAME(dw, @date),3) CalendarAbbDayName,
CASE
WHEN DATEPART(dw, @date) IN (1,7) THEN
'Y'
ELSE
'N'
END CalendarWeekday,
DATEPART(ww, @date) CalendarWeek,
'Week ' + RIGHT('0' + DATENAME(ww, @date), 2) CalendarWeekName,
DATEPART(mm, @date) CalendarMonth,
DATENAME(mm, @date) CalendarMonthName,
LEFT(DATENAME(mm, @date),3) CalendarAbbMonthName,
DATEPART(qq, @date) CalendarQuarter,
'Q' + DATENAME(qq, @date) + ' ' + DATENAME(yy, @date) CalendarQuarterName,
DATEPART(yy, @date) CalendarYear,
DATEPART(dw, @date) FiscalDayWeek,
DATEPART(yy, @date) FiscalYear,
DATEPART(mm, @date) FiscalPeriod,
DATEPART(ww, @date) FiscalWeek,
DATEPART(qq, @date) FiscalQuarter,
0 CurrentDay,
@LoadId LoadId,
'STA' DataSource
SET @id = @id + 1
SET @date = DATEADD(dd, @id, @startdate)
END
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN -- Rollback Transaction
END CATCH

Thanks
Sutha

Building Dynamic Excel Connection String

2007-07-23T12:00:00.000-07:00

If you wish to build the Excel connection string dynamically inside SSIS and assign it to Excel Connection Manager you can use the following expression.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::strWorkingFolder] +"\\"+ @[User::strExcelFileName] + ";Extended Properties=\"" + "Excel 8.0;HDR=YES" + "\""+";"

Where strWorkingFolder and strExcelFileName are user variables. For example, strWorkingFolder = C:\Temp and strExcelFileName = Sales.Xls

Please note the above syntax is for Excel 97-2003

Thanks
Sutha

SQL Server 2005 Best Practices Analyzer

2007-02-22T00:32:00.000-08:00

All

SQL Server 2005 Best Practices Analyzer (February 2007 CTP) is available now for Download.

Thanks
Sutha

Download Feb 2007 Books Online

2007-02-19T10:19:00.000-08:00

There is an updated version (February 2007) of Books Online available for download.

Thanks
Sutha

SQL Server 2005 SP2 is available for download

2007-02-19T10:14:00.000-08:00

All

SP2 is available for download now.

Thanks
Sutha

OLE DB Source - SQL Command

2007-02-13T01:09:00.000-08:00

I came across something simple on OLE DB Source last week. I was using “SQL Command” as Data Access Mode. Most of source SQL statements were simple, such as

SELECT oid,baseid
FROM STG_PAS99
WHERE baseid = ?

For the above SQL I can pass a parameter without any problem.

For one reason or the other I had to write a source SQL something like this. Yes I do know that I don’t need inside parameter if I have one outside. I am using it to explain my point plus it does improve performance.

SELECT oid,baseid
FROM STG_PAS99
INNER JOIN (
SELECT MAX(loaded), oid, baseid
FROM STG_PAS99
WHERE baseid = ?
GROUP BY oid, baseid
)
WHERE baseid = ?

If I enter the above SQL into “SQL Command Text” and click on Parameters I got the following message

As the error message suggests I used the SQL Command from variable to get around that issue. If you don’t know how to do that please have a look at Jamie’s Blog

Thanks
Sutha

64 Bit Oracle Issues

2007-02-12T03:33:00.000-08:00

One my friends has done a brilliant post on 64 bit Oracle drivers and connectivity issues. Well worth having a read.

http://stevemchugh.blogspot.com/2007/02/adventures-of-ssis-and-oracle-in-64bit.html

Thanks
Sutha

One Day Maybe....

2007-02-09T00:34:00.000-08:00

I dont like E-Bay. I think they charge too much.

Hopefully there is someone emerging in the market.

http://www.mobadoo.com/pages/home/home.aspx

Thanks
Sutha

Executing Batch Files Using SQL Agent

2006-08-03T01:48:00.000-07:00

I wrote a simple batchfile which takes a copy of our source files. I wanted to execute that via SQL Server Agent. Batchfile works fine when I execute on a command prompt.

To get the batch file to execute via SQL Agent I have to use CALL command.

In my scenario
CALL E:\Data\FTP\CopySourceFiles\SourceFile_Backup.bat

Thanks
Sutha

Shredding Recordset Object In ForEach Loop - While Using Checkpoint - Bug Fixed

2006-08-01T04:37:00.000-07:00

Following my earlier post, I raised a bug and I have been informed my Microsoft Connect team this bug has been fixed and the fix will be available on SP2. Quote from MS Connect below:

"The bug was identified as a same known issue which was fixed already for our next release, however, after considering the problems involved in this bug issue, we decided to also port the fix into our next Yukon service pack, SP2. You should be able to get the fix after applying SP2."

Thanks
Sutha

Why am I Processing Index Separately?

2006-07-31T23:14:00.000-07:00

Someone asked me why am I processing index separately in my earlier post. Why didn't I do it inside the first measure group foreach loop.

Yes, it would have worked.

There is a reason for splitting it up, is because that we have a measure group with billions of rows. Building aggregations on that measure group will take some time. I do not want to hold rest of the measure group partition processing.

Therefore I process the cubes first and make them available to users as quickly as possible and then build the aggregations. I have informed the users that some of the reports might not run as fast as you would expect straight after refreshing the data, until the aggregations are rebuilt.

Thanks
Sutha

Processing Dimensions, Partions and Re-Building Aggregations

2006-07-31T07:08:00.000-07:00

This package is very similar what Jamie has already posted. I have written a simple package which process all dimensions and process 3 specific partitions (current fiscal period, current fiscal period -1 and current fiscal period -2).

Once I have processed all the dimensions and 3 partitions in selected measure groups, I rebuild aggregations (using Process Index) for each measure group.

I have used XMLA and Execute DDL statement tasks to achieve this.

You can download the sample package from here. Please note that I have set DelayValidation = TRUE, to avoid any errors.

Thanks
Sutha

Gotcha When You Process Update Dimensions

2006-07-31T00:52:00.000-07:00

We can process cube related members via DDL statements. Jamie posted a simple package which process all the dimensions and measure groups.

Jamie followed it with a question in the forum and did a post. I got interested in that and followed the thread and got it confirmed the following:

If your do process update on dimensions you are likely to lose aggregation on the cube.

You have to re-build aggregations using "Process Index" option.

I will post a simple package later on today which process all dimensions, specific partitions in a given measure group and finally it will build the aggregations for the cube.

Thanks
Sutha

64 Bit Performance Issue Resolved

2006-07-23T23:33:00.000-07:00

With the hotfix from Microsoft we have resolved this issue. It was a bug, which was fixed with hotfix. If anyone has performance issue contact Microsoft and get hotfix.

Thanks
Sutha

Continuing on 64 bit SSIS issues - Hopefully Resolved

2006-07-14T09:51:00.000-07:00

Using the example I have posted on my previous post, Microsoft have reproduced the issue I have had on a 64 bit platform.

Infact some of the issues were identified before. Microsoft have been working on the solution for last couple of months (service pack 2). Microsoft released a hot fix (which is not available for public yet, I dont know when it will be available either) for us to test.

After initial testing the hotfix seems to have resolved the issue on a 64 bit box. I will be doing an extensive testing on Monday and will post my findings.

Thanks
Sutha

Sample Project / Package to Re-Pro 64 Bit Issue

2006-07-07T04:18:00.000-07:00

Microsoft said, they couldn't reproduce the problem from Scratch, but I have. I have sent it to MS as well. I have attached the whole project (with all the packages) and configuration files.

Master Package
[1] Creates a simple table in tempdb database if it doesn't exist else it will truncate the table.
[2] It will populate 25 records into that table.
[3] SQL will get those 25 records and assign them to a Object variable.
[4] Foreach Loop (ADO) and set the values of each row to couple of variables. Inside that foreach I have an execute package task, which will call another package (in this case Pkg_Sample25.dtsx)

I have got 52 packages in this project. All the packages except Pkg_Master is exactly same. They all have one single Execute SQL Task, which has a SQL statement as "SELECT 1".

Every package has connections for all 51 packages and 55 flat files. All these are configured in the configuration file (64BitConfig.dtsConfig) which is located in a directory called "C:\RePro\Config". Please note you would need to change servername in the configuration file before you execute the package. You will need to change flat file directory and packages directory if you put them anywhere other than "C:\RePro".

If I run a package called "Pkg_Master" on my laptop (32 bit), it executes under 50 seconds. If I execute same package on a 64bit machine it executes just under 7 minutes (6min 42 Secs).

If anyone wish to download the sample project and try it on there 64 bit box, you can do it from here.

Thanks
Sutha

Rambling on about 64 bit issue I had...

2006-06-28T03:57:00.000-07:00

I have done various tests. I developed a simple package (which moves files from source directory to working directory) to test timing against RTM, and the same process against SP1 and then Hotfix. The differences were negligible, between different builds. So I opened my original package and disabled all the tasks except the one I have built in the simple package and executed it and the difference was huge.

Simple package took around ~ 18 seconds to moves files etc where my master package took around ~ 270 seconds. Infact it was 15 times slower.

Then I went back drawing board as they say and looked at what was going on, specially what are the differences between simple package against the master package originally developed. My first instinct was event handlers and I disabled them and the performance was negligible . Second thought was transaction and that didn't make any difference. Next thing came to my mind was checkpoint and that wasn't an issue either. At this point I was going mad.....!!!!!!

While checking both packages visually I noticed something quite different. The simple package has only 2 connections where the master package had quite a few (close to 100 connections). Then I reproduced it and it seems to be the problem.

So, my initial conclusion is that Execute Package Task seems to be resolving package configurations each time it loads / execute a package on a 64 Bit environment. The behaviour seems to be totally different on a 32 bit environment.

We have logged a call with MS about this.

Keep you posted.

Thanks
Sutha

SSIS Performance on 64 Bit Sucks....

2006-06-23T00:23:00.000-07:00

I have been working on a project last few months. I have developed the project on my laptop, which is resonably powerful. It runs through fine within 9 mins with sample data set. If I replicate the same environment on a 64 Bit machine with 32 Bit Win 2003 and SP1, it takes just over 7 mins. If I rerun it on a 64 Bit machine with 64 Bit Win 2003, it takes between 21 and 24 mins. Executing the packages via dtexec on a command prompt.

I have got "Run64BitRunTime" set to "True". I dont know any other settings I should watch out when executing on 64 Bit.

My laptop(dual core) has 2 GB of memory. This server has 16GB of memory. Server is 4 way Dual Core box.

I have few things running on my laptop, where server is absolutely dead, nothing is happening on it. Only difference is that the source files, databases are on C: of my machine, where source files are on C: on the server, but the databases are on different disk and one of the source database (ODS) is on the different server all together.

I am running exactly same packages on my laptop and dev server.
My packages uses various componts such as OLE DB Source, OLE DB Destination, SQL Server Destination, Lookup, Merge, Sort, Aggregate, Script Task, SQL Task, Execute Package Task, For Each Loop, Data Conversion, Derived Column, RowCount, Union All, OLE DB Command, Conditional Split, Multicast and CHECKSUM.

I audit my packages via event handler. I execute SQL Task to insert record into a table on PreExecute, PostExecute and OnError. I also use Checksum from Darren & Allan. I don't think that's the problem as everything is slow in general.

We are trying various options and we are speaking to Microsoft as well. As we uncover more things I will update my blog.

Thanks
Sutha

UK BI UserGroup Meeting

2006-06-23T00:07:00.000-07:00

Last night we had a successful first BI user group meeting at Microsoft Reading. Turn out for the meeting was excellent. Most of the BI consultancy representatives were there.

At last I have met people like Jamie Thomson and Christian Wade face to face after speaking to them for a long time on the web.

Chris Webb, Mark Hill and Simon Sabin presentations were excellent. Hopefully we can have more sessions like this.

Slides and sample codes from this session will be available soon @ SQL Server User Group site.

Thanks
Sutha

Upgrade to SQL Server 2005

2006-06-05T07:07:00.000-07:00

If you are going upgrade from SQL Server 7 / 2000 to SQL Sercer 2005 you are highly recommended to use the following tools before you upgrade.

You have to run the Microsoft Upgrade Advisor which can be downloaded free. Microsoft SQL Server 2005 Upgrade Advisor analyzes instances of SQL Server 7.0 and SQL Server 2000 in preparation for upgrading to SQL Server 2005. Upgrade Advisor identifies deprecated features and configuration changes that might affect your upgrade, and it provides links to documentation that describes each identified issue and how to resolve it.

Scalability Experts have developed a great tool, which helps you during upgrade process. You can download the SQL Server Upgrade Assistant for free. This tool analyses the impact associated with upgrading SQL Server 2000 installation to SQL Server 2005.

Thanks
Sutha

Cross database ownership chaining

2006-06-05T01:11:00.000-07:00

One of the Gotcha when you migrate a database from SQL 2000 to SQL 2005 "Cross database ownership chaining" is turned off by default.

You would need to enable it manually once you have restored the database.

This option is available under "Server Properties" in "Security" tab.

You can either Execute the SQL below or you can change the server property using Management Studio.
EXEC SP_DBOPTION "(DB Name)","DB CHAINING", "TRUE"

If you wish to check is the database chaining is ON / OFF you can using the following statement
EXEC SP_DBOPTION "(DB Name)","DB CHAINING"

Thanks
Sutha

SQL Server 2005 Directory Structure

2006-06-02T13:06:00.000-07:00

Nothing too serious, it is something everyone need to be aware.

Generally if you have full / default installation you will get the directory structure like shown below:

When you conduct default installation SQL Server (data / backup etc) will be installed MSSQL.1 directory. Analysis Services related stuff in MSSQL.2 dorectory and Reporting Services related stuff in MSSQL.3 directory.

What you have to be aware is that for example if you just install Analysis Services on a new server it will install under MSSQL.1 directory. On the other hand if you install another instance of SQL Server, Analysis Server and Reporting Server in the above scenario, they will get MSSQL.4, MSSQL.5 and MSSQL.6 directory as 1,2 and 3 already exists as shown above.

Specially you have to be aware if you use within your application / reports where Reporting Services sits etc.

The directory names would differ between servers depending on installation.

Thanks
Sutha

Partitions & Identity Columns

2006-05-31T06:48:00.000-07:00

Question
When I switch the data to the main table partition, it is ignoring the Identity specification column on and duplicating the values in the Identity column.

I did a search on Books on Line and all I could see is Performing partition switching can introduce duplicate values in IDENTITY columns of the target table, and gaps in the values of IDENTITY columns in the source table. Use DBCC_CHECKIDENT to check the identity values of your tables and correct the values if you want. But it doesn't tell me how to use DBCC_CHECKIDENT.

Answer
When you are inserting data into your empty table, reseed that table before you start with the max+1 value from the main table, a quick query should work that out.

Insert your data and then push it into your main table, no issues.

Scenario
My table partition is based on the date. I've created my Temp tables dynamically based on the date. And before inserting the records into Temp table I've retrieved the (max + 1) value of the Identity column from the main table and reseeded the Identity of the Temp table with DBCC CHECKIDENT

Thanks
Sutha

Kimball Group Webcasts - MSEevents

2006-05-26T00:44:00.000-07:00

Following their book on SQL Server 2005, Kimball group is going to conduct 4 TechNet webcasts. It should be well worth listening material. You can rigister for those webcasts using the links below:

Microsoft Business Intelligence (BI) Using the Kimball Method
Designing a Scalable Data Warehouse / Business Intelligence (DW/BI) System
Using SQL Server 2005 Integration Services to Populate a Kimball Method Data Warehouse
Getting Started with Data Mining

Thanks
Sutha

Shredding Recordset Object In ForEach Loop - While Using Checkpoint

2006-05-18T23:28:00.000-07:00

I think this got to be one of the annoying bugs within SSIS.

I have a SQL Task which returns the result set to a variable called "Pkg_Obj_ResultRet".

Then using Foreach Loop container I shredd the result set. I have a Foreach ADO Enumerator and the Enumeration Mode is set to Rows in the first table. Shredded results are mapped to 2 variables called Pkg_Str_SSISStreamName and Pkg_Str_SSISVarNameControlFile.

Inside Foreach Loop Container I have "Execute Package Task" which execute package using these variables. Here comes the fun.

The iteration works fine first time, runs the second iteration successfully and then it try to run second iteration in an infinite loop.

This happens when you have SaveCheckPoints option set to True. If you want to use checkpoint, you can't get this to work unless you set the Foreach Loop TransactionOption = Required.

I think this is a bug and I have raised it with Microsoft.

Thanks
Sutha

Cumulative hotfix package (build 2153) for SQL Server 2005

2006-05-18T07:36:00.000-07:00

As we all know there were we some issues with SP1. Microsoft released hotfix package, which can be downloaded from here.

Thanks
Sutha

Getting SSIS Packages To Run On A 64 Bit Box

2006-05-16T23:53:00.000-07:00

We had some serious issues getting the SSIS packages to run on a 64 Bit platform which connects to Oracle database.

Mark Hill managed to get this bloddy thing to work. He wrote a simple document explaining what he did. I thought I would share it with all of you.

-----------------------------------------------------------------------------------
When we first approached this, I would have thought it should have been as easy as installing the 64 bit drivers from the Oracle CD and then copying a package across and running it.

This approach did not work, no matter which driver was selected at design time (Oracle Client, Dot Net Provider etc) we could not establish a connection.

When opening up the package on the server with the BIDS environment we found that we were getting bad image messages or init failures for the driver from the design dialog.

Going back to basics, I thought this through a little.

If BIDS is a 32 bit process (which it is), it cannot load the 64 bit drivers, therefore that would go someway to explaining the bad image error message.

Solution?! Let’s try and install the 32 bit Oracle client on the 64 bit box.

Install worked, however the 32bit drivers also failed to load under BIDS.

By this time I was pulling my hair out for real.

Next we developed a package on the 32bit laptop, was sure to choose the Oracle driver, copy it across and run it. No, the 64 bit run time of SSIS cannot run this package.

Back to the drawing board (web search)

Three pieces of interesting information (there is actually four)

1) There are two versions of SSIS installed on a 64 bit by default, one in x86 programs and the other in programs
2) The 32 bit Oracle drivers do not like x86 in their path name (may explain the odd 32 bit driver errors under BIDS)
3) A package can be told which subsystem to use, package properties, 32 bit or 64 bit.
4) If you use scripting code, ensure that the flag is set in the properties to allow dynamic recompile, or else you will get an error message loading the vbscript code on 64 bit.

Reality

Well there are two versions of SSIS, which you can run from the command line prompt, dtsexec.exe, if you use the /f option then you will be able to specify a filename for it to run.

The dynamic code flag, sometimes does not work, if you have this problem, open the package on the 64bit box and then resave it.

Eventually after all of this we were able to copy across a package and get it to work accessing an Oracle box.

All in all a bloody nightmare.

-------------------------------------------------------------------------------------

Thanks Mark

Sutha

First BI User Group Session

2006-05-08T06:07:00.000-07:00

BI User group has been set up by Chris Webb and Tony Rogerson. The first meeting is set for 22/06/2006. Register here if you wish to attend. Look forward to see you there.

Thanks
Sutha

Installing SQL2005 SP1 - checking for locked files via Windows Installer file sqlrun_sql.msp

2006-05-04T00:05:00.000-07:00

Please note that you have to shutdown all the services except SQL Server service, before you start SP1 installation.

When I tried to install SP1 yesterday I came across a small problem. I had all SQL related services shutdown, except SQL Server service (It is required to run when SP1 installation starts - Installation process will shut SQL Server service later on during installation). I came across this problem. During Database Services, installation process hung stating the following message.

checking for locked files via Windows Installer file sqlrun_sql.msp

I tried few things and couldn't get it to work.

Eventually I moved all the user created database files (MDF and LDF) to a different directory from SQL Data directory (in my case - C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data) to a temp directory.

Then I re-installed SP1 and it went through without any problems.

Thanks
Sutha

2 Digit Date Format in SSIS

2006-04-27T01:31:00.000-07:00

If I run the following SQL into SQL Server via SSMS it will load "31-DEC-49" as 2049-12-31.
INSERT
INTO Stg_StockProductCostPrice
VALUES (0,9999,0.99,'15-NOV-75','31-DEC-49')

But if I have the same data in a file and load it via SSIS it is loading into the database as 1949-12-31, instead of 2049-12-31.

There is a property called "Two Digit Year Cutoff" against the server. It is defaulted to 2049.

SSIS uses the Windows settings. The work around is to use derived column and get what you wanted as shown below:

(DT_DBTIMESTAMP) (SUBSTRING(CostPriceEndDate, 1, 7) + ((DT_I2)SUBSTRING(CostPriceEndDate, 8, 2) > 10 ? "19" : "20") + SUBSTRING(CostPriceEndDate, 8, 2))

**** PLEASE READ BELOW IF YOU HAVE SQL 2005 SP1 ****

http://wiki.sqlis.com/default.aspx/SQLISWiki/DateConversions.html
http://blogs.conchango.com/jamiethomson/archive/2006/04/26/3870.aspx

Thanks
Sutha

SQL 2005 Service Pack 1 and April Books Online Available

2006-04-26T01:15:00.001-07:00

Service pack1 has been available for download few days now. Brian Knight has listed the enhancements / features related to SSIS which can be found here. You can get the full details from the following knowledge base articles KB 913090 and KB 916940.

SQL 2005 Books Online (April 2006) is available now for download.

Thanks
Sutha

Persistent Teams Up With Microsoft SSIS

2006-04-23T05:46:00.000-07:00

Following up my earlier post about Oracle connector performance within SSIS, Persistent have released a press release, which can be found here. They have implemented their custom component at Moffit Cancer Centre.

Thanks
Sutha

Microsoft Bought ProClarity

2006-04-04T00:28:00.000-07:00

Microsoft annouced yesterday that they have acquired Proclarity. You can access the press release here.

Where does this leave Panorama and IntelligentApps. Microsoft might give at least some of it free.

Very interesting move by MS.

Thanks
Sutha

TransactionOption In SSIS

2006-03-31T07:13:00.000-08:00

I had a 3 simple packages get called from master package. First package populates time dimension (stored proc), second one populates transaction type dimension(stored proc) and the final one populates date dimension.

I set the TransactionOption = Required in the Master package, every thing else (package & component) set TransactionOption = Supported.

I have been testing transactions, basically I made time and transaction type dimension to fail and the package did roll back. I am stuck in the date dimension.Within Date dimension I got a stored procedure which populates calendar date attributes into Wrk_Date table. Then I have a data flow task which reads from thats working table, but it cant access it. I tried running SP_WHO2 command, and the status is SUSPENDED, being blocked by id -2.

I was going through step by step and identified the reason for locks. During step 2, if date table exist in stage database it TRUNCATES the table else it creates the table. Truncate statement seems to hold the lock and it is not releasing it further down the line. I have managed to get the rollback to work after removing truncate.

Therefore conclusion is you can't have Truncate and cant populate the same table in a single transaction in SSIS.

Thanks
Sutha

Most Powerful Sort Component So Far

2006-03-20T23:25:00.000-08:00

As everyone knows the SSIS sort component out of the box is not known for its higher performance. There are couple of third party Sort components available.

ExtraSort component was available for a while. It is a freeware. You can access ExtraSort here. Ivolva Digital is behind this component.

During last few days another Sort component was made available. This one is called NSort. You can download the trial version here. Ordinal Technology Corp is behind this component. This is not memory hungry as it uses temporary files. It accepts multiple inputs and provides multiple outputs.

Thanks
Sutha

Single Source File With Multiple Files

2006-03-15T04:42:00.000-08:00

One of the requirments I came across today was interesting. Each outlet has a specific menu file. Therefore we receive aournd 2000 menu files. Each menu file from system does have 8 different file types. I need to shread the data send 8 files out to different destinations. First 2 characters identifies the file type.

I have written a script component to do this. Script Component is the source. I have attached the sample package and sample text file I have used. In this example I have used 2 types of files just to prove the point that it works. You can download sample package from here.

Thanks
Sutha

Null field in Fixed Length File Which Should be an Integer Column

2006-03-14T02:53:00.001-08:00

The project I am currently working get source files in a fixed length format. Some fields allowed to have nulls, and some are integer fields.

I ran into a problem if I define the field as integer column in the connection manager. The reason for that is that those fields which supposed to be nulls, are not really nulls, they are spaces. To overcome this I did the following

* Define the column as DB_STR in the flat file connection manager.
* Once the data is read from the data source I added Derived Column in the next step.
* I added a new derived column with the following expression
(DT_UI4)((TRIM([GrpId]) == "") ? NULL(DT_WSTR,0) : [GrpId])

The above statement will TRIM the spaces out from a column called GrpId and if GrpId is equal to “”, then it will set it to be null, else it will assign the value of GrpId. Finally (DT_UI4) statement will convert the return result set to Four Byte Unsigned Integer.

Thanks
Sutha

Date Source File in US Format. Database Locale is Set to British. Need to Get Surrogate Key?

2006-03-14T02:22:00.000-08:00

If you are receiving the date source file with US format (MM/DD/YYYY). It may sound odd, but in the project I am currently working on I had to overcome this issue. The date dimension is stored in the table using DD/MM/YYYY format. You wish to get Surrogate Key using Lookup feature within SSIS.

Source File As shown below:
Col1 Col2 Col3 Col4
1 USD 01/01/2006 0:00 0.99980004
1 USD 01/02/2006 0:00 1.000900811
1 USD 01/21/2006 0:00 0.99960016

I would suggest you define the column3 as STRING in the flat file connection manager. Once you have defined the data type you can use Derived Column component to convert the string into the correct format as shown below :
(DT_DBTIMESTAMP)(SUBSTRING([Column 2],4,2) + "/" + SUBSTRING([Column 2],1,2) + "/" + SUBSTRING([Column 2],7,4))

You can download the sample package from here.

Try to avoid using DATEPART. If you wish to use DATEPART then you would have to set the source field (in this example Column 2 need to be defined as DATETIMESTAMP instead of STRING). The statement below will give you similar result, but there is GOTCHA.

(DT_STR,2,1252)DATEPART("dd",[Column 2]) + "/" + (DT_STR,2,1252)DATEPART("mm",[Column 2]) + "/" + (DT_STR,5,1252)DATEPART("yyyy",[Column 2])

The above script will produce the following result set.

1 USD 01/01/2006 0:00 0.99980004 01/01/2006 - This is right
1 USD 01/02/2006 0:00 1.000900811 01/02/2006 - This is NOT right
1 USD 01/21/2006 0:00 0.99960016 21/01/2006 - This is right

First of January worked fine. Second of January didn't work fine. Twenty first worked fine. Therefore be aware when you use it.

If you receive date in the following format 01-JAN-2006 then you can use the following syntax to convert it to date. You would need to define the source as String.
(DT_DBTIMESTAMP)(SUBSTRING([Column 2],1,2) + "/" + SUBSTRING([Column 2],4,3) + "/" + SUBSTRING([Column 2],8,2))

Thanks
Sutha

Moving Files - Source to Working Dir

2006-03-09T09:46:00.000-08:00

The project currently I am working on had the following directory structure.

C:\Data\SourceFiles - This dir will have source files.
C:\Data\ArchiveFiles - Once the data is loaded move it from working to archive dir.
C:\Data\WorkingFiles - Move source files to working dir.
C:\Data\ControlFiles - Single control file per dimension.

One of the dimension we have is payment type which is made out of 2 files, such as paytype and paytygrp. Each time we receive files with different names, but first 8 characters of the file is fixed. Some of our dimension has upto 24 different files, therefore we decided to use a control file with the delimiter of "-". In the control file we have source file name and final source file name.

One of my collegue(Rastko) wrote a script to move the files from source directory to working directory. You can download the entire script here.

Thanks
Sutha

SSIS - Clustered Service

2006-02-20T22:52:00.000-08:00

As everyone knows that SSIS is not Cluster-Aware. There is a work around for it.

Kirk did a brilliant post with the help from Ranjeeta and Nick. You can access the post here.

Thanks
Sutha

More New SSIS Transforms

2006-02-15T09:34:00.000-08:00

Performance When Loading Data into Oracle Using SSIS

2006-02-14T10:00:00.000-08:00

It is a known fact that the Microsoft OLE DB for Oracle / Oracle's their own OLE DB drivers does not perform well during data load. They are extremly slow.

You can see vaious people have difficulties with this provider(s).
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=130786&SiteID=1
http://forums.microsoft.com/msdn/showpost.aspx?postid=117014&siteid=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=124921&SiteID=1&mode=1

Donald has written a blog about the stance within Microsoft. You might find it surprising what are about to read:
http://www.sqljunkies.com/WebLog/donald_farmer/archive/2005/03/13/8819.aspx

The solution is provided by a company called Persistent Systems. The numbers are impressive with their Oracle Bulk Load Connector. Check how impressive the numbers compares for yourself at http://www.persistentsys.com/products/ssisoracleconn/ssisoracleconn.htm

I am putting all these in here for my furture reference.

Thanks
Sutha

SQL2005 Profiler Trace Definitions

2006-02-13T00:44:00.000-08:00

While I have been involved in PoC, I have learnt lot about SQL Profiler and Performance Monitor.

There are 6 Trace files available out of the box, which is located at C:\Program Files\Microsoft SQL Server\90\Tools\Profiler\TraceDefinitions\1033. These definition files are in XML format. There is defintion file for SQL Server, Analysis Services and Integration Services.

This XML will give you the textual definition of a SQL Profiler event class number that I have captured.

Thanks
Sutha

Incremental Cube Processing

2006-02-13T00:19:00.000-08:00

AS2005 will NOT process aggregations automatically when you process the partition. During used partition processing it used to process the data and it also built the aggregations for that partition. It is changed in AS2005. You can process partition incrementally using "Process Incremental" option. If you want to build index you have to do that using "Process Index" option.

If you wish, you can put these two in 2 separate steps within SSIS after data is loaded.

Its all about how quickly you want the data presented to the users. If you do the Process Incremental first and made the data available for users first and then build the aggregations while users are using the data. This way will give you maximum availability, but impacts performance while building aggregations and indices. UI in SSMS does not support "Process Incremental" and "Process Index" in one single transaction. You can achieve that using simple XMLA scripts as shown below:
http://www.smart-bi-solutions.com/BlogStuff/incremental_process.xmla

This example uses Adventure Works. ProcessAdd and ProcessIndexes are the key things to look out for in the XMLA doc.

Thanks
Sutha

Can you load 74 million rows in 2 Minutes using SSIS (Bulk Insert Task)?

2006-02-12T23:41:00.000-08:00

The simple answer is “Yes”. I have loaded 74 million rows in just under 2 minutes.

I was working on a Proof of Concept (PoC), where I had to load about 10 billion rows into a fact table. Each file had 6,170,000 rows and they belong to single day transactions in the outlets (~3000). As it was PoC we generated each day’s fact data into text files and RAR them up. I had to load this data very rapidly into the table and then only we can process the cube and continue with the rest of the testing.

We created the database with the maximum possible size, in other words it does not have to grow during data load. SQL Server Table was partitioned by month. We had 61 partitions (60 partitions for 5 years worth of data + 1 empty in the end). We also set the database to go across 3 file groups. We built the table without an index (I know that having an index will slow down the data load). Loaded the data using 8 Way Unisys ES7000 with 32GB of memory with 1.5GHz Processor.

I have used 12 parallel Foreach tasks to load data. I was loading 4 files (24,680,000 rows) into a single file group. You can see what I have done below (12 of those):
http://www.smart-bi-solutions.com/BlogStuff/09_02_06_Pic1.JPG

To get the best out of the Bulk Insert Task, we did the following settings.
Set the Code Page to 1252 (ANSI - Latin I)
Set the batch size to 1,000,000. Varies depending on the memory.
Set Options – Uncheck Check Constraints (default)
- Check Table Lock. It is not physical lock on the table.

Once the whole data set was loaded we built a clustered index on DateId, which took on ES7000 5 hours to build. It took around 1 hour to drop clustered index on this fact table.

If you have any questions on this feel free to contact me on sutha@smart-bi-solutions.com.

Thanks
Sutha

Calendar Transform and UnDoubleOut

2006-02-06T01:46:00.000-08:00

There are couple of SSIS components which we can download from Microsoft site for free.

UnDoubleOut is an SSIS dataflow component that removes qualifiers from quoted text, either in place, or via the creation of a new output column. This component removes quotes that bracket input strings if present. Replaces doubled quotes with single quotes inside the string.
http://www.microsoft.com/downloads/details.aspx?familyid=91E24D1D-ACF0-45B1-A9A5-9EA1C7BD0463&displaylang=en

CalendarTransform is an SSIS dataflow transform component that generates standard calendar attributes.
http://www.microsoft.com/downloads/details.aspx?familyid=E603BDE7-44BB-409A-890F-ED94A20B6710&displaylang=en

Thanks
Sutha

How can you validate XML Schema against incoming XML document?

2006-02-02T10:24:00.000-08:00

For example if you are getting customers in an XML file format to load into your data warehouse using SSIS. Initial file you have received was in a perfect format. You built Schema using a specified XML location. Generate the schema using “Generate XSD” and saved it to a given location. You mapped the XML Output to a destination table in SQL Server. All ran through fine during first week. Second week the XML file that was sent to you by source system has been changed. You want to validate the XML source against XML Schema. How can I do that? If there is a difference between the source file and schema I want to fail the package.

There is a component called XML Task in Control Flow, which could help you to do that. You can do the following using the XML Task like Diff, XSLT, XPATH, Merge, Patch & Validate.
http://www.smart-bi-solutions.com/BlogStuff/020206_Pic1.jpg

The Validate is the Operation Type you would you to validate the XML Source file against XML Schema. Once you have selected the OperationType, make sure XSD is selected in ValidationType (which is default). Please be aware that the FailOnValidationFail is set to False by default. If you leave it with default value for FailOnValidationFail, the package will NOT fail even if the XSD and XML doesn’t match. Therefore you need to change FailOnValidationFail value to True if you want your package to fail.

Thanks
Sutha

Steps Involved in Loading Text File Into SQL Server Using SSIS

2006-01-26T12:26:00.000-08:00

How many steps involved in loading a simple text file straight into a SQL Server table using SSIS?

6 steps involved to complete this process and they are listed below:
1. Read data from the text file – Disk speed is significant.
2. Load data from the text file into the transport (SSIS) – Network speed is significant.
3. Open a transaction on SQL Server – Server contention is significant.
4. Pass data to the SQL Server interface
5. Load the data into SQL Server – Memory resource is significant.
6. Commit the transaction – Disk performance is important.

When loading from text to typed SQL Server columns (int, datetime etc) the data conversion must take place somewhere either step 2 or 5 in this above example. Is it necessary to use SSIS for this? Are the operations simple enough for BCP / BULK Import? With low volume of data the overhead of starting up SSIS package process may offset any performance gain over BCP or Bulk Insert. Where should SSIS run (Server1 or Server2)? SSIS On Server1 – Competes for memory and processor resources with other applications. Will data conversion on server1 reduce or increase the volume of data transferred across the network? But can not use the fast SSIS SQL server Destination. Where should SSIS run (Server1 or Server2)? SSIS On Server2 – Competes for memory and processor resources with SQL Server. Will pulling text over conversion be expensive (also consider transferring the file unparsed to server2 and read it locally from there). You can use the fast SSIS SQL Server Destination. If you were going to run in two servers you would need SQL Server licensee in both servers.

Thanks
Sutha

SSIS Service

2006-01-15T12:15:00.000-08:00

SSIS Windows Service – Monitors and manages running as well as stored packages, via SQL management studio.

It is installed when you install SSIS. Service is not required to design or execute packages. Windows events for service (Service start, service fail to start, package started, package stopped…).

In Management Studio you can see Stored Packages. When you create a folder(s) they are stored in MSDB database. In Management Studio you can see Running Packages.

As default it only shows the name(s) of the package(s) which are running at present. If you click on a “Report” button on the top menu, it will display information such as Package name, Executed by, Execution Instance Id, Execution Started, Execution duration, Package ID and Description. In Running Packages you can select the package and right click and “Stop” the package.

When you connect to SSIS server within Management Studio, you can select the server and Right click on it and select “View Logs”. This is limited Windows events.

Thanks
Sutha

How does SSIS work?

2006-01-15T12:06:00.000-08:00

SSIS is a replacement product to SQL 2000 DTS. New archutecture and design. I have tried to give a summary explaining how SSIS works :

All data is loaded in tabular form into a memory buffer.
Data sources can be diverse, including custom or scripted adapters.
Transformation components shape and modify data in many ways.
Data is routed by rules or error conditions for cleansing and conforming.
Flows can be as complex as your business rules, but highly concurrent.
Finally data can be loaded in parallel to many varied destinations. As far as possible no copying of data (even in memory)

All in all more memory you have the better it is for performance of SSIS.

Thanks
Sutha

Cannot Set Connection String For Bulk Insert Source Connection

2006-01-11T05:06:00.000-08:00

I want to go through a directory in my local machine and get files and assign the Fully Qualified File Name to a User Variable using Foreach Component from SSIS.

I want to use that variable as a connection string within Expression in Bulk Insert Task Source Connection, Data Flow Task and File System Task. It is working fine within Data Flow and File System task. It is NOT working for Bulk Insert.

- Expressions ConnectionString @[User::FileName]

This is the expression I want to use within ConnectionString in Bulk Insert, File System Task and Data Flow Task. Same expression works fine in all except Bulk Insert. You can see the same issue being raised in Forum.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=188886&SiteID=1

From the forum I found out that I need to use Script within Foreach to set the connection string and then call Bulk Insert. You can set the connection strings as shown below (thanks to Mark)

------------------------------------------------------
Dim connmgr As ConnectionManager = Dts.Connections("[your connection name]")connmgr.ConnectionString = Dts.Variables("User::filename").Value.ToString()connmgr.AcquireConnection(Nothing)
------------------------------------------------------

I hope this helps
Thanks
Sutha

SSIS Package Template

2006-01-06T08:00:00.000-08:00

The project I am working currently needed a template all ETL developers can use as a standard package. This will be the beginning point of each package. I have written a simple package to act as a template.

I wrote the package and called it as “Pkg_Template.dtsx”. Template package(s) need to locate in certain location for it to work. You would need to put this SSIS package in the following location on your machine

%ProgramFiles%\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems

Once you have placed the template package in the above location you can open the template package by Right click on a project and select “Add” and “New Item”. This will bring a screen like the one in the link below :
http://www.smart-bi-solutions.com/BlogStuff/06012005_Pic1.jpg

As you can see from the above screen shot the Pkg_Template does appear within BIDS template section. You can give new name at this point or it will give a default name of “Pkg_Template1.dtsx”.

The screen shot in the link below explains what template package does:
http://www.smart-bi-solutions.com/BlogStuff/06012005_Pic2.jpg

The above template does the following:

Retrieves LoadId from SQL Server using Stored Procedure.
Checks for LoadId Validity.
Configures SSIS auditing using Execute SQL Task (Expressions),
Uses “Environments.dtsconfig” to set the SQL Server connections.
Package is configured to log to Windows Event Viewer if the package errors out.
Event Handler to do auditing, for example, it logs what time task started and completed etc.
I have put some comments for the new developers in the future to see what the package does etc.

I hope this is useful.

Thanks
Sutha

IDENTITY_INSERT

2006-01-06T01:20:00.000-08:00

If you have a Dimension table for example with Identity column. You want to insert a default record with the Surrogate Key of -1 with description "Unknown".

Table with the following structure:
CREATE TABLE Dim_Manager
(
ManagerId INT IDENTITY (1,1) NOT NULL,
Name VARCHAR(50) NOT NULL
)

If you create the table with the above statement and would like to insert default dimension record. You can do that by doing the following:

SET IDENTITY_INSERT dbo.Dim_Manager ON
INSERT INTO Dim_Manager (ManagerId, Name)
VALUES('-1','Unknown')
SET IDENTITY_INSERT dbo.Dim_Manager OFF

You need to set the IDENTITY_INSERT to ON before inserting into Identity colum. Once you have inserted the record you need to set IDENTITY_INSERT to OFF as shown above.

Thanks
Sutha

Package Configurations - DTSConfig File

2005-12-31T09:32:00.000-08:00

SSIS supports 5 different types of sources to use the setting the properties of objects and they are :

XML Configuration File
Environment Variable
Registry Entry
Parent Package Variable
SQL Server

I have used XML configuration file to do all the SQL Server connection settings. In the future if you wish to include more connections / settings you can add to this configuration file. If you continue to use this method, you can execute SSIS Packages in different environments without making any changes to the packages. Environments.dtsconfig is the only file you would have to change between environments.

Configuration file is located at C:\Config\Enironments.dtsconfig

This configuration file should exist at the same place in all environments. During runtime SSIS package will pickup the setting from that location and update the connection settings according to the XML configuration file.

XML configuration file will look like the one in the link below:
http://www.smart-bi-solutions.com/BlogStuff/31122005_01.jpg

When you migrate the SSIS packages from one environment (Dev) to the other (UAT), you would need to do the following to the configuration file :

Copy “Enironments.dtsconfig” from your dev environment to UAT environment. Please make sure that you place this configuration file in the same location as development.
Edit “Enironments.dtsconfig” using WordPad or something similar.
Rename Data Source values. For example if your development environment is called “Beast\SQL2005” and UAT environment is called “Sql2005\Uat”. You need to search and replace the value for data source.
Save the file.

Thanks
Sutha

MSDTC - What a pain

2005-12-31T07:12:00.000-08:00

Yes I admit that I am not an expert in MSDTC, but still so difficult to figure this damn thing. If Windows Firewall is ON it wont work. I didnt have that turned on, but I have had Norton on my machine, which would stop MSDTC working as well. If you get this kind of message please try to disable your firewall and try before anythign else. You will get the message below even though you have configured MSDTC correctly and started successfully.

------------------------------
Error: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.
------------------------------

Useful posts when you try to resolve MSDTC issue :
http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2339.aspx
http://blogs.conchango.com/paulmcmillan/archive/2005/10/17/2277.aspx
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q293799
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q306843

It is advisable to run MSDTC with DTC Logon Account such as "NT AUTHORITY\NETWORK SERVICE". Also select "Allow Inbound" and "Allow Outbound" under Transaction Manager Communication.

Thanks
Sutha

Creating Random Data From Given Dimensions

2005-12-30T09:32:00.000-08:00

I had to create millions of rows dummy fact table for testing purpose. I have got valid dimensions, but not fact. I had a requirement to create facts using random products (roughly 160 products across whole day). The fact table stored data to the minute. Each shop in average have 2000 transactions / day.

Each day shop in average sells 160 products. This is populated into a Tmp table as shown below:

SELECT TOP 160 productid
INTO TmpProduct
FROM dim_product
ORDER BY NEWID()

I have written couple of SQL statements to achieve this. They produced right result but unfortunately they are slow for my requirement. Therefore we have to do in C#.

Option 1
---------
SELECT TOP 2000 RANK() OVER (order by newid()) as RankRec,
dateid,
timeid,
shopid,
managerid,
TmpProduct.productid,
1 as Qty,
(timeid*rand()) as Amount
FROM dim_date
CROSS JOIN dim_shop
CROSS JOIN dim_time
CROSS JOIN dim_manager
CROSS JOIN dim_product
INNER JOIN TmpProduct
ON dim_product.productid = TmpProduct.productid
WHERE shopid = 39
AND dateid = 1
AND dim_shop.shopno = dim_manager.shopno

Option 2
---------

SELECT TOP 2000
dateid,
timeid,
shopid,
managerid,
TmpProduct.productid,
1 as Qty,
(timeid*rand()) as Amount
FROM dim_date
CROSS JOIN dim_shop
CROSS JOIN dim_time
CROSS JOIN dim_manager
CROSS JOIN dim_product
INNER JOIN TmpProduct
ON dim_product.productid = TmpProduct.productid
WHERE shopid = 39
AND dateid = 1
AND dim_shop.shopno = dim_manager.shopno
order by newid()

I am posting this for me as it will come handy during future projects.

Thanks
Sutha

Random data

2005-12-28T02:04:00.000-08:00

I had a request from a client to use random products (for example 100 products per customer) when I create a dummy set of data for testing purpose. I didn't realise that there is a simple way to get random set of products being returned at each time, until I Googled it.

SELECT TOP 100 ProductId
FROM Dim_Products
ORDER BY NEWID()

NEWID() returns a UniqueIdentifier every time it runs and therefore it will return different product list each time.

Very useful when you create random data.

I am sure most people knew this, but I learnt this yesterday. So I thought I would post it as it would be useful for me in the future.

Thanks
Sutha

Auditing

2005-12-27T07:50:00.000-08:00

As part of best practice I have kept all the auditing details in a separate database. I have given database name as Audit. There are couple of tables, one for auditing of SSIS package and the other for stored procedure auditing.

SSIS Audit table structure

CREATE TABLE dbo.SSIS_Audit
(
LoadId int NOT NULL,
PackageName varchar(100) NOT NULL,
TaskName varchar(100) NOT NULL,
[Type] varchar(100) NOT NULL,
MachineName varchar(50) NOT NULL,
UserName varchar(50) NOT NULL,
NoOfRowsRead int NOT NULL CONSTRAINT DF_MBA_Audit_NoOfRowsProcessed DEFAULT ((0)),
NoOfRowsInserted int NOT NULL CONSTRAINT DF_MAB_Audit_NoOfRowsInserted DEFAULT ((0)),
NoOfRowsUpdated int NOT NULL CONSTRAINT DF_MAB_Audit_NoOfRowsUpdated DEFAULT ((0)),
StartTime datetime NOT NULL,
PackageDuration int NULL
)
GO

Stored Procedure Audit table structure

CREATE TABLE dbo.Error_Audit
(
ErrorNumber numeric(18, 0) NOT NULL,
ErrorSeverity numeric(18, 0) NOT NULL,
ErrorState numeric(18, 0) NOT NULL,
ErrorProcedure varchar(60) NOT NULL,
ErrorLine numeric(18, 0) NOT NULL,
ErrorMessage varchar(250) NOT NULL,
ErrorDate datetime NOT NULL
)
GO

Within SSIS package I used SQL Expression to achieve auditing. I have used Event Handler wherever possible (On Pre-Execute / On Post-Execute) and sometimes I have used Execute SQL Task to achieve this. I couldn’t use event handler on package itself as it is triggering twice. The reason it triggers twice is “it will fire the event handler scoped to the task AND the event handler scoped to the package. So your event actually gets handled twice”. Therefore I use Execute SQL Task to log audit details at the beginning and end of the package to log auditing details.

Example expression to populate SSIS audit table

"INSERT INTO SSIS_Audit (LoadId,PackageName, TaskName, Type, MachineName, UserName, NoOfRowsRead, NoOfRowsInserted, PackageDuration, StartTime)
VALUES ('"+(DT_STR,50,1252)@[User::LocalLoadId] +"',
'"+ @[System::PackageName] + "', '" + @[System::TaskName] + "',
'Package Completed', '" + @[System::MachineName] + "',
'" + @[System::UserName] + "', '"+(DT_STR,50,1252)@[User::RowsRead] +"','"+(DT_STR,50,1252)@[User::RowsInserted] +"',"+(DT_STR,4,1252)DATEDIFF("ss",@[System::StartTime],getdate()) + ", getdate() )"

When the above expression is evaluated it will display something similar to this as SQLStatement

INSERT INTO SSIS_Audit (LoadId,PackageName, TaskName, Type, MachineName, UserName, NoOfRowsRead, NoOfRowsInserted, PackageDuration, StartTime)
VALUES
( '0','Pkg_Fct_Summary_Initial_Load', 'End SSIS Pkg Audit Log',
'Package Completed', 'BEAST', 'BEAST\Sutha', '0', '0',14, getdate()
)

Within stored procedure I have used the following stored procedure to call if anything fails. This Stored procedure will log it into a table which I have specified above (Error_Audit)

CREATE PROCEDURE dbo.prc_LogErrorInfo
AS
BEGIN TRY
INSERT INTO Error_Audit
VALUES
(
ERROR_NUMBER(),
ERROR_SEVERITY() ,
ERROR_STATE() ,
ERROR_PROCEDURE() ,
ERROR_LINE() ,
ERROR_MESSAGE(),
GETDATE()
)
END TRY

BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage,
GETDATE() AS ErrorDate

END CATCH

As usual comments welcome.

Thanks
Sutha

LoadId

2005-12-27T07:13:00.000-08:00

I thought I would explain the load id mechanism I have been using in my current project. I have stored couple of tables within "Stage" database. Decision was made to put it in the Stage database, against Audit database as these are not auditing tables.

The structure of the tables as follows:

CREATE TABLE dbo.Ctl_Load_Summary
(
LoadId int NOT NULL,
StartDate datetime NOT NULL,
EndDate datetime NULL,
Duration int NOT NULL CONSTRAINT DF_Ctl_Load_Summary_Duration DEFAULT ((0))
)
GO

CREATE TABLE dbo.Ctl_Load_Batch_State
(
LoadId int NOT NULL,
InProgress varchar(1) NOT NULL
)
GO

"Ctl_Load_Batch_State" table always hold single record, which is controlled by a stored procedure (Code is below). This table will hold last successful load id OR current load id which is in progress. If it is in progress it will have the current load id and ‘Y’ as Progress flag. If the load is successful it will have the load id and ‘N’ as progress flag. During any unforeseen problems if the SSIS package fails during data warehouse load progress flag will remain as ‘Y’. When we rerun the package it will pick the same load id as the transaction within the package would have rolled the data back.

“Ctl_Load_Summary” table holds the summary details of the load. For example when we load data everyday it will have the loaded we have used for the day along with when the package started and ended. The duration of the package is calculated in seconds and stored in the duration column. This is mainly done to meet the client’s requirement.

Commets welcome.

Thanks
Sutha

Stored Procedure To Retrieve Load ID

-- =============================================
-- Author : Sutha Thiru
-- Create date : 12/12/2005
-- Description : This SP will return the latest .
-- =============================================
ALTER PROCEDURE [dbo].[prc_get_loadid] (@loadid INT OUTPUT)
AS

SET NOCOUNT ON

DECLARE @inprogress VARCHAR(1)
DECLARE @statecount INT
DECLARE @summarycount INT

SET @loadid = 0
SET @summarycount = 0
SET @statecount = 0

BEGIN TRY

BEGIN TRAN
SELECT @summarycount = count(*)
FROM Ctl_Load_Summary

SELECT @statecount = count(*)
FROM Ctl_Load_Batch_State

IF @summarycount <> 0 and @statecount = 0
BEGIN
RETURN 0
END
ELSE
SELECT @loadid = loadid,
@inprogress = inprogress
FROM Ctl_Load_Batch_State

IF @inprogress = 'Y'
BEGIN
set @loadid = @loadid
END
ELSE
SET @loadid = @loadid+1
IF @statecount = 0
BEGIN
INSERT
INTO Ctl_Load_Batch_State
VALUES (@loadid,'Y')
IF @@ERROR = 0
BEGIN
set @loadid = @loadid
END
END
ELSE
BEGIN
UPDATE Ctl_Load_Batch_State
SET loadid = @loadid,
inprogress ='Y'
IF @@ERROR = 0
BEGIN
set @loadid = @loadid
END
END
COMMIT TRAN

RETURN @loadid
END TRY

BEGIN CATCH
ROLLBACK TRAN
--EXEC Audit.dbo.prc_LogErrorInfo -- This calls another SP for auditing
END CATCH

Execute SQL Task - Retrieving LoadId & Assigning To User Variable

2005-12-13T12:58:00.000-08:00

During daily load of data warehouse I wanted to get the LoadID from a table for that load and assign it to a user variable. I wanted to execute a stored procedure which will return an output value which will be the LoadID. I wanted to assign it to a variable.

The SQLStatement would be “EXEC prc_get_loadid ? OUTPUT”. You can see from the link below:
http://www.smart-bi-solutions.com/BlogStuff/13122005_01.jpg

From the link below below you can see that I am assigning the output value from stored procedure to user defined variable. Please note that the variables are case sensitive within SSIS.
http://www.smart-bi-solutions.com/BlogStuff/13122005_02.jpg

Now I assigned the output (LoadId) from a stored procedure to user variable, I can use that variable anywhere within the calling SSIS (parent / child) package which handles the daily load.

Thanks
Sutha

Auditing Data Flow Task

2005-12-09T03:35:00.000-08:00

I would like to audit number of rows read from source, inserted, updated etc. You can use out of the box Row Count component to get the number of rows processed at a given point and time and assign it to a variable as Jamie explained well in his post.

I was talking to Mark and said it would be nice if we could have something to do this using an Event Handler, which means we don’t have to have multiple variables in a package etc. We discussed it and the wiz-kid wrong a component which lets me do what I wanted to achieve. Using this component I can get the number of rows processed into a single variable at a given time and fire an event handler to insert it into an audit table. You can see what Mark has done here.

I will do a blog soon, with simple demo explaining all the details.

Thanks
Sutha

SQL Server 2005

2005-12-09T03:22:00.000-08:00

While I was working for Reuters I had the opportunity to have access to SQL Server 2005 as Reuters was one of the Technology Adoption Programme (TAP) partners. I have been working with SQL Server 2005 over 2 years now.

I have concentrated mainly on SQL Server Integration Services (SSIS), but have spend time in understanding new features with SQL Server database as well as SQL Server Analysis Service (SSAS).

Thanks
Sutha

Lets Give it a go....

2005-12-09T03:12:00.000-08:00

I have been working with SQL Server for nearly 8 years now. I have concentrated on BI front all these years. I never bothered to setup my own blog.

After all these years couple of my collegues (Steve & Mark) nagging me for a long time, I have decided to go ahead with my own blog today.

I am setting this as a knowledge base for me more than anything else. If I get to share some valuable information during that process it would be bonus. There are some great blogs out there as you all aware.

I am ready to rock..... Lets start blogging....

Thanks
Sutha