How does SQL Server handle SSIS project deployments, and what is best practice executing these packages?

Question

I'm quite new to SSIS (actually SSDT with VS2015), and have a .sln that uses 3 packages:

1. Main.dtsx
2. ArchiveFiles.dtsx
3. FTP.dtsx

The 'entry' point (is this the correct way of thinking of it?) is Main.dtsx, which will execute the 'child' packages (no. 2 and no. 3) at various points in the flow.

To deploy, I simply just copied the wizard (the .ispac file in /bin/Development) which seemed to recreate my project within the Integration Services Catalogs folder on SQL Server.

I have been able to execute the SSIS solution by right clicking and executing the Main.dtsx package from SQL Server. Same with creating a scheduled job (just tell it to execute the Main.dtsx package and everything works).

I'm deploying to SQL Server 2016 and SQL Server 2012.

Questions:

1. Since the project seems to have been copied verbatim to SQL Server, and the code is just XML, could I edit the deployed packages in the future? (I'm using Git for source control so I would NOT want anyone editing the package directly and then someone else deploying from the source control)

2. Is my deployment method correct and best practice? Seems like this is too simple and intuitive for the Microsoft world... (considering the headache and pain involved in working with SSIS in general)

• SqlWorldWide: Mentioning the version will be useful. There has been some big changes around ssis package deployment and execute options.
• Zach Smith: @SqlWorldWide - added.
• user507: Look at using Biml to manage your package. It will allow you to more easily change and redeploy if your data structure changes over time.

Answer 1

Could I edit the deployed packages in the future?

You can import from GIT, open the package in SSDT and deploy again. Overwriting the existing deployed package.

You can also export using SSMS save and open in SSDT. Once you make your changes deploy again and update your source control.

As far as not letting anybody else modify or deploy packages this document will give you a good start. A SSISDB is considered another user database.

Is my deployment method correct and best practice?

As you are using datatools to create your solution I would follow this method (assuming you have the privilege).

Deploy packages by using SQL Server Data Tools (Visual Studio)

In Visual Studio, with an Integration Services project open, select the package or packages that you want to deploy.

Right-click and select Deploy Package. The Deployment Wizard opens with the selected packages configured as the source packages.

Complete the wizard. Follow the remaining steps described in Package Deployment Model.

You can also do the same using SSMS. Details here.

In case you are not aware that you can deploy package from SSMS using tsql, details here.

• Zach Smith: Thanks very much - what is the difference between deploying the package from Visual Studio vs using the wizard created on a build? I've noticed that deploying from VS allowed my scripts to work (when I had them), but I thought the fact that the scripts didn't run when deployed via the package just a bug (dba.stackexchange.com/questions/177652/…)
• Zach Smith: Also, my development computer is separate to the server that the package is deployed on. I would prefer not to have to download data tools onto the server
• SqlWorldWide: @ZachSmith you do not have to have data tools in the deployed server. You can point from your dev server SSDT. In fact after you deploy it you can run against different environment using environment variable.
• Zach Smith: @SqlWorldide - I've tried this, but when I type in my server it can't be found.
• SqlWorldWide: @ZachSmith this works for me.