Ssis deployment

SSIS Deployment

Parameters,

configurations,

Deployments,

SCHEDULING

Parameters vs Variables

	Variables	Parameters
Usage	Variables are used inside the package for passing values between control flow and data flow task or used for computations: Set property values for tasks. Store an iterator or enumerator value for a loop. Set input and output parameters for a SQL query. Store results from a SQL query. Implement conditional logic in an expression.	Parameters are used to pass in data from outside of the package. ex. table or column names, usernames, passwords or connection strings etc. Parameter can help you do the project deployment and you can set it up in SSISDB catalog, while variable cannot.
Scope	Only package level. (Also, can be task or container level within pkg)	Parameters can be defined at 2 different levels: Project and Package
Run-time behavior	Variable’s values can change at run time	Parameter’s value can't change
Expression property	Variables do have an expressions property.	Parameters don't have an expressions property.

Configurations

While deploying the packages to the production environment, it is a bad practice to edit the package configuration settings in SSDT.

• For example, if we place connection manager details inside the package, then we must edit them using SSDT only.
• To avoid these, we use SSIS Configuration settings.

Project deployment model (2012) configurations can be done at SSMS by passing in Environmental Variables' values into parameters

Package deployment model (2008 earlier) configuration done on packages level:

• Configuration types:
• XML Configuration File: (the most common approach)
• Environment Variable:
• Registry Entry:
• Parent Package Variable:
• SQL Server:

SSIS Deployment

• 2008 R2 and earlier:
• File system deployment using Manifest file
• SQL server deployment to MSDB
• 2012 and later:
• Project Deployment File using ispac file
• SQL Server deployment to Integration Services Catalog SSISDB

Note: Single package deployment is available now in SQL Server Integration Services 2016.

2008 R2 and earlier

2012 & Later

Different ways Schedule SSIS Package

• SQL Server Agent Job
• Windows Scheduler (uses DTEec.exe)
• Any third-party Scheduler