2

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)

Zach Smith
  • 2,360
  • 12
  • 32
  • 61

1 Answers1

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.

Community
  • 1
SqlWorldWide
  • 13,153
  • 3
  • 28
  • 52
  • 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 (https://dba.stackexchange.com/questions/177652/how-to-change-a-script-task-version-in-ssis) – Zach Smith Jul 05 '17 at 21:44
  • 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 – Zach Smith Jul 05 '17 at 21:44
  • @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. – SqlWorldWide Jul 06 '17 at 00:16
  • @SqlWorldide - I've tried this, but when I type in my server it can't be found. – Zach Smith Jul 06 '17 at 02:31
  • @ZachSmith this works for me. – SqlWorldWide Jul 06 '17 at 11:40
  • That is the process I go through when copying a build to the server. However I have found creating builds in VS 2015 and deploying via double clicking the built package didn't work for scripts at all (deploying to SQL Server 2016 I got 'C# 2015 incompatible` errors), and didn't work for targeting a lower version of SQL Server (I got package version errors). Both these problems went away when I deployed from VS 2015. However I don't think I can target a Server that isn't in a network (i.e. a cloud server) – Zach Smith Jul 06 '17 at 21:33