We are heavy MS shop(using only SQL servers) and SSIS is our primary ETL/reporting tool, which integrates very wells.
Can folks that are using Data Stage in their production environment, share why/when would you prefer DataStage over Microsoft's SSIS as the primary ETL tool? I want to know the pros and cons of either of the tools.
DataStage vs SSIS
Moderators: chulett, rschirm, roy
If you use only SQL Server then SSIS is fine. Most of those shops are very good with the drawbacks to SQL Server SQL and stored procedures. It can do some pretty powerful things. Almost any database's SQL or stored procedures can perform as well or better when only one database is all you ever deal with. The trade off is now you are using database resources. If your database is heavily taxed then you add more to it. You can off load some resources if work is done within DataStage. If they take their SQL and throw it in user defined SQL within DataStage then you are off loading less resources than possible. It is all based on the comfort level of the developers. If they are taught poorly then that is how they develop within DataStage.
If you have several databases like a mix of SQL Server and Oracle then DataStage becomes more valuable. It allows you to become more database independent. Maybe Oracle is better for SAP application. If you have a departmental datamart then SQL Server is a better solution.
If you have several databases like a mix of SQL Server and Oracle then DataStage becomes more valuable. It allows you to become more database independent. Maybe Oracle is better for SAP application. If you have a departmental datamart then SQL Server is a better solution.
Mamu Kim
I worked at one sie where we had 80 SQL server boxes. We used SSIS for data integration between them and DataStage for the enterprise data warehouse. It worked very well. SSIS for quick and dirty solutions and DataStage for the heavy lifting.
I worked at another place where everything was Oracle. In the past the EDW was done in Oracle stored procedures. DataStage replaced all the stored procedures very quickly. It gave them a lot of flexibilty. IBM suite of products gets better every year. I am certain the metadata tools will be the best. When that day happens then I am sure we are all going to be glad we tied our futures to IBM.
In order for data to become information then the metadata tools have got to part of everything we do in IT. Data only becomes information when it becomes useful to make business decisions. That only happens when we have better understanding of data. So business glossary and similar tools are critical to making this happen. Most companies are a long way from being that organized.
I worked at another place where everything was Oracle. In the past the EDW was done in Oracle stored procedures. DataStage replaced all the stored procedures very quickly. It gave them a lot of flexibilty. IBM suite of products gets better every year. I am certain the metadata tools will be the best. When that day happens then I am sure we are all going to be glad we tied our futures to IBM.
In order for data to become information then the metadata tools have got to part of everything we do in IT. Data only becomes information when it becomes useful to make business decisions. That only happens when we have better understanding of data. So business glossary and similar tools are critical to making this happen. Most companies are a long way from being that organized.
Mamu Kim