Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
100% SISS or Mixing SISS with SP´s decision for future migration??

Answered 100% SISS or Mixing SISS with SP´s decision for future migration??

  • Tuesday, May 01, 2012 7:17 PM
     
     
    Sign In to Vote
    0

    Ive read several previous post on this matter.  And Ive learned that... always... "it depends"

    My feelings goes for store procedures for keeping in one tier the business logic and modifying in one click and re-use code its easy and makes my developing time faster that using every single SISS box for the same purpose... for extract and load operations I would go for data flows transformations, and of course sql task for running my sp´s... bottom line I came up that is good to use the best of the two approaches ... SISS and SP´s ...

    but someone is questioning what if in the future we want to migrate to Oracle ... all the stored procedures would have to be replaced in PL/SQL ... and they are arguing that if we construct ETL with no Transact queries at all...and we do it all using Transformations, Lookups, merges, unions, joins with SISS the migration to Oracle would be transparent... as if you change your configuration package and point to a Oracle database and all the tables will be linked and the columns as well (in case all sql tables and columns is named the same in oracle) ... the packages will not need manual re-configuration or adjustments and such things... is this true?   I would like to hear some thoughts on this matter in order to complete my vision or opinion in using 100% SISS or mixing with SP´s

    Thank you in advanced

    Regards

    Adriana

     

All Replies

  • Tuesday, May 01, 2012 7:33 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hello Adriana,

    It would be naive to state a migration to Oracle can be transparent. Even the syntax of executing stored procedures (called procedures that reside in packages in Oracle) is different.

    At the very least you will end up modifying each package because you will need a different connector set and then deal with new datatypes (potentially).

    In addition, most packages that shovel a lot of data perform poorly using the default Oracle connector so using the one from Attunity is the common practice.

    Arthur My Blog

     
  • Tuesday, May 01, 2012 8:22 PM
     
     
    Sign In to Vote
    0

    Thank you for your answer

    For store procedures yes I knew that wouldnt come transparent...

    But for 100% SISS packages and data transformations... the reason they gave me was... SISS manages its owns data types and thats why if new data types comes thrue the flow it wouldnt afect since they will be transformed to SISS data types...

    so what do you suggest?

    thanks

     
  • Tuesday, May 01, 2012 8:33 PM
     
     Answered
    Sign In to Vote
    0

    I will say it goes parallel(SSIS,Database Engine), there are places where you need to use SPs/TSQL or PL SQL Queries to do set base updates.

    If someone say you have to use only SSIS and there are good amount of update/Deletes, OLE DB Transformation is not going to work great, That's where people use the power of Database Engine( Set base queries).

    As Arthur explained, when you will change your source from oracle, you have to make require changes, This includes using new drivers( OLEDB --> Attunity, DataTypes, functions even ( ISNULL-->NVL) etc.

    By building everything in SSIS is not going to help in migration straight forward. Use best practices according to current environment and when you migration then you have to make changes or use best practices according to new source/destination etc.

    Thanks

    Aamir 


    http://sqlage.blogspot.com/


     
  • Tuesday, May 01, 2012 8:45 PM
    Moderator
     
     
    Sign In to Vote
    0

    Thank you for your answer

    For store procedures yes I knew that wouldnt come transparent...

    But for 100% SISS packages and data transformations... the reason they gave me was... SISS manages its owns data types and thats why if new data types comes thrue the flow it wouldnt afect since they will be transformed to SISS data types...

    so what do you suggest?

    thanks


    Just to add to Aamir's rightful comments, thing is, in short, SSIS requires static metadata that is going to be broken right at the moment you make change  the package to work against an Oracle connection.

    Arthur My Blog

     
  • Tuesday, May 01, 2012 9:45 PM
     
     
    Sign In to Vote
    0

    Thank you all for your kind answers, definetly I will help me in taking my decisions.

    Regards