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.

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

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.

 

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.

 

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.

 

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.

 

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

 

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.

     

 

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.