CODEWITHSUNDEEP
sql-server-2008ssis
Chad
Chad

Reputation: 24689

Running SSIS Packages that are saved on the SQL Server vs. running packages saved as dtsx (XML)

When I run VS2008 locally and open up a package that points to a remote database and run, I believe that the data, from the input file to the db server, is running through my PC, even if the data file is on the database server.

However, if the SSIS package is stored in SQL Server and I start the job through SQL Agent, my PC is out of the picture and that data does not flow through my PC and so I should get see a signatificant performance boost.

Is this the case? I just want to confirm. Currently, I do not have permission to save a Package on our development server and I am considering requesting rights to be able to do for the above reason provided that it is a valid reason.

What kind of access does one have to have to be able to save SSIS Packages on a SQL Server? Might there be a reason to deny me rights to do so perhaps because granting me such access would require giving an elevated access level that would also allow me to do other things that the DBA might not want me to do? As a developer, I think that I should be able to shuffle data from UAT, or so iother non production env into a DEV database without having to request that a DBA do it when he gets around to it.

Upvotes: 1

Views: 1568

Answers (1)

Pondlife
Pondlife

Reputation: 16240

Your understanding of where the package executes is correct, and performance will certainly be improved by moving execution to the server. At least, it will be if the server has more system resources than your workstation, especially RAM. And avoiding using the network unnecessarily is helpful too, of course.

There are specific roles created in the msdb database for managing SSIS packages so your DBA can let you deploy and run them without making you a sysadmin. However, as the documentation says, there is a possible privilege escalation issue if you run the packages from jobs so the recommended solution is to create a proxy account.

Upvotes: 1

Related Questions