CODEWITHSUNDEEP
ssis
user2773301
user2773301

Reputation: 11

SSIS Installation on Application Server VS Database Server - Which is right option

As the subject says I am confused myself with this question.

A) I built DTSX package that performs the below tasks in sequence

  1. download zip files from ftp to local folder,

  2. Unzips the downloaded file to another folder

  3. Exports the unzipped flat file data to database tables

B) I have built a web application where on a button click event I am loading the above dtsx package file and executing it.

This works fine as long as AppServer(machine where web app is deployed) and Database Server are the same machine with SSIS installed.

Questions:

Q1) When AppServer and DB Server are two different machines in any production environment, and to make the above application work successfully SSIS needs to be installed on App Server. IS THIS CORRECT? or Installing few components on App Server should be fine?

Q2) In my case AppServer doesn't have SSIS installed but DB Server has SSIS. What should I do now?

Q3) In general which is the right approach?

Upvotes: 1

Views: 1062

Answers (1)

billinkc
billinkc

Reputation: 61211

A1) Yes, you would need to install the Integration Services components on the App Server. Be advised, installing a "few SQL Server components" on AppServer will consume a SQL Server license. This might be be 6 to 22k per socket/core depending on your edition and version.

A2) Request that the package run on the server where it is licensed. This might be as simple as firing off a SQL Agent Job to as complex as creating a windows service on the DB server to start packages. The right answer will depend on a host of factors not supplied in the question (always the same folder {ftp and unzip location} or dynamic? Is concurrency an issue? etc)

A3) Mu

Upvotes: 1

Related Questions