Sql-server – How does SQL Server handle SSIS project deployments, and what is best practice executing these packages

sql serverssis

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

Main.dtsx
ArchiveFiles.dtsx
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:

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)
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)

Best Answer

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.

Related Solutions

Sql-server – Error trying to run SSIS Packages in SQL Server 2008 SQL Server Agent

It sounds like your connection manager is setup to use windows Authentication. If that is the case, when you execute the package via the SQL Server Agent it will run with credentials of the agent.

You need to do one of three things:

Give the account that the SQL Server Agent runs as appropriate permissions to execute SPs in your database.
Setup a proxy account for the agent with the appropriate permissions (as suggested by Jon Seigel).
Use a different authentication method - i.e SQL Server Auth.

I would go with option 1 if possible, but it depends what your company's policies are.

Sql-server – dtsinstall.exe does not deploy packages

DTSInstaller.exe consumes a file called with an extension of SSISDeploymentManifest

The contents of it look like

<?xml version="1.0"?> 
<DTSDeploymentManifest 
    GeneratedBy="HOME\administrator"
    GeneratedFromProjectName="SSISHackAndSlash"
    GeneratedDate="2010-04-30T21:19:08.6336677-05:00"
    AllowConfigurationChanges="true">
        <Package>Package.dtsx</Package>
        <Package>Package2.dtsx</Package>
        <Package>Package7.dtsx</Package>
</DTSDeploymentManifest>

That file defines the universe of SSIS packages the DTSInstaller will operate upon.

It sounds like the manifest file you are using does not have match the list of packages in your project. You can right click on the project itself and select Properties, Deployment tab and change the value of CreateDeploymentUtility from false to true.

Now when you right click on your project to build, or deploy, your bin\Deployment folder will contain a new version of the manifest file. Double click on that and you should have all the desired packages ready for installation.

An alternate approach, would be to use your manifest file but have PowerShell generate the deploy commands. Advantage of that is you can audit what's being deployed versus the black box of DTSInstaller.exe PowerShell, dtutil, SSISDeployManifest - How to deploy SSIS packages with PowerShell

Best Answer

Related Solutions

Sql-server – Error trying to run SSIS Packages in SQL Server 2008 SQL Server Agent

Sql-server – dtsinstall.exe does not deploy packages

Related Question