Deploying an SSIS Project (using an .ispac project deployment file)

New to SSIS 2012? You may find the below info helpful in your journey through SSIS 2012 (which has seen some major changes from earlier versions).

This is the Second¬†in the series of articles that I plan to cover over the next couple of months on how we can deploy and execute SSIS 2012 Projects. I will try my best to churn out one article every week or two, fingers-crossed ūüôā

Deploying an SSIS Project (using an .ispac project deployment file):

SSIS 2012 supports two deployment models:

  1. Project Deployment Model (newly introduced in SSIS 2012)
  2. Package Deployment Model (Pre РSSIS 2012 versions). 

The Project Deployment Model helps us to deploy our projects to the Integration Services server.

Today, we will discuss on how to deploy an SSIS Project that was created using the Project Deployment Model.

Step 1. Open the .ispac project deployment file using the SQL Services Integration Services Deployment Wizard. E.g., Open the Data Migration .ispac file as shown in the below screenshot.

SSIS-ispac file

SSIS-ispac file-OpenWith

Step 2. The Integration Services Deployment Wizard opens up to the Introduction page.
Click Next in the Integration Services Deployment Wizard.

SSIS-ISDeployment Wizard-Intro

Note: Sometimes, you may get a failed to decrypt warning during the validating Project deployment file phase. This may occur for a variety of reasons such as the user account that is used to run the package may differ from the original package author, or the user account may not have the required permissions to either make connections or to access resources outside the SSIS package, etc.

SSIS-ISDeploymentWizard-warning

 

The most appropriate method depends on the environment and the reason that the package failed.

Some of the common methods to resolve this are as below:

Method 1: Use a SQL Server Agent proxy account
Method 2: Set the SSIS Package ProtectionLevel property to ServerStorage
Method 3: Set the SSIS Package ProtectionLevel property to EncryptSensitiveWithPassword
Method 4: Use SSIS Package configuration files
Method 5: Create a package template

More details about this can be found at: http://support.microsoft.com/kb/918760

Additional Link: http://www.databasejournal.com/features/mssql/ssis-2012-implementing-package-security-using-access-control.html

For now, I will click OK and continue the deployment.

Step 3. Select the Integration Services Project that you want to deploy. Here, since we are deploying from the ispac file, select Project deployment file as the source.

SSIS-ISDeployment Wizard-Select_Source

 

Step 4. Enter the destination server details like Server name and Path as shown in the below screenshots. Click the Browse button to select the Server name and Path. Click Next to go to the next screen.

SSIS-ISDeployment Wizard-SelectDestination

SSIS-ISDeploymentWizard-SelectDestination-Server

SSIS-ISDeploymentWizard-SelectPath

SSIS-ISDeployment Wizard-SelectDestination-Server-Path

 

Step 5. Once the destination server name and path is provided, click Next.
You will be taken to the Review screen (as in the below screenshot). Click Deploy button.

SSIS-ISDeploymentWizard-Review

 

Step 6. Upon successful deployment, you will see the below screen.

SSIS-ISDeploymentWizard-Results

 

Step 7. Go to SQL Server Management Studio and Refresh the Staging1 folder.
You can see that the Project has deployed successfully and you can view all the packages of the project.

image030      image031 image032

 

Even though we have deployed the project, we are still not ready to execute it. Because the project most likely uses some Project level parameters for which correct values need to be supplied (rather than the values used in development) and hence we still need to bind it to our required environment(s).

In the next article, we will look at Creating Environments for Customized Executions.

 

 

 

Creating SSIS Catalog

New to SSIS 2012? You may find the below info helpful in your journey through SSIS 2012 (which has seen some major changes from earlier versions).

This is the first in the series of articles that I plan to cover over the next couple of months on how we can deploy SSIS 2012 Projects and also how to execute¬†them. I will try my best to churn out one¬†article every¬†week or two, fingers-crossed ūüôā

This article would explain in detail about Creating the SSIS Catalog. (The next article would focus on the types of deployment in SSIS 2012 and also provide detailed steps on how to deploy an SSIS project to SQL Server).

Creating the SSIS Catalog:

Note: Use Windows Authentication (and not SQL Server Authentication as we cannot Create Catalog under SQL Server Authentication)

Step 1.  Once you are logged into SQL Server Management Studio, Connect to the Database Engine as shown below.
Provide the Server name and set the Authentication to Windows Authentication

SSIS-ObjectExplorer         SSIS-ConnectToSQLServer

 

Step 2.  Once logged in, right-click Integration Services Catalogs and select Create Catalog.

SSIS-CreateCatalog

 

Step 3.¬† Select ‚ÄėEnable CLR Integration‚Äô checkbox and provide a password for the Catalog.

Note:  Make sure to keep this password at a secure place because if you migrate and move the Integration services catalog to another SQL Server Instance, you can restore the key to regain access to encrypted content.

SSIS-CreateCatalog-EnableCatalog

 

Step 4.  You can now see that a new SSISDB Catalog is created under Integration Services Catalogs.

SSIS-SSISDB

 

Step 5.  Right-Click SSISDB and select Create Folder.

 SSIS-SSISDB-CreateFolder

 

Step 6.  Provide a Folder Name in the Create Folder dialog box. Optionally, enter a Folder description.

 SSIS-SSISDB-CreateFolder-Name

 

Step 7.  Once done, you can see that a new folder by the given name is created under SSISDB.

This newly created folder has 2 sub-folders by default: Projects & Environments (which I will explain later in another article).

SSIS-SSISDB-FolderName

¬†That’s it. Now you have created the SSIS Catalog where you can deploy your projects.

In my next article, I will provide detailed steps to deploy your SSIS project using the newly introduced Project Deployment Model (in SSIS 2012). Till then, stay tuned… ūüôā