SSIS as a ETL tool?
-
SSIS as a ETL tool
Guys,
I currently doing microsoft's hands-on training tutorial for SSIS. I realized that its not an comprehensive ETL tool as it was advertised to me. I see it more of a package tool for all imports, exports which include DML scripts.
In my scenario I have to convert 10 source data tables to destination schema tables. i normally use DML scripts to convert the data. Once I have mapping between source and destination tables it does not take more than hour to write DMl scripts. But when I try to use SSIS its taking me more time to create data flow tasks and create a package. And does SSIS support all the sql server 2005 string functions.
Any comments/inputs would help.
Thanks
-
SSIS isn't a comprehensive ETL tool? You've got to be kidding right? I'm curious, what is it missing that means it isn't an ETL tool? SSIS is far far superior than any ETL offering Microsoft have had in the past, and superior to many other top-of-the-market ETL tools as well.
Yes, SSIS supports all T-SQL string manipulation functions simply because you can issue any T-SQL statement from within SSIS (i.e. using the Execute SQL Task). If you want to do the same manipulation within the dataflow (though it doesn't sound as if you do) then the SSIS expression language is more than capable.
If you want to write DML script to manipulate your data - go for it. Microsoft certainly aren't saying "You should always use the SSIS dataflow to manipulate your data". In many many scenarios T-SQL code will be the way to go.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
am, if you have not come across Jamie before, he is a world expert on SSIS so his advice is good.
Although some ETL tools are more sophisticated or performant than SSIS (e.g. Ab Initio), you pay a lot for this. SSIS is definitely able to cope with most ETL problems that can be thrown at it and has been used for some very large and complex systems. Once you have identified the requirements for your ETL processing, you will have a good base to decide which tool offers the best price / performance / functionality for meeting your SLAs.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Thanks Jamie I will continue to explore SSIS using specific scenarios.
Thanks again for your valuable inputs
-
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply