THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast.

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:

Cat2016_1_0

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

Cat2016_1_1

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

Cat2016_1_2

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:

Cat2016_1_3

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

Cat2016_1_4

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:

Cat2016_1_5

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

Cat2016_1_6

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

Cat2016_1_7

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

Cat2016_1_8

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

Cat2016_1_9

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

Cat2016_1_10

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

Cat2016_1_11

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

Cat2016_1_12

Double-click the Script Task to open the editor:

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

Cat2016_1_13

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

Cat2016_1_14

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

Cat2016_1_15

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:

Cat2016_1_16

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

Cat2016_1_17

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

Cat2016_1_18

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

Cat2016_1_19

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:

Cat2016_20

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

Cat2016_1_21

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

Cat2016_1_22

Deploy the Project

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

Cat2016_1_23

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

Cat2016_1_24

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

Cat2016_1_25

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

Cat2016_1_26

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

Cat2016_1_27

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:

Cat2016_1_28

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

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

Cat2016_1_30

Click the Next button to proceed to the Review page:

Cat2016_1_31

Click the Deploy button to begin the SSIS project deployment:

Cat2016_1_32

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

Cat2016_1_33

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

Cat2016_1_34

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

Cat2016_1_35

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

Cat2016_1_36

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:

Related Training
IESSIS2: Immersion Event on Advanced SQL Server Integration Services

Related Posts
SSIS 2016 Administration: Create the SSIS Catalog
SQL Server 2016 Developer Edition is Free
Converting an SSIS 2014 Solution to SSIS 2016
Installing SQL Server 2016 Developer Edition, One Example
Deploying a Single Package First in SSIS 2016
Stairway to Integration Services

Related Books
SSIS Design Patterns

Help!
Enterprise Data & Analytics – We are here to help.

Published Monday, June 20, 2016 2:47 PM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

News

My Latest Book:

Community Awards



Friend of Red Gate

Contact Me

Archives

Privacy Statement