ETL Replacement Suggestions Needed
2 posters
Page 1 of 1
ETL Replacement Suggestions Needed
Greetings,
I’ve just begun the process of researching a fitting ETL tool that will allow for a simplistic user friendly source extraction to staging configuration for minimal licensing costs. We are in the works of decommissioning a very old version of DataMirror (4.3) that pumps data from our i5 system into a SQL Server 2000 data warehouse. The reason for the decommission is that we no longer hold licensing for the DataMirror product, thus we are not supported on it. Additionally, management does not want to spend the exuberant cost that is required for the licensing.
We have a small 80GB data warehouse that comprises of stage, fact, and dimensional tables as well as some historical tables. We hold 2 full years plus current years’ data. The plan is to upgrade from SQL Server 2000 to 2005 in conjunction with the new ETL.
Our current ETL is installed on both our i5 (Host) and SQL server (Client). DataMirrror does Transformation Server Mapping and on the i5 contains the Source Administration piece that consists of three steps: Defining a target system (SQL Server), selecting the source tables (i5 tables) that you want to replicate to the target system, and describing these tables to the target system by means of a secured connection made between the Host and Client. It also allows one to select/exclude specific rows and columns in a table and define derived columns to support joins. DataMirror on the SQL server end (Client end) registers and connects to an ODBC data source (SQL Server data warehouse ), then allows one to ‘Assign’ the ‘Target’ tables (SQL Server tables) to the ‘Source’ tables (i5 tables), that were previously described from the i5 to the SQL Server. This will complete the connection and assignment process needed for the refreshment of data. We refresh our data once per week.
My plan was to start looking into Microsoft’s SQL Server Integration Services as a replacement of DataMirror. I have very little experience using the product and have only created a few basic packages in the past. This will be my first endeavor in the implementation of a full blown ETL project. I’m hardly qualified, but the job is up to me to figure out as we cannot hire contractors at this time. I’m doing my best in trying to convince the boss to send me to the ‘ETL Architecture in Depth’ seminar that’s upcoming in May, however I’m hoping to have a solid plan in place before then, or at least know what product I will be utilizing for the ETL.
Long story short, I would appreciate any feedback or thoughts regarding what might be a good fit as an ETL replacement. After reading my description of how we are currently handling our ETL, does a more suiting ETL utility come to mind that I might research? We currently hold licensing for MS SSIS which was my original ETL choice to start looking at, especially since money was an issue.
Again, any suggestions would be greatly appreciated!
Regards,
Traci Marcero
I’ve just begun the process of researching a fitting ETL tool that will allow for a simplistic user friendly source extraction to staging configuration for minimal licensing costs. We are in the works of decommissioning a very old version of DataMirror (4.3) that pumps data from our i5 system into a SQL Server 2000 data warehouse. The reason for the decommission is that we no longer hold licensing for the DataMirror product, thus we are not supported on it. Additionally, management does not want to spend the exuberant cost that is required for the licensing.
We have a small 80GB data warehouse that comprises of stage, fact, and dimensional tables as well as some historical tables. We hold 2 full years plus current years’ data. The plan is to upgrade from SQL Server 2000 to 2005 in conjunction with the new ETL.
Our current ETL is installed on both our i5 (Host) and SQL server (Client). DataMirrror does Transformation Server Mapping and on the i5 contains the Source Administration piece that consists of three steps: Defining a target system (SQL Server), selecting the source tables (i5 tables) that you want to replicate to the target system, and describing these tables to the target system by means of a secured connection made between the Host and Client. It also allows one to select/exclude specific rows and columns in a table and define derived columns to support joins. DataMirror on the SQL server end (Client end) registers and connects to an ODBC data source (SQL Server data warehouse ), then allows one to ‘Assign’ the ‘Target’ tables (SQL Server tables) to the ‘Source’ tables (i5 tables), that were previously described from the i5 to the SQL Server. This will complete the connection and assignment process needed for the refreshment of data. We refresh our data once per week.
My plan was to start looking into Microsoft’s SQL Server Integration Services as a replacement of DataMirror. I have very little experience using the product and have only created a few basic packages in the past. This will be my first endeavor in the implementation of a full blown ETL project. I’m hardly qualified, but the job is up to me to figure out as we cannot hire contractors at this time. I’m doing my best in trying to convince the boss to send me to the ‘ETL Architecture in Depth’ seminar that’s upcoming in May, however I’m hoping to have a solid plan in place before then, or at least know what product I will be utilizing for the ETL.
Long story short, I would appreciate any feedback or thoughts regarding what might be a good fit as an ETL replacement. After reading my description of how we are currently handling our ETL, does a more suiting ETL utility come to mind that I might research? We currently hold licensing for MS SSIS which was my original ETL choice to start looking at, especially since money was an issue.
Again, any suggestions would be greatly appreciated!
Regards,
Traci Marcero
Traci Marcero- Posts : 2
Join date : 2010-04-13
Age : 45
Location : Michigan
Re: ETL Replacement Suggestions Needed
SSIS is not a bad tool and it's free, so the price is right.
There are open source options, such as Kettle, but since you are a SQL Server shop, there really isn't any particular reason to not use SSIS. Just make sure you are on the most current version... it is much improved over the pre-2005 stuff.
There are open source options, such as Kettle, but since you are a SQL Server shop, there really isn't any particular reason to not use SSIS. Just make sure you are on the most current version... it is much improved over the pre-2005 stuff.
Re: ETL Replacement Suggestions Needed
Ngalemmo,
Thank you for taking the time to analyze my choices. I will strongly consider SSIS and I'll be certain to upgrade to the latest and greatest when doing so.
I look forward to using you as a knowledgeable resource as I will certainly need it in my upcoming project.
Thanks Again,
Traci M.
Thank you for taking the time to analyze my choices. I will strongly consider SSIS and I'll be certain to upgrade to the latest and greatest when doing so.
I look forward to using you as a knowledgeable resource as I will certainly need it in my upcoming project.
Thanks Again,
Traci M.
Traci Marcero- Posts : 2
Join date : 2010-04-13
Age : 45
Location : Michigan
Similar topics
» Looking for suggestions on ETL tools for DW/BI Project
» Suggestions for cleaning data
» How to convert required OLTP schema into dimensional model
» Modeling suggestions
» Suggestions for BI tools
» Suggestions for cleaning data
» How to convert required OLTP schema into dimensional model
» Modeling suggestions
» Suggestions for BI tools
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|