SSIS and SQL Linked Server

• hurricaneDBA

  SSCertifiable

  Points: 5027

  More actions

  July 4, 2019 at 7:20 am

  #3659981

  hi everyone

  I hope everyone is having a good break.

  We are trying to use SSIS to get data from an RTDB using a linked SQL server since the RTDB database is in a different network. My question is which connection manager will we need to use to create the connection between SSIS and the linked server which is a sql server database?

   

  kal

• Thom A

  SSC Guru

  Points: 99137

  More actions

  July 4, 2019 at 7:37 am

  #3659982

  You don't have linked servers in SSIS; Linked Servers is a Data Engine "thing". You can have multiple OLEDB Connections in a package, and those can reference different Servers/Instances in a single SSIS package/project. You just need to create the appropriate OLEDB Connection(s).

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• hurricaneDBA

  SSCertifiable

  Points: 5027

  More actions

  July 4, 2019 at 8:26 am

  #3659989

  Dear Thom

  thanks for the reply.

  So to connect between SSIS package and a separate linked server I would create an OLEDB connection using the connection managers. Is that the procedure?

  Kal

• Thom A

  SSC Guru

  Points: 99137

  More actions

  July 4, 2019 at 8:46 am

  #3659992

  hurricaneDBA wrote:

  So to connect between SSIS package and a separate linked server I would create an OLEDB connection using the connection managers.

  You just need to create an OLEDB connection for that instance yes. It's not a "linked server", like I said there is no such thing in SSIS. You can have as many, or as few, connections you want in an SSIS package/project and you can multiple of the same type, and that includes OLEDB. So you could have 4 OLEDB connections in the package/project that all point to completely different RDBMS instances, and when you want to use that connection, just select to correct one for the connection manager in your task.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk