10

I'm currently in the process of upgrading from SQL Server 2008R2 to 2014 (both Enterprise). There are a plethora of SSIS jobs that are in production, and which will need to be migrated. I'm trying to get a handle on how I should manage SSIS jobs going forward.

In 2008R2, I would always using BIDS to deploy packages to MSDB. All permissions were then controlled through SQL Server.

In 2014, I see that you can still save to the file system or MSDB, but that there is now the SSISDB that you create as an Integration Services Catalog. This method clearly allows a lot more flexibility with the addition of easy variable access and even environment variables.

Is deploying SSIS packages to SSISDB in 2014 now the best practice way of deploying and managing SSIS projects, rather than to deploy to MSDB? Am I still able to manage permissions? When I backup SSISDB, are all of my deployed projects backed up (like before with MSDB)? Lastly, when I schedule these packages via the SQL Agent, do they still behave the same, where the permissions of the SQL Agent service account and job owner determine the SSIS package permissions when it is run?

Many thanks to anyone who can help. I've been on Microsoft's site all day, and while the documentation is very helpful, it doesn't actually answer these questions specific questions.

jamiet
  • 10,501
  • 14
  • 80
  • 159
Skkra
  • 121
  • 1
  • 1
  • 5
  • 1
    2008 packages will run on 2014 as is. Technically, the engine will first upgrade and then run them but the package is only changed in memory, the xml on disk/in msdb will remain at 2008 version. 2012/2014 gives us a new SSIS model, the [project deployment model](http://stackoverflow.com/search?q=user%3A181965+project+deployment+model) which treats your SSIS packages more like an assembly versus individual files. Only the .ispac will deploy into the SSISDB. You can't make individual files go. – billinkc Jan 15 '15 at 18:30
  • Thanks, Bill. That does help. I think though that my main question was, is using the SSISDB now the "correct" way to deploy modern SSIS packages? It seems that the answer is yes. – Skkra Jan 15 '15 at 19:27
  • To be precise, the SSISDB is only for deploying an ispac, which is a collection of packages. Some people lose their minds when they think I have to redeploy all the packages since I just changed *one*. I correlate this to poor change management practices as people aren't completely convinced they know what's in production. – billinkc Jan 15 '15 at 19:32

1 Answers1

9

I recently took the SSIS Exam (70-463) so i can tell you some things about the new deploy model.

Short Answer:

Yes SSISDB is Best Practice. Packages can be deployed to SSISDB. Packages keep a deployment history (like a very basic version control) so you can even rollback some revisions of your package.

Main Advantage of the new model is the configuration. You don't need XML or dedicated SQL tables to save your configurations. You can use input parameters and map them with enviroments defined on sql server.

You can manage security through SQL Server because now everything can be handled via SQL Server Security.

Another cool feature is the Integration Services Dashboard, a report automatically built with report services template. Just click Integration Service Catalog and right click your packages to view "All Executions".

You can see very detailled Information about your packages including execution time.

Long Answer: In my opinion the main advantage are the project parameters. Imagine this like Parameters you can pass to the SSIS Package. You can parametrize your Connection Manager or just parts of it.

Example: You can parametrize the server name and in your ssisdb you can create two enviroments (or more) called "development" and "production". Then you can add variables to both of them and map them to the input parameter of your package. The main advantage is that you can deploy a package to SSISDB and link to an environment and you don't have to handle the connection strings by yourself.

Dave Markle
  • 95,573
  • 20
  • 147
  • 170
tuxmania
  • 906
  • 2
  • 9
  • 28