none
SSIS catalog to remote SSIS instance

    Dotaz

  • I have an ETL server which has only SSIS 2014 installed on it. SQL 2014 database server is installed on another server. I would like to run SSIS only on the ETL server and pull data from remote servers to the SQL server, to distribute the load mostly to the ETL server. I like the functionality of the SSIS catalog though, is there any way of creating an SSIS catalog on the db server but pointing to the ETL server, I am trying not to install a db instance on the ETL.

    I tried in a dev env and the configuration does not look like allowing this, but pointing to the local

    Thank you,

    čtvrtek 12. července 2018 21:10

Odpovědi

  • Hi HServer,

    Yes, you are right.

    So, basically you would like to use DTexec Tool to run the package and schedule it with Windows Task Schedule.The dtexec utility provides access to all the package configuration and execution features.

    Install Integration service will install a full supported Dtexec tool (SE/EE/Dev editon) with corresponding version (32/64) . But you may still install the client Tools connectivity feature tools as well and any other components you might need to connect to your sources.

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Označen jako odpověď HSever pondělí 16. července 2018 20:56
    pondělí 16. července 2018 9:04
    Moderátor
  • Hi Pirlo,

    for the scheduling part I can use Task Scheduler which can run the dtexec. the package can also be deployed to filesystem or package store. Theoretically, db instance is not needed and this would allow running the load on the ETL server, however would not be able to use the project model and SSIS catalog this way, basically none of the features starting from 2012 version, am I not correct?

    Thank you,

    You're correct

    If you want to use project deployment model with new features and improved monitoring and execution capabilities, you need to have database instance also installed and CLR enabled.

    Otherwise you will be limited to storing packages in filesystem/package store and executing them using dtexec which provides limited logging and monitoring capabilities. Windows Task Scheduler also provide limited logs and alert mechanisms compared to SQL Server Agent as latter is tight coupled to database engine


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Označen jako odpověď HSever pondělí 16. července 2018 20:56
    pondělí 16. července 2018 9:11

Všechny reakce

  • Hi HSever,

    To use a dedicated server for extraction, transformation, and loading (ETL) processes, install a local instance of the SQL Server Database Engine when you install Integration Services. Integration Services typically stores packages in an instance of the Database Engine and relies on SQL Server Agent for scheduling those packages. If the ETL server does not have an instance of the Database Engine, you have to schedule or run packages from a server that does have an instance of the Database Engine. As a result, the packages are not running on the ETL server, but instead on the server from which they are started. As a result, the resources of the dedicated ETL server are not being used as intended. Furthermore, the resources of other servers may be strained by the running ETL processes

    See: Installing a dedicated server for ETL processes

    So, if you would like to run SSIS only on the ETL server, you have to install a local SQL instance.

    Thanks for your understanding and support.

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    pátek 13. července 2018 3:10
    Moderátor
  • Hi Pirlo,

    for the scheduling part I can use Task Scheduler which can run the dtexec. the package can also be deployed to filesystem or package store. Theoretically, db instance is not needed and this would allow running the load on the ETL server, however would not be able to use the project model and SSIS catalog this way, basically none of the features starting from 2012 version, am I not correct?

    Thank you,

    pátek 13. července 2018 17:51
  • Hi HServer,

    Yes, you are right.

    So, basically you would like to use DTexec Tool to run the package and schedule it with Windows Task Schedule.The dtexec utility provides access to all the package configuration and execution features.

    Install Integration service will install a full supported Dtexec tool (SE/EE/Dev editon) with corresponding version (32/64) . But you may still install the client Tools connectivity feature tools as well and any other components you might need to connect to your sources.

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Označen jako odpověď HSever pondělí 16. července 2018 20:56
    pondělí 16. července 2018 9:04
    Moderátor
  • Hi Pirlo,

    for the scheduling part I can use Task Scheduler which can run the dtexec. the package can also be deployed to filesystem or package store. Theoretically, db instance is not needed and this would allow running the load on the ETL server, however would not be able to use the project model and SSIS catalog this way, basically none of the features starting from 2012 version, am I not correct?

    Thank you,

    You're correct

    If you want to use project deployment model with new features and improved monitoring and execution capabilities, you need to have database instance also installed and CLR enabled.

    Otherwise you will be limited to storing packages in filesystem/package store and executing them using dtexec which provides limited logging and monitoring capabilities. Windows Task Scheduler also provide limited logs and alert mechanisms compared to SQL Server Agent as latter is tight coupled to database engine


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Označen jako odpověď HSever pondělí 16. července 2018 20:56
    pondělí 16. července 2018 9:11