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:

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:


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.


Click Next. You need to select the destination.


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

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


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


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.


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

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


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


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).


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

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


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.


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

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.

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.


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


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.

PowerShell Pro Tools is a Visual Studio Community 2017 extension that you can try free

I see there is at least one extension, PowerShell Pro Tools that you can download. You can find it in the Visual Studio Community 2017...