DSXchange

I had a look at SQL Server 2005 and the new Information Services that replace DTS. DTS was never a strong competitor to DataStage in large data integration projects, but SSIS is a lot more like DataStage and could be a big threat. At the risk of getting contradicted from all sides I've compared them in my latest blog IBM versus Microsoft data integration slugfest.

If you haven't had a chance to use SSIS yet there is a short description of the new functionality.

DTS and analysis services has undergone a complete transformation in SQL Server 2005. They have added Business Intelligence development studio for DTS and analysis services (as in Beta version). SQL Server 2005 runs in .NET CLR which itself is a huge change over SQL Server 2000.

What has .NET has got to do with Datastage exactly? Whenever datastage is installed .NET framework is installed. This is a common point between SQL Server 2005 and datastage because SQL Server 2005 needs CLR 2.0 to be installed while datastage needs CLR 1.1.

DataStage client software is (since 7.0) written using .NET Framework 1.1 (therefore the .NET framework must be installed in order for it to be run).

Hi

Thanks Ray.

it seems that SSIS have included Slowly changing dimension transformation as well.

--Balaji S.R

That was a clever move by Microsoft. We wont see a SCD stage in DataStage until the Hawk release.

Nice article Vince. I hope You're right about the lower prices of DataStage because this will make the software more popular and will give us more work oportiunities

I like to chime in on this since I believe that the cost savings is not only the ETL tools SSIS vs Datastage. If you have SSIS you now have an enterprise level database that supports partitioned tables as well as mirroring (SQL SERVER). You can save the expense on Oracle and it's licenses which are more costly then SQL Server. You can now run your SQL Server Database on a Windows server instead of Oracle running on Unix. There is also additional saving there. Additionally I believe that parallel processes are supported in SSIS as well as multi-threading.


Under these circumstances is there a reason to start a new data integration project using Datastage instead of SSIS?
By the way an enterprise reporting system is included for free which has all the robustness of Crystal Enterprise with the security buit-in.


The following is a case study on project real which you should find intresting.

http://www.microsoft.com/downloads/deta ... laylang=en

Another Olap Solution
http://www.microsoft.com/technet/itsolu ... rrtcs.mspx

I forgot to mention you can actually use a debugger.


Larry

A good evaluation Larry, SQL Server 2005 is a strong competitor to WebSphere DataStage and Oracle and Hyperian and Business Objects.

I'm sure both Informatica and Ascential saw the writing on the wall with the SSIS development and aimed at richer ETL rather then cheaper ETL. The Hawk release has integrated QualityStage and MetaStage, they have already merged in the Orchestrate parallel engine to make it handle volumes that SSIS cannot handle and they have a big head start on mainframe, RDBMS and ERP connectivity (though Microsoft may catch up there). Both Ascential and Informatica spent a huge portion of the budget on connectivity and you cannot underestimate the value of all the data types they support.

Is SQL Server 2005 Enterprise on Windows cheaper then Oracle on Linux or Oracle on Windows? Oracle of course refute this with their own studies:
http://www.oracle.com/database/docs/edi ... s20051.pdf

SSIS will be more popular then any other ETL tool because it is free with SQL Server but for larger companies that require native RDBMS connectivity or data quality functionality or metadata management or scalability or mainframe data sources or ERP packs it is not yet ready to compete.

Hi guys,

I have read the article from Vincent on SSIS and DS comparison and thank you Vincent for such a informative article on it.

Currently we are planning to use SQL server as a DB and SSIS as ETL tool for one of our requirements. We will be getting around 10 M (million) rows every month and have to be processed and loaded in the Database. Then from this database it goes to another SQL server in different cycles.
One record from first DB may be inserted in 2nd DB more than once times as there are multiple cycles.
Once the data is loaded in 2nd SQL server, some calculations will be done on it and only the results will be taken back. The result will not be more than 1M.

So the database has to store around 10M rows * 12 months * 5 years = 600M records.

Can the first SQL DB take this much load and can SSIS give us the performance to carry out the 10M * process at least 3 times in a month = 30M row processing?

I tried searching this forum as well as internet, but could not get anything as SSIS is very new.

Surely you should be asking Microsoft, particularly if the answer you require is "yes".

10 Million records a month. You could use DataStage if you wanted to process this in under an hour but SSIS should be able to churn through this in a month. You should find that SSIS can load it and SQL Server can store it but the challenge is how much of that data you need to make available to user reporting and how big and frequent your SQL queries get.

On these smaller data warehouses it's not so much SSIS versus DataStage/Informatica but SQL Server + SSIS versus IBM Balanced Warehouse C Class (or Netezza or HP Neoview or Oracle). That's the product targeted at smaller companies and it comes with linux and a DB2 SQL builder and data load tool rather than an ETL tool.

Thanks for your input guys!!!