May 21, 2009 at 8:22 am
I am not very familiar with SSIS and I am debating if I should use it to import data originates in Oracle.
We have a corporate database that runs or Oracle, for which we only have limited permissions (i.e. read only, no stored procedures or anything of that sort). So I have a bunch of programs that run on workstations that import various data from Oracle to our SQL Server, at different times during the day.
Some programs run every 15 minutes, others a few times a day. Presently I'm using SAS to manipulate and import the data. I want to switch to a more mainstream solution and I'm debating whether to use SSIS or C#.
The only worry I have with SSIS is that with all the manipulating and importing of data, for instance every 15 minutes, I would slow down the SQL Server. As opposed to C#, which would run on a different machine, manipulate the data locally and only access SQL Server in the final step, i.e. to import the data.
Are my assumptions right ?
Thx in advance.
May 21, 2009 at 9:57 am
I would recommend SSIS as it has all the tools you need to develop the transfer(s) quickly. You are right to be somewhat concerned with performance as SSIS will consume resources. One option, which of course costs money, is to have a separate SSIS Server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 24, 2009 at 6:47 am
SSIS will consume resources, but so would a application written in C#. The beauty of SSIS has Jack has already stated that it comes with everything you need to get data from one system to another quickly already in the box. It has been designed for this vary purpose. Providing the server you are running it on has adequate resources such as plenty of RAM.
To give you an idea how fast SSIS is, on our old Datwarehouse running SQL Server 2000 it would take approx 4 hours to load a days sales. On the new Datawarehouse running SQL Server 2005 and SSIS I can load the same volume of data in about 20 minutes.
May 24, 2009 at 4:21 pm
Something to keep in mind as well is that SQL2008 and the corresponding release of SSIS now has the ability to use C# as script component. So you don't necessarily have to pick one over the other. You can have both. I did a lot of writing ETL applications in C# and I've recently written a rather complex ETL with SSIS, in which I used C#. I would say that it would really speed up your development time to use SSIS. Now that they have C# Scripting in SSIS, it really opens up your options. I think with the 2, you should be able to do just about any ETL transformation and Data modeling that you could possible need.
Good luck with your decision.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply