Wednesday, December 26, 2018

SSIS

Purpose

Setup a package to use Project parameter connections and deploy to Integrations Services Catalogs

Design

1. Open or Create a new solution

2. Setup the amount of environments in Configuration Manager

clip_image002

clip_image004

3. Open Package and Setup connections. In this case sql db. Right click on Connection Managers

clip_image006

4. Select New and select server and database.

clip_image008

5. Rename connection to something more meaning full ie SQL_Source

clip_image009[4]

To

clip_image010[4]

6. Convert to a project connection.

clip_image012

Name will change from SQL_Source to (project)SQL_Source

clip_image014

7. Parameterize Connection

clip_image016

7.1. Click Ok

clip_image018

double click Project.params to open

clip_image020

8. Add Parameter to Configuration

clip_image022

8.1 Click Add

clip_image024

Now you can adjust the connection strings for each environment

clip_image026

9. You can test that the connection string changes per environment by adjusting the solution configuration

clip_image028

clip_image029[4]

10. Repeat Steps 3-9 for destination connection

clip_image030[4]

clip_image031[4]

11. Lets setup a simple data flow task to test this.

clip_image033

clip_image034

Deployment

1. Skip this step if Integration Services Catalogs has been setup. Setup Integration Services Catalogs.

clip_image035

2. Setup folders in Integration Services Catalogs

clip_image036

4. Deploy package/s for DEV.

clip_image038

Follow the wizard (Please note that a new deployment in project was created in Integration Services Catalogs).

clip_image040

5. Repeat step 4 for test configuration

clip_image041

6. Check configuration

clip_image042

6.1. Dev Environment

clip_image044

6.2. Test Environment

clip_image046

7. Execute Dev package

clip_image047

clip_image048

Result

clip_image050