How to manage an existing SSIS deployment if the client decides to use an Oracle database later on?


  • Gurus!

    My current project involves a lot of ETL tasks for which I am using SSIS as the ETL tool, and of course, for storage needs, my database is MS SQL Server 2008 R2.

    But at a later point in time, if the client decides to use Oracle for data storage, how do I manage the situation? I would still want to use SSIS for ETL.

    I know I can still write packages and script transforms to send data to Oracle, but suppose the client demands to uninstall SQL Server itself, then how do I still programatically generate packages to send data to Oracle?

    Is there a 'bare minimum' part of SQL Server that I can install so that I can still access the libraries to write script transforms and generate packages dynamically for an Oracle database?

    Seeking your wisdom Gurus!

    Novice Kid

    Tuesday, October 08, 2013 7:42 AM


All replies

  • Hi Novice Kid !

    You can use OLEDB connection managers in your SSIS packages: to switch between SQL Server and Oracle, you'll only need to change the connection string. (And of course, don't use SQL Server specific functions)
    (If you're using SSIS2012, the recommendation is to use ODBC connection managers)



    Tuesday, October 08, 2013 2:14 PM
  • Thanks Guru Guillame!

    I will definitely factor in your advice!

    Just to explain a bit more about my worry, the current situation is that I am writing some script transforms to dynamically generate packages for the SQL Server database. Obviously, I have SQL Server installed on the server where I am running the dynamically generated packages.

    Now my worry is, if at some point in future, the client for whom I am doing this project demands to uninstall SQL Server from the server and install Oracle 11g or something similar in its place, then will my SSIS packages still work? Or will they get broken?

    In such a case, do I insist that they keep SQL Server installed on the server just for the sake of SSIS, or is there some smaller exe I can install, just so that my script transforms and packages can still run when they decide to switch to Oracle? Is it neccessary to have a full version of SQL Server running to have programmatic SSIS packages running?

    Please enlighten me with your wisdom on this Gurus!

    Novice Kid

    • Edited by Novice Kid Tuesday, October 08, 2013 2:30 PM
    Tuesday, October 08, 2013 2:29 PM
  • The full version of SQL Server is not required.

    You can install the SQL Server Integration Services only, and use the dtexec tool to run your packages.

    Remark: with SSIS 2012 using project deployment model, you'll need the SQL Server engine in order to store the SSIS catalog. So if you're using the 2012 version, I recommend you to use the package deployment model if you want to uninstall SQL Server later on.

    For more information, you can have a look there:
    Tuesday, October 08, 2013 2:38 PM
  • Many thanks once again Guru Guillame!

    Let me try out a PoC using your ideas!

    Novice Kid

    Tuesday, October 08, 2013 3:06 PM