Post reply

How are task implemented internally

  • May 25, 2014 at 9:33 pm

    #291668

    I am relatively new to SSIS.

    I was ask by one of my friend, what is the need of SSIS.

    I told him that using SSIS we can load the data fast from other sources into SQL Server,

    and showed him some links where it was proved that SSIS loads the data faster.

    Then he ask me, this all loading and cleaning and dumping at the backend should have being

    implemented using SQL Scrpits ? I said i dont know and search about it but to no help.

    Now here are my two questions ,

    1. How is the loading,cleaning and dumping implemented in SSIS

    2. If it is through queries , then why we use SSIS , can't we just used use queries.

    Thanks.

  • May 25, 2014 at 11:52 pm

    #1716430

    Shadab Shah (5/25/2014)

    ...showed him some links where it was proved that SSIS loads the data faster.

    Faster than what? Show us the links, please.

    Then he ask me, this all loading and cleaning and dumping at the backend should have being

    implemented using SQL Scrpits ?

    Yes, it's possible and it's not so difficult. You can use BCP, BULK INSERT (which is why I want to see the links you said you have on the subject), ACE Drivers with OPENROWSET, and a couple of other things. I don't use SSIS at all. It's not that it's bad. I just don't need it because I know how to do ETL all from T-SQL.

    Now here are my two questions ,

    1. How is the loading,cleaning and dumping implemented in SSIS

    From what I understand, it uses a similar engine as that found in BULK INSERT. It's a little "smarter" because it will easily handle quoted identifiers, which BCP and BULK INSERT don't actually support. Still, if the data is well formatted, both can handle true CSV with quoted identifiers quite easily. If it's not well formatted, a quick trip to PowerShell from T-SQL does the trick or you can use the Ace Drivers. Just as with SSIS, you do need to study and know the tools for them to work easily for you.

    2. If it is through queries , then why we use SSIS , can't we just used use queries.

    We can do it all through queries. That's part of why I don't actually use SSIS.

    Still, it's a tool that some folks like. You have to learn a new language to use it for more than just simple stuff but it does have the advantage of a informative interface you can watch data progress through although you do have to sometimes call T-SQL or other code because it can't actually do all of the ETL that a lot of people want. Why use it? Because it makes it easier to do parallel processing, set up processes, and has the benefit of a lot of click'n'drag programming. It's really good/easy at process control.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 26, 2014 at 1:05 am

    #1716437

    Jeff Moden (5/25/2014)

    Shadab Shah (5/25/2014)

    ...showed him some links where it was proved that SSIS loads the data faster.

    Faster than what? Show us the links, please.

    Thanks for the response Jeff. By faster i mean that through SSIS we can load Millions of record in less time and I showed him this lin

    http://stackoverflow.com/questions/6061075/how-can-i-load-a-large-flat-file-into-a-database-table-using-ssis/6159600#6159600

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply