Configuring SSIS Prior to Installing Custom SSIS Component

The following section explains how to configure SSIS and Custom SSIS Component, which is used by FASTER Web SSIS based integrations. FASTER Web must be installed prior to following the instructions below:

Important Notes:

If SSIS is not deployed to the server that will host the import utilities, SSIS needs to be deployed. (You may already have SSIS on the DB Server that hosts FASTER Web DB and can choose to host the utilities on that DB Server. This is the scenario that FASTER has tested. However, you may opt to have the import utilities hosted on a different server. Once SSIS is deployed and proper permissions are granted, you can proceed to the SSIS configuration steps below.)
If the database server was created from a virtual image, ensure @@SERVERNAME matches the database server name. If database server name and @@Servername don’t match, please use the below queries and set @@Servername similar to the database server name.
EXEC sp_DROPSERVER 'Value of @@Servername'
EXEC sp_ADDSERVER 'Value of Database server name', 'local'
The @@SERVERNAME value must be used in the SSIS Server Name.

Prerequisites

SSIS requires the login and password of a Windows Authenticated user. Ensure that you have valid windows credentials when performing the following steps. Creating login [domain]\[windows username] is recommended, where [domain] is the name of your domain. For example: <mydomain\windowsUser>.

1. Create SSISDB Integration Service Catalogs using SQL Server Management Studio (SSMS)

a. Open SSMS

o Select the Database Engine as the Server Type.

o Use credentials for SQL Server Admin User.

b. Navigate to Integration Services Catalogs and right-click, select Create Catalog…

o General

· Password: {password of your choice}

· Retype Password: {see Password above}

o Click OK.

2. Create SSIS Admin user login using SSMS

a. Open SSMS

o Select the Database Engine as the Server Type.

o Use credentials for SQL Server Admin User.

b. Navigate to Security > Logins and right-click, select New Login…

o General

· Login name: [domain]\ssis.admin

· Windows authentication

· Default database: SSISDB

o User Mapping

· msdb

Roles

♦ db_ssisadmin

♦ db_ssisltduser

♦ db_ssisoperator

♦ public

♦ SQLAgentOperatorRole

♦ SQLAgentReaderRole

♦ SQLAgentUserRole

· SSISDB

Roles

♦ public

♦ ssis_admin

o Click OK.

3. Create SSIS Package Execution Proxy using SSMS.

a. Navigate to Security > Credentials and right-click, select New Credential…

o Credential name: ssis.admin

o Identity: [domain]\ssis.admin

o Password: {password for [domain]\ssis.admin}

o Confirm password: {see Password above}

o Click OK.

b. Ensure the SQL Server Agent is running.

c. Navigate to Security > SQL Server Agent > Proxies > SSIS Package Execution and right-click, select New Proxy…

o General

· Proxy name: ssis.admin

· Credential name: ssis.admin

· Active to the following subsystems: SQL Server Integration Services Package

o Principals:

· Name: [domain]\ssis.admin

· Type: SQL Login

o Click OK.

4. Set SQL Server Agent Start Mode to Automatic

a. Launch SQL Server Configuration Manager.

b. In the left pane, select SQL Server Services.

c. In the right pane, double-click SQL Server Agent.

d. Click the Service tab.

e. Click the Start Mode.

f. Click the drop-down arrow, and select Automatic.

g. Click OK.

Configuring SSIS Prior to Installing Custom SSIS Component

Prerequisites

Related Topics