Search code examples
sql-serverssissql-server-data-tools

Where do i create a SSIS catalog

I have a question that might be very basic. If I have a SSIS package where I have the Transfer DB task to transfer a database from a server with a SQL Server 2016 Instance to another server with an Instance SQL Server 2019,

  1. Do I need to install SSIS for each instance on each server?
  2. Do I need to create the catalog in both source and destination instances? or is it enough to create it only on the source instance? or it has to be on the destination?

Thanks!

Solution

  • SSIS is an ETL tool, so you'll install SSIS on the server that you want to designate as your ETL server. There are several common scenarios for where that server is located.

    1. A stand-alone ETL server, which is neither the source nor the target of the current ETL job you're working on.

    2. A separate ETL server that is an instance on the same server as either your current source or your current destination.

    3. Your current source server

    4. Your current destination server

    You'll have to decide which scenario makes the most sense in your environment. At my current gig, we have a few dedicated ETL boxes, and we cluster the jobs on them by subject matter.

    Previously, I worked in an environment where 10% of the ETL was importing data and 90% was manipulating it on one server. In that situation, it made sense to have the SSIS stuff all on the destination server so that we weren't moving data over the wire to transform it.

    As your situation evolves, and you use more and more SSIS, you might change your mind. That's fine. It's not a trivial thing to change, but it's also not set in stone once you plunk SSIS down someplace.