Friday, January 24, 2014

Run SSIS 2012 package in Integration Services Catalog from Sql Agent job with proxy account.

Problem

An Integration Services 2012 package that reads/writes files in a shared folder has to be executed under a windows account with sufficient access rights. The package is deployed with VS2012 in the Integration Services Catalogs. The package has a production and a test environment. It will be scheduled in a Sql Server Agent job. The windows account will need sufficient rights to run the package from the Integration Services Catalog. In this blog article the steps are presented necessary to grant rights to run the package by a Sql Server Agent proxy.

Solution

Prerequisites

The creation of proxy user was already necessary for SSIS 2008 packages with similar functionality, so I do not go into too much detail

  • On the Sql server that runs the job
    • In Security / Logins the windows account is added.
    • In Security / Credentials, a new credential is added with this account and password.
      image
    • In SQL server Agent / Proxies a new proxy is added, with the Credential and active to the subsystem ‘Sql Server Integration Services Package’.


Grant the proxy rights to execute the package

On the SQL server where the package is deployed, open the Integration Services Catalogs, SSISDB and right click on the projectfolder where the package is stored. Select properties

image

In the folder properties dialog window, in the select a page pane, select Permissions. Add the windows user account and check the Grant, Execute Objects and Read Objects permissions.

image

The Read Objects permission is necessary to read the Environment variables in the Environments folder.

Create the SQL server Agent job

In the job Steps add a new step that will execute the SSIS package

  • Step name: Import files
  • Type: SQL Server Integration Service Package
  • Run as: prxSSIS (the name of the proxy
  • Package source: SSIS Catalog
  • Server: the SSIS catalog server where the Project was deployed
  • Package: pathname inside the SSIS catalog, e.g. \SSISDB\FileImportExport\SSIS_A..\FileImport.dtsx
  • In the configuration Tab: check the Environment and select the necessary environment e.g. Production (the name given in the environments folder of the SSIS catalog)

Tuesday, January 14, 2014

SQL Server 2012 setup job notification by email

Step by step procedure

How to setup job notification by email on a newly installed SQL Server 2012.

  • Setup Database Mail
  • Create an operator for SQL server Agent
  • Setup notification in the job properties

Setup Database Mail

First check with your mail administrator what the outgoing mail server is and if you need a real email account for the outgoing email or if a dummy email address will suffice.image

image

image

image

image

image

image

image

image

Next screen click finish

Next screen: success message, click Close

Create an operator for SQL server Agent

image

image

Select properties

image

image