SSIS Package - SSIS DB, Security and Upgrades

Introduction

We all know SSIS packages are for extracting data from multiple sources, cleaning and processing them and then storing these processed data into another relational database which we call Data warehouse. The source can be anything ranging from another SQL Server Database, excel sheet, flat files, emails or CCTV footages. The idea of a data warehouse is to bring all those data into one place for the users to process, analyze and generate reports which would help to decide the future course of action for an organization.

For an organization using SSIS package diligently, there can be many such packages deployed in its premises. Each of those packages can have different time schedules for refreshing data. It will be impossible for a person to update those packages manually every day. This is where schedulers come into play.

We develop SSIS packages in the development environment. Once we are satisfied with the development we upload the package in the server using the SSISDB Catalogue and schedule the package.

Over the next few paragraphs, we will learn how to schedule an SSISDB catalogue, upload a package and schedule it to run automatically.

How to setup SSISDB

The SSISDB catalogs are the main point for working with Integration Services (SSIS) projects that is deployed to the Integration Services server. For instance, you can set task and package parameters, design conditions to determine runtime values for package, execute and investigate packages, and oversee Integration Services server activities.

If you have installed your visual studio package and Microsoft SQL Server with all the BI components, you will probably see a tab named Integration Service Catalogue under the database server tab in SQL Server Management Studio. It will look somewhat like below.

For the next few paragraphs, we will learn how to set up an SSIS Catalogue DB. This is a onetime setup and only one Catalogue DB can exist in one SQL Server.

Step 1

Right-click on the integration service catalogue and select create catalogue.

Step 2

Once create catalog is selected, the  following screen appears.

Step 3

To use the catalogue we need to select the Enable CLR Integration checkbox like below.

Step 4

Enable automatic execution of Integration Services stored procedure at SQL Server startup and then put the password in the password slot and click OK.

Step 5

The catalogue database creation process will start and finally, you will get to see a created catalogue database in your database server as below.

Uploading a package into SSIS Catalogue DB

There are two ways how you can upload an SSIS package into the catalogue database. We will learn about both the ways, one by one in the next few paragraphs.

Let us assume we have an SSIS project like below. We will upload the project into Catalogue Database using both the technique one by one.

Process 1

Step1

Right-click on the project name and click on deploy.

Step 2

The following screen comes up

Click on next

Step 3

The following screen comes up.

You can see that the physical location of the file is already selected since you are deploying the file from the visual studio application.

Click on next.

Step 4

Here we need to put our server name where our catalogue database is hosted. You also need to put the destination folder name in the catalogue database.

Put the required value and click on next.

Step 5

The following screen appears.

Click on deploy.

Step 6

If everything is alright, you will get the following screen with the details of the deployment.

Click on close.

Step 7

Check the  SSIS Catalogue database inside the Microsoft SQL Server database server and see if the project is properly uploaded or not.

Process 2

We can do this process from Microsoft SQL Server as well. The next few lines will show how we can control the deployment process from the Microsoft SQL Server.

Step1

Right-click on the SSISDB under integration service catalogue and select create folder.

Step 2

The following screen appears. Put the name of the folder and the description as below and click on OK.

Step 3

The folder will be created inside the catalogue database as below.

Step 4

Right-click on the project folder and click on deploy. The integration service deployment wizard window will pop up.

From here the rest of the process is the same as process 1.

Managing Security in SSIS

You can control the security of the SSIS Catalogue database. Please Right-click on the SSIS Catalogue database and click on properties. The catalogue properties dialogue box appears.

From here you can change different properties to manage the security of the Database.

The encryption level AES_256 is the default encryption level. Other encryption levels are as below.

• DES
• TRIPLE_DES
• TRIPLE_DES_3KEY
• DESX
• AES_128
• AES_192
• AES_256 (default)

The Following points about encryption need to be noted.

• The stronger the encryption, the more the CPU is used.
• Longer keys give better encryption than shorter keys.
• Long passwords are stronger than short passwords.

Scheduling an SSIS package

We have uploaded the developed package into the Catalogue database and also set the security and encryption level. Now it is time to schedule them so that they can run automatically. Over the next few steps, we will learn how to schedule an SSIS package.

Step 1

Right-click on jobs under SQL Server agents and click on new jobs.

Step 2

The following screen appears.

Provide the name of the job and a short description.

Steps 3

Select steps and insert the different steps that are required to execute the job automatically. Here you need to select the SSIS package name which you want to schedule.

Once the steps are set the final window looks like below

Step 4

Next, comes the setup on how frequently the application will run. Select the schedule tab and configures it accordingly.

Comparing SSIS 2005 R2 & 2008 & 2012 & 2014

Comparison between SSIS 2005 and 2008

SSIS 2005	SSIS 2008
Here users can write the scripts in VB only.	Here users can write the scripts in C# and VB.
DataProfilingTask is not in SSIS 2005.	DataProfilingTask is introduced in SSIS 2008.
In SSIS-2005 for Error Output look-ups had only the following 3 options. Fail Component Ignore Failure Re-direct row	SSIS -2008 added an additional feature “No match Out-Put” to the SSIS 2005
Cache Mode is not in SSIS 2OO5.	Cache Mode is introduced in SSIS 2OO8. 3-Different Cache Mode in SSIS 2008: FULL CACHE MODE PARTIAL CACHE MODE NO CACHE MODE.

Comparison between SSIS 2008 and 2012

SSIS 2008	SSIS 2012
No Undo And Redo feature in SSIS 2008	Undo And Redo feature available in SSIS 2012.
SSIS Parameters at package level	SSIS Parameters at the package level, task level and project level.
No DQS in SSIS 2008.	DQS Transformation is available in SSIS 2012.
Introduced in SSIS 2008. But there is no task to support CDC in SSIS 2008.	CDC ControlTask available to support CDC in SSIS 2012.

Features in SSIS 2014

• Better Deployment

1. Incremental package deployment
2. SSIS Catalog encryption

• Better debugging

1. SSIS Log reader
2. Column names for error in dataflow

• Better Package management

1. Improved experience for project upgrade
2. Autoadjustbuffersize feature
3. Reusable control flow templates

• Connectivity

1. Support for HADOOP,Excel
2. Connector support for SAP BW

• Usability

1. Better install experience.
2. Multiple designer improvement and bug fixes.

Conclusion

The above write-up gives the details on how you can set up a catalogue database, encrypt the database, upload an SSIS package and schedule it to run automatically. This write-up also gives a detailed comparison on different types of SSIS applications starting from 2005.Although this is not a detailed discussion but it gives a glimpse of every aspect of the SSIDB topic and would help the user to pursue further studies if required.

FaceBook Twitter Google+ LinkedIn Pinterest Email