SSIS 2016: Building and Deploying a Simple Test Project

All software is tested. Some, intentionally. – Andy, circa 2010

I test software a lot. Why? Because all software is tested. Some software is tested intentionally. Some is tested by your soon-to-be-former-largest-customer.

In this post I will show you how to build and deploy a very simple SSIS 2016 project and deploy it. Cool? Let’s get started!

Open SQL Server Data Tools or Visual Studio 2015. I have a copy of Visual Studio 2015 Enterprise installed on my Windows Server 2016 Preview 5 virtual machine (named vmWin16_5). To open it, click the Window (Start) button in the lower left corner and type Visual Studio:

Click Visual Studio 2015 to open the Integration Development Environment (IDE):

If the Start Page doesn’t appear as shown above, you can click View—>Start Page to open it:

The Start Page is a good place to start (pun intended). You can open recent projects, open non-recent projects, and create new projects. We’re going to create a new project. To do that, click the New Project… link on the Start Page:

When the New Project window displays, it will appear similar to that shown below. Your version may look different from my version pictured below, depending on which templates you have installed. Visual Studio is an IDE (Integrated Development Environment), as I mentioned earlier. Each product installs templates for developers to use, and the template we want to use – Integration Services Project – is found under Installed—>Templates—>Business Intelligence—>Integration Services (click to view larger image):

Type a name for the project in the Name textbox. Optionally, type or select (using the Browse button) a location for the Integration Services project:

When the project is created it will appear similar to that shown below:

Note Solution Explorer contains a single SSIS package in the SSIS Packages node, and it is named “Package.dtsx”:

You can test it in the debugger by clicking the Start button:

You can click Debug—>Start Debugging or press the F5 key:

Believe it or not, this empty SSIS package will execute:

Let’s add a Script Task. First, click the SSIS Toolbox icon in the upper right corner of the package surface:

Once the SSIS Toolbox opens, click a drag a Script Task onto the Control Flow surface:

Double-click the Script Task to open the editor:

When the Script Task Editor opens, it will appear similar to that shown here:

Let’s add a ReadOnlyVariable. Click the ellipsis in the ReadOnlyVariable value textbox:

When the Select Variables window displays, scroll until you find the System::PackageName variable. Check the checkbox for this variable:

When the System::PackageName variable has been selected, click the OK button to close the Select Variables window. The Script Task Editor now shows the variable has been added to the ReadOnlyVariables property:

Click the Edit Script button to open the Visual Studio Tools for Applications (VSTA) Script Editor:

The VSTA editor takes several seconds to open but when it does, it appears similar to that shown below (click to view an enlarged image):

Find the public void Main() method and replace the commented text “// TODO: Add your code here” with the code shown below:

string packageName = Dts.Variables["System::PackageName"].Value.ToString();
string msg = "I am " + packageName;
bool fireAgain = true;

Dts.Events.FireInformation(1001, packageName, msg, "", 0, ref fireAgain);

Close the VSTA editor and then click the OK button on the Script Task Editor to close it as well. Your Script Task should appear similar to that shown here:

Press F5 or click the Start button to execute the package in the SSIS debugger. It should succeed…

… and you should see the message “I am Package” on the Progress tab:

Deploy the Project

To deploy the SSIS Project, right-click the project name in Solution Explorer and click Deploy:

The Integration Services Deployment Wizard displays. If this is the first time you’ve deployed an SSIS package, the Introduction page will display first:

Click the Next button to select a Destination SSIS Catalog. Enter a SQL Server Instance that hosts an SSIS Catalog (SSIS 2016 Administration: Create the SSIS Catalog).

Click the Browse button beside the Path textbox to open the Browse for Folder or Project dialog:

When the Browse for Folder or Project dialog displays, select a Catalog folder to deploy the project:

You can learn more about the SSIS Catalog by reading Deployment and Execution – Level 18 of the Stairway to Integration Services.

When the Create New Folder window displays, enter a Name for the Catalog Folder and (optionally) a Description. I named my Catalog folder “Test” but you can name yours whatever you’d like:

Click the OK button to create the Catalog Folder and return to the Browse for Folder or Project dialog:

Click the OK button to select the newly-created Catalog folder and return to the Integration Services Deployment Wizard:

Click the Next button to proceed to the Review page:

Click the Deploy button to begin the SSIS project deployment:

Once the deployment is complete, click the Close button. View the deployment by opening SQL Server management Studio (SSMS):

Connect to your SQL Server instance that hosts the SSIS Catalog you used to deploy your SSIS project:

SSMS Object Explorer, once connected, will appear similar to that shown below:

Expand Integration Services Catalogs—>SSISDB—><Folder Name>—>Projects—>SSIS Test Project—>Packages. There’s your SSIS Package!

Conclusion

I hope this basic walk-through helps you get started using SSIS 2016, a free component of SQL Server 2016. Remember, you can get SQL Server 2016 Developer Edition for free!

:{>

Learn more: