SSIS vs. T-SQL

This is a common question which comes often before any developer to choose between the two as to follow which path to choose as SSIS or T-SQL for developing ETL solutions.

well i also got similar scenario some time back and was in a dilemma to decide this.

on a big picture it's always better to go with conventional way i.e. using T-SQL and not to go with the tool.
But on doing some more reading and research over many blogs and forums and also the development phase of my work over some 2 and a half odd years. I found some good reasons and explanations over this as not to always stick to a particular approach and do proper analysis of the requirements to take a decision.

Below are some of the key points which i was able to collect regarding this.

1. If the data does need to be persisted temporarily it can be to a raw file in SSIS whereas T-SQL requires temporary tables for which there may be a management/security overhead.
2. SSIS have a very good visual representation of the "work".

3. SSIS manages memory very efficiently, which can result in big performance improvements compared to     T-SQL.

4. Bad data can be captured to a different data sink for examination later in SSIS as it have an in-built debugger where we can attach the data in between the transformations without using any temporary object and can copy that data to excel file to analyze.

5.  Exception Handling is better in SSIS as compared to T-SQL.
6. Data-flows are, to some extent, self-documenting so improves code understand-ability as far as documentation is concerned.

7. User-controlled parallel execution of data-flows is possible where it isn't really in the inherent batch operated world of stored procedures.

8. "Heavy-lifting" of data can occur on a different to machine to that storing the data.
Regarding the performance impact on the source server, since the Stored Proc's have to do all the work on the server itself, while the data flow can be pulling data in an asynchronous way and doing the heavy lifting on a different machine.

9. By default, events containing very pertinent information such as "component "<component name>" (5824)" wrote 2398156 rows" are rose which might be very useful for logging and process tracking in case of any failures or logging scenarios.

10. For each Loop Container in Integration Services takes a data type of System.Object (not the physical SQL table), which is an in-memory object that is native to the Integration Services, which is very effective in terms of performance optimization as compared to Temp tables\tables variables\cursors.

Hope you find this post Useful.

Do comment if it you need any further information on this or if you find anything here which doesn't looks convincing.

Happy Learning !!

Comments

  1. UnknownJuly 6, 2013 at 9:40 AM

    nice piece of information, I had come to know about your internet site from my friend vinay, delhi,i have read atleast 12 posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new post, once again hats off to you! Thanx a ton once again, Regards, Difference sql and tsql

    ReplyDelete
    Replies
    1. Ashish AgarwalJuly 6, 2013 at 10:37 AM

      Thanks Nandkishor..
      Always a pleasure to share the knowledge..
      do let me know if you need any specific information on any particular topic and feel free to give your valuable suggestions and feedback..

      Happy Learning !!

      Delete

Post a Comment

Popular posts from this blog

Deleting Duplicate Data From SQL Server Table

Duplicate data is a very well known and frequently occurring issue in any DBMS and dealing with deletion of duplicate data is something not that easy for one specially one who is dealing with crucial and huge data. There are 2 scenarios which can come while deleting duplicate data - 1. when we have an identity column in our table containing duplicates. The problem becomes much easier here and we can just keep the data with MIN or MAX keys removing all other. --Create New Table create table Person ( ID int identity ( 1 , 1 ), Name varchar ( 30 ), age int ) --Insert Data into Table with multiple Duplicate entries insert into Person ( Name , age ) values ( 'Ashish' , 24 ) insert into Person ( Name , age ) values ( 'Ashish' , 24 ) insert into Person ( Name , age ) values ( 'Gaurav' , 26 ) insert into Person ( Name , age ) values ( 'Nayan' , 25 ) insert into Person ( Name , age ) values ( 'Nayan
Read more

Excel Based Reporting

Hello Everyone, so today my focus is on to give insights about how can we deal with scenarios where the client wants us to help them with Excel based reporting. We will be doing this using SSIS as we will need automation of the report on a daily basis. Technology Items used - 1. SSIS 2. Excel files (format files) 3. Macro codes 4. Conditional formatting. 5. SSIS 6. C#.Net / VB.Net Script Components Step-1:  - We will be writing a few macro codes to do the same as we will need the numeric columns to be in the numeric while we insert the data from SSIS it will automatically convert it to Text. Macro-1 - Code to Convert Text to Integer. Sub Function_Name() sheetlist = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7") For i = LBound (sheetlist) To UBound (sheetlist) Worksheets(sheetlist(i)).Activate ' Your Code Goes Here Range("F:F").Select 'specify the r
Read more