Featured image test tube drawing attribution: Test Vectors by Vecteezy.

How do I test new SQL Server Integration Services (SSIS) extensions and updates to Visual Studio?

I have a collection of virtual servers that run locally on my laptop. SSIS extensions for Visual Studio 2019 and Visual Studio 2022 are now separate downloads (see the links provided).

Testing SSIS Extensions

Test 0

My first test (Test 0) is to create a new SSIS project:

I name the project and select a target folder:

I click the Create button to create the new SSIS project.

Next, I rename the default SSIS package file in Solution Explorer:

Renaming the package in Solution Explorer renames the file, which I verify by checking File Explorer:

I then check to make sure the package object is renamed by comparing the file name I just changed to the name on the SSIS package tab:

I also validate the Name property of the SSIS package by clicking anywhere on the Control Flow and pressing the F4 key to open the package Properties, and then verifying the Name property:

If the filename, package tab, and the Name property reflect the new package name from Solution Explorer, this test is successful. If not, I add a comment on the appropriate SSIS extension version page to let the team know this test fails. In my experience, the Microsoft SSIS Team is responsive to bugs reported in this fashion.

Test 1

The next test is to test-execute the SSIS package in Debug. Perform this test by pressing the F5 key, clicking the Start button, or by clicking Debug–>Start Debugging:

The result I seek is found at the bottom of the IDE (Integrated Development Environment, a description of Visual Studio):

Test 2

I next inspect the SSIS toolbox. If the SSIS toolbox is not open, it may be opened by clicking the toolbox icon found in the upper-right of the package designer:

Once the toolbox is open, I check for anomalies. For example, the Execute SQL Task appears in the “Other Tasks” category:

In earlier versions, the Execute SQL Task is listed in the “Favorites” category along with the Data Flow Task. Is this a bug, though? I do not think so, but I will make the team aware of this change because it may confuse students just learning SSIS.

I can easily move the Execute SQL Task to the Favorites category by right-clicking the task and then clicking “Move to Favorites”:

The Execute SQL Task is moved to Favorites:

Test 3

The next test covers three common tasks:

1. Data Flow Task
2. Execute SQL Task
3. Script Task

A prerequisite for this test is at least one database from which I may read and to which I may write. I use an edition-appropriate version of the AdventureWorks sample database, available here:

I can hear some of you asking, “Where do I get SQL Server, Andy?” I wrote a couple series about acquiring and installing SQL Server 2019 and SQL Server 2022 Developer Editions. Check out those links for more information. Please note: they are free!

Back to Test 3…

Follow the instructions to setup an AdventureWorks database on an instance of SQL Server that is available to you.

Return to SSIS.

Drag an Execute SQL Task onto the Control Flow surface:

Double-click the Execute SQL Task to open the editor. Click the dropdown for the Connection property and then click “<New connection…>”:

When the “Configure OLE DB Connection Manager “dialog displays, click the New button:

When the Connection Manager dialog displays:

1. Enter the name of your SQL Server instance
2. Click the dropdown for the “Select or enter a database name” property
3. Click the database name to which you wish to connect
4. Click the OK button:

The workflow returns to the “Configure OLE DB Connection Manager” dialog. Click the OK button to proceed:

Click the OK button to return to the Execute SQL Task editor.

Change the Name property to “SQL Truncate Target”. Prefixing the name of the Execute SQL Task with “SQL” is an example of using an SSIS naming convention. An example of an SSIS Naming Convention may be found at SSIS.tips.

Click the ellipsis beside the SQLStatement property to open the “Enter SQL Query” dialog:

Enter the T-SQL statement “Truncate Table <target table name>” (my target table is named dbo.Person):

Click the OK button to close the “Enter SQL Query” dialog, and then click the OK button to close the Execute SQL Task editor.

Drag a Data Flow Task onto the Control Flow surface. Click the “SQL Truncate Target” Execute SQL Task to surface the OnSuccess precedence constraint (green arrow). Click the OnSuccess precedence constraint and drag your mouse over the Data Flow Task, and then release the mouse to connect the OnSuccess precedence constraint from the “SQL Truncate Target” Execute SQL Task to the Data Flow Task:

Double-click the Data Flow Task to open the Data Flow tab, which serves as the Data Flow Task editor.

Once open, expand the “Other Sources” category on the SSIS Toolbox (which now contains Data Flow Task components). Drag an OLE DB Source onto the Data Flow surface:

Double-click the OLE DB Source to open the OLE DB Source editor.

Ignore the “OLE DB connection manager” property value. the very next step is to update the connection manager by creating a new connection manager.

Click the New button to open the “Configure OLE DB Connection Manager” dialog:

When the “Configure OLE DB Connection Manager” dialog displays, click the New button to open the Connection Manager dialog:

When the Connection Manager dialog opens, connect to the instance of SQL Server that contains your AdventureWorks sample database. My SQL Server instance is named “vDemo22\Dev” and my AdventureWorks sample database is named “AdventureWorks2022”:

After configuring the Connection Manager, click the OK button to return to the “Configure OLE DB Connection Manager”:

Click the OK button to return to the OLE DB Source editor.

Click the “Data access mode” dropdown, and then select “SQL command”:

After selecting SQL command data access mode, enter a SELECT statement in the “SQL command text” property.

I used the following query to return all the data in the Person.Person table:

Select
BusinessEntityID
, PersonType
, NameStyle
, Title
, FirstName
, MiddleName
, LastName
, Suffix
, EmailPromotion
, AdditionalContactInfo
, Demographics
, rowguid
, ModifiedDate
From Person.Person

Click the Preview button to test the OLE DB Source configuration. If configured correctly, data should be returned:

Click the “Close” button to close the Preview Query Results dialog.

Click the OK button to close the OLE DB Source editor.

Right-click the OLE DB Source and then click “Rename”:

Using the SSIS.tips Naming Convention for Data Flow Sources, I use the prefix “OLEDBSrc” followed by “Person_Person” (because periods are not allowed in component names):

In the SSIS Toolbox, expand the “Other Destinations” category and then drag an OLE DB Destination onto the Data Flow Task surface. Click the “OLEDBSrc Person_Person” OLE DB Source and drag a data flow path (blue arrow) from the “OLEDBSrc Person_Person” OLE DB Source to the new OLE DB Destination:

Double-click the OLE DB Destination to open the OLE DB Destination editor. Click the “OLE DB connection manager” property dropdown and select the target connection manager (mine is “vDemo22\Dev.TestDB”:

Click the OK button to close the “OLE DB connection manager” property dropdown.

Click the “Name of the table or view” property dropdown and select the name of your target table (mine is named “dbo.Person):

Click the Mappings page and, if necessary, perform necessary column mappings. In my example, all columns are auto-mapped because the target table (dbo.Person) was built from the T-SQL script for the source table (Person.Person), with minor modifications:

Click the OK button to close the “OLE DB Destination” editor.

Right-click the “OLE DB Destination” and then click Rename:

I choose to rename the OLE DB Destination to “OLEDBDest dbo_Person”:

Return to the Control Flow. Drag a Script Task onto the Control Flow surface and connect an OnSuccess precedence constraint from the Data Flow Task to the new Script Task. Finally, rename the new Script Task “SCR Log”:

Double-click the “SCR Log” Script Task to open the Script task editor. When the Script Task editor displays, click the ellipsis beside the ReadOnlyVariables property:

When the Select Variables dialog displays, select the System::PackageName and System::TaskName variables:

Click the OK button to return to the Script Task editor.

Click the “Edit Script…” button to open the Visual Studio Tools for Application (VSTA) editor:

Please note: I am using Microsoft Visual C# 2022 as the Script Language.

Once the Visual Studio Tools for Application (VSTA) editor opens, scroll to the Main method:

Enter (or copy and paste) the following code between the comment “//TODO: Add your code here” and the line “Dts.TaskResult = (int)ScriptResults.Success;”:

string packageName = Dts.Variables["System::PackageName"].Value.ToString();
string taskName = Dts.Variables["TaskName"].Value.ToString();
string subComponent = packageName + "." + taskName;
int informationCode = 1001;
bool fireAgain = true;

string description = "Wrapping up " + packageName + ".dtsx execution";

Dts.Events.FireInformation(informationCode, subComponent, description, "", 0, ref fireAgain);

Click the “Window Close” (X) button to close the VSTA editor:

Please note: Closing the VSTA editor saves the code.

Click the OK button to close the Script Task editor.

Time to Test!

Click the Start button to begin a Debug (test) execution:

Once the Debug execution begins, Control Flow tasks start executing:

Debug execution continues until all Control Flow tasks complete execution:

When this test execution completes, click the Progress tab (or “Execution Results” tab, if you stopped the debugger operation). I search for the highlighted messages below:

I want to see the number of rows written by the “OLEDBDest dbo_Person” OLE DB Destination in the Data Flow Task.
I want to see the Information message built by the Script Task.
I want to see the Execute SQL Task’s “100 percent complete” Progress message.

Conclusion

This is not an exhaustive test and this is not all of the testing I do. My intent in writing this post is to share some of the testing I conduct on new and updated SSIS extensions (I also run these tests on updates to Visual Studio). My hope is this will inspire you, Dear Reader, to think about how you might test SSIS functionality that is important to you and your enterprise.

While this is a long-ish blog post and, if you are new to SSIS, probably a lot of work; in real life executing these tests and creating the test package takes me about 15 minutes.

I hope this helps!

:{>