Friday, January 8, 2016

Creating and Executing a SSIS Project Package - Part 4

This post is about deploying a SSIS package using SQL Server Data Tools.
Before you start on this post review the following:

http://hodentekmsss.blogspot.com/2015/12/creating-and-executing-ssis-project.html
http://hodentekmsss.blogspot.com/2015/12/creating-and-executing-ssis-project_28.html
http://hodentekmsss.blogspot.com/2015/12/creating-and-executing-ssis-project_29.html

There are two modes deployment SSIS 2012:
  • A mode to deploy individual packages
  • A mode to deploy SSIS projects
SSIS Project deployment is the default for SSIS 2012. Verify that the project you created earlier has no problems. For this, bring up the project in Visual Studio and execute the package as shown in Part 3.

In Solution Explorer right click the project and select Deploy Package as shown:


deploy_02

The wizard Integration Services Deployment Wizard gets displayed. There are a couple of steps (5) you need to follow. This wizard will deploy the package to an Integration Services Catalog on an instance of SQL Server 2012.


deploy_03

Click Next. You need to select the destination.


deploy_04

Click in the box under Server name: and click Browse...

Browse for Servers window is displayed as shown displaying the instance name.


deploy_04a

Click OK to Browse for Servers window.  The Browse for Project window is displayed the SSISDB is the only object.


deploy_04b

SSISDB is organized as folders and the Projects are housed in the folders.
Click on New folder...button. The Create New Folder window is displayed as shown.


deploy_04c

Provide a name and a description(Optional) and click OK.

The Browse for Project now contains the folder you created, SSIS_Dec27Pkg.


deploy_04d

Click New project...The Create New Project window us displayed.


deploy_04e

Provide a name and a description and click OK. The Browse for project window now displays the Project (All these names I have given were taken from my SSIS project created in Parts 1, 2 and 3).

deploy_04f

Now click OK. Now the Select Destination page of the wizard has all the needed information as shown.
deploy_04g

Click Next. The Review you selections section of the Review page is displayed.

deploy_05

Just check the Source and Destination paths and click Deploy.

After some processing the results are displayed in the Result page of the wizard as shown.

deploy_06

Looks like the deployment failed for some reason. Clicking the link Failed brings up the next window.
deploy_07FailedLink.png

The message appears to indicate failing to find a stored procedure. Searching for the package on the SQL Server instance using PowerShell did indicate the creation of folders and projects but did not find the package.
deploy_08.png

On a hunch, the SQL Server Data Tools was closed and opened with administrative privileges and
the package was deployed to the same folder in the SQL Server.

This time the deployment was successful with any errors.


deploy_09.png

The next image shows the package in the Integration Services Catalogs in SQL Server Management Studio.

deploy_10.png

For getting a jump start on SSIS review my popular book and describes some 20 routine tasks that you can configure. The version may be different but the procedures have not changed much.


https://www.packtpub.com/networking-and-servers/beginners-guide-sql-server-integration-services-using-visual-studio-2005