SSIS

Only one instance can be installed per computer

IS is a shared features in the installation

In previous versions of SQL Server (<2012), by default when you installed SQL Server all users in the Users group had access to the Integration Services service.

To grant access to the Integration Services service

1.Run Dcomcnfg.exe. Dcomcnfg.exe provides a user interface for modifying certain settings in the registry.

2.In the Component Services dialog, expand the Component Services > Computers > My Computer > DCOM Config node.

3.Right-click Microsoft SQL Server Integration Services 11.0, and then click Properties.

4.On the Security tab, click Edit in the Launch and Activation Permissions area.

5.Add users and assign appropriate permissions, and then click Ok.

6.Repeat steps 4 - 5 for Access Permissions.

7.Restart SQL Server Management Studio.

8.Restart the Integration Services Service.

Deployment model comparison

 

Feature	Package Deployment	Project Deployment
Unit of Deployment	Package	Project
Storage	File System or msdb	SSIS Catalog
Dynamic Configuration	Package configuration	Environment variables mapped to project-level parameters and connection managers
Compiled Format	Multiple .dtsx files	Single .ispac file
Troubleshooting	Configure logging for each package	SSIS catalog includes built-in reports and views

One noticeable difference in the SSIS project created in SQL Server 2012 is that by default the SSIS project will be created in Project Deployment mode, but if you need to you can change it by right clicking on the project in Solution Explorer and clicking on "Convert to Legacy Deployment Model". 

A SSIS project in Project Deployment model creates a deployment packet (with *.ispac extension) that contains everything (all packages/parameters) needed for deployment unlike the Legacy Deployment mode in which each SSIS package is separate unit of deployment.

The second screen of the wizard is the place where you actually specify the location to deploy from; you can either choose the deployment packet (*.ispac) file or choose an already deployed package from the Integration Services catalog as the source for the deployment. Since I want to deploy from the SSIS project, I have specified the *.ispac deployment packet name:

*Project deployment file
*Integration Services Catalog

In SQL Server 2014 (SSMS), you can roll the project back by selecting version. In the Project Versions dialog box, simply select the older version and click Restore to Selected Version to roll back the entire project to an earlier release.

Project Deployment model

–To configure permissions, right-click on the project in SSMS and select Properties.

–Go to the Permissions and click Browse to grant rights to a new role or SQL Server user that's in the catalog database called SSISDB.

–By default, any user in the DBO role of the SSISDB database will automatically have full rights to your project unless you revoke those rights.

Permissions:

http://www.mssqltips.com/sqlservertip/3153/managing-ssis-security-with-database-roles/

Either install SSIS only or
Install SSIS & Data Engine instance (Preferred, SQL agent needed for IS job scheduling)

My First Integration Services Solution
https://code.msdn.microsoft.com/My-First-Integration-fa41c0b1

Reference: https://www.youtube.com/watch?v=3cPq9FXk-RA&index=1&list=PLNIs-AWhQzcmPg_uV2BZi_KRG4LKs6cRs

Control Flow --> is used to build and modify the control flow in the package, contains flow chat type

diagrams.

Data Flow -->

Five types of connection managers
Ado.net
Excel
flat file
ODBC
OLE DB
Raw file
Xml

Connection created in Project Connection managers can be used in any package with in the project
Connection created in one package cannot be shared with another package in the same project.

Debugging data flow tasks:
Using data viewers

Debugging control flows:
Setting breakpoints
Watching variable values

Difference between variable and parameter
Paramater can be passed at runtime, starts with $Package
Variable value is passed in SSIS cannot be changed in runtime, start with User


Deployment in SSIS
upto 2008 R2, For project deployment--> Click Convet to Package deployment model