Datastage Server vs SSIS

[wahi80]

Hi,

Currently we are planning to move 800 plus server jobs from Datastage Server to SSIS. We are trying to see if there is any functionality which is currently present in Datastage Server which would be difficult to replicate in SSIS. SQL Server is our primary DB, with a few connections to Sybase DB. We also have jobs which read CFF delivered by Mainframe and call Web services.

So the question I have is is there anything particular that stands out in SSIS, which would prevent me from not migrating from Datastage?

Regards
Wah

P.S I did read some blogs and other threads on dsxchange but most of them were old

[asorrell]

I think you've already identified your main issue - connectability and support for Complex Flat Files and other "non SQL Server" data sources. Overall, both products have similar features, though the mechanism and syntax varies a lot.

There are literally hundreds of features in both products, so no-one can guarantee what issues you'll encounter. I'd suggest taking a few of the most complex Server jobs and then converting them to get a hands-on feel for the requirements and issues.

The reason you didn't find much here is that usually we are going the other way - moving SSIS jobs to Parallel DataStage jobs to resolve performance issues. You might also want to check SSIS forums to see if they have any posts on the problem.

[SURA]

SSIS will be fast if your source and target is SQL Server Or Source is file. I haven't tried other source with SSIS. So i am not able to comment on the rest!

[vmcburney]

I think your main problem here is that DataStage is the leading product in the market and SSIS is very limited. The limitations of SSIS may force you into complex stored procedure coding as it doesn't have the same breadth of functionality. The lack of metadata support makes it hard to maintain or re-engineer. The platform and database limitations means you still have to keep other ETL tools around anyway.

I don't think I have ever seen a good business case for rewriting ETL - it is a waste of money and you can end up with worse ETL within a year or two. The consumers of information get nothing knew from the exercise, you are just changing the plumbing. I think you are better off re-engineering the reporting platform and use modern ideas such as real time warehousing, big data or data vault and throw the old Warehouse on the trash pile. This lets you get value for money in your ETL rebuild.

Unless you have experts in the new ETL tool then what you will build is going to be dodgy.

The Gartner Magic Quadrant for Data Integration released last week says this about Microsoft SSIS:

Implementations reflect increasing departmental-level deployments that orientate toward less-sophisticated usage. SQL Server SSIS is an embedded functionality in SQL Server Fragmentation of advanced skill base: with a wide availability of highly varied implementation skills in the market, it is difficult to identify Microsoft best practitioners Reference customers report overall satisfaction with the tools; however, there are specific issues with integrating SSIS with wider data-management-related technologies and some connectivity issues with more traditional sources (challenges are cited for AS/400, z/OS and VMS environments). Customers reported a lack of metadata support as a weakness that affects metadata discovery, lineage and dependency reporting capabilities. References also expressed that guidelines and approaches for integrating SSIS with SOA services are difficult to find, much less follow (noting orchestration and job flow issues).

[vmcburney]

But to answer your original question, though it sticks in my craw, if I were to replace DataStage Server jobs with SSIS jobs I would have the following problems:
- All transformation code needs to be rewritten, stage variables, derivations and functions.
- All basic transformation routines need to be rewritten, do a count of the number of routines you have and the lines of code and add that to your estimate.
- Any data your persist in hash files will be lost, you may need to move it into SQL Server lookup tables.
- All your job control needs to be rewritten, sequence jobs, batch jobs, batch routines etc. Unless you are using an enterprise scheduler.

As a ballpark estimate I would say it will cost somewhere between 2 to 5 million to replace 800+ jobs, taking into account you have to go through design, development, testing, UAT, regression testing and production deployment.

[chulett]

Currently we are planning to move 800 plus server jobs from Datastage Server to SSIS.

Out of curiosity, what is driving this?

[SURA]

In one of my project they upgraded the Datastage version 8x - 8x. I asked why do we need to upgrade?

I got a surprising reply!

CDC is not working in the old version! I said that is because of 1,2,3 issues and upgrade to different version is not the solution!

They eventually migrated to higher version. Someway I am happy because I got a chance to work in that version.

Anyhow end result is, wrong guys in the right seat could end up by spending good number of $$$$ to the client.

Sorry guys, your case should be 100% valid and I am not in the position to comment on this! I thought this is an opportunity to share how the DW projects are being handled in some places!

Anyhow good luck with your proceedings!

[wahi80]

Hi,

Thanks for the input, it really helps in decision process.
One of the main drivers of the change is that we are mainly an SQL Server shop. We have lot of DBAs comfortable with SSIS, and maintenance costs for SSIS will be much lower than Datastage

[asorrell]

If it costs a over a million dollars (assuming you have several developers on staff and it takes 2 - 3 years to do the swap), how many years of DataStage maintenance will that buy?

[wahi80]

Hi,

Does anyone have a ballpark figure for DataStage Server licensing cost?
I'm assuming it should be very low.

Just need to do cost comparison analysis vs SSIS.

It is tougher getting it from my software procurement team

Regards
Wah

[SURA]

As far as i know the cost is based on the negotiation talent with IBM sales.

I bought a HONDA CRV for $44G whereas my friend bought the same car from the same showroom for $42G.

[vmcburney]

If you have already bought DataStage Server Edition then your only cost is ongoing maintenance, which is usually around 20% of your original purchase cost (though it can increase based on discount lapsing). You can also choose to let your maintenance lapse, for exmple if you have decided to stick with Server Edition 7.x which is falling out of support anyway, but this causes problems as systems get upgraded.

If you want to upgrade and go with version 8.x then you need to keep your maintenance payments going. Version 8 will give you extra metadata functions and the option to buy other products such as Business Glossary and Workbench. It will give you better connectivity and keep you up to date on the latest database versions.

[Fawlty]

Looking through the posts here, Vincent seems to have the most relevant responses, take heed.

We (like yourself) are a SQL Server shop and moved to SSIS (from DS Server) about 3-4 years ago for most of the same reasons that you mention in your original post. So here's a bit of free advice.

We either made the decision to upgrade to the Enterprise Edition or utilise the 'free' ETL tool that was SSIS. The upgrade from DS Server to DS Enterprise jobs requires a re-write anyway (as far as I was told - happy to stand corrected) so either way, there would be some work to be done. And yes, I understand that NOT all Server jobs need to be converted to Enterprise style jobs.

So how did we we go? Well after all this time we are NOT done converting, though we haven't placed a real high emphasis on it either. Not sure what your business is, bit ours is pretty dynamic and as we have left it so long, many of our ETL Tasks are either no longer required or changed so much, it was worth the re-write in SSIS. Of course, all new work was performed using SSIS. So overall, hasn't been too costly, certainly no outside help required.

So, in a nutshell you WON'T have over 800 server jobs to convert.

Our policy was to convert over the jobs that most required a conversion, this was either measured by performance gains or specification changes. Again, those jobs/processes that give you the most pain are the ones to convert fisrt.

The first thing you should look into is getting a framework ready (check out BI Monkey ETL Framework) that gives you all the logging, restartability, dependencies, notifcations etc you require (just like job Sequence's) and then write a series of template jobs to cover your most used tasks (DB to DB, File to DB, FTP etc etc). Make sure these are all solid before you start.

As far as re-writing DS to SSIS, there is NO easy conversion, it's all a pain. You do get to find out how much you DIDN'T know about T-SQL however. Highly recommend looking into task libraries such as Pragmatic Works or Cozyroc.

The other thing you need to take into considersation is the testing overhead and the time taken from the business to perform this task.

Also, make sure you have a HOT swappable DS Server environment ready to go that is made up of a supported hardware/software combination for whatever DS Server version you are using (especially if you have run out of maintenance like us). Just in case of the inevitable server failure.

Overall, I'd say DataStage is a MUCH better development environment but SSIS has provided much better performance (for us anyway Vs DS Server jobs).

If you can convince the business to stick with DS and front the cost, I'd be leaning that way. The jobs you already have will always work and the new ones you develop will be faster. Not much re-tooling required.

Yep, there's a cost involved, but like a previous poster suggested, time to hone your negotiation skills and getting know what drives your sales rep (try EOFY and EOQ for starters).

Cheers

Hope that helps

Basil