There I was…

Delivering ExpertSSIS training with Brent Ozar Unlimited (we’re going to present another ExpertSSIS class – stay tuned for more information). I scheduled the execution of an SSIS package that contains a Script Task via SQL Agent, executed the job, and boom:

“Excellent teaching moment,” I thought as I opened SSIS Catalog Overview and Messages reports in SSMS for the execution. The error is shown here:

Since we were covering Data Integration Lifecycle Management (DILM) in class – and since Brent encourages presenters to share tools, websites, and utilities that they build (and even sell)* – we also took a look at the error using the DILM Suite free and open-source Catalog Reports solution:

I cannot select text from the SSIS Catalog reporting reports built into SSMS. However, I can select the error text from Catalog Reports or from SQL Agent History:

The SSIS Catalog execution error is:

Script Task:Error: There was an exception while loading Script Task from XML: System.Exception: The Script Task “ST_f690ad8e3ea0454d92176ea4235a87e2” uses version 15.0 script that is not supported in this release of Integration Services. To run the package, use the Script Task to create a new VSTA script. In most cases, scripts are converted automatically to use a supported version, when you open a SQL Server Integration Services package in %SQL_PRODUCT_SHORT_NAME% Integration Services.
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.LoadFromXML(XmlElement elemProj, IDTSInfoEvents events)

The SQL Agent error is:

Executed as user: NT Service\SQLAgent$DEMO. Microsoft (R) SQL Server Execute Package Utility Version 13.0.1601.5 for 64-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 6:12:58 AM Package execution on IS Server failed. Execution ID: 20258, Execution Status:4. To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report Started: 6:12:58 AM Finished: 6:13:00 AM Elapsed: 2.125 seconds. The package execution failed. The step failed.

When Did this Happen?

I started seeing this error around the time I installed SSMS v17.2 on my VMs running SQL Server 2016 and SSIS 2016. I suspect the two events – the installation and the errors – may be related but I could be mistaken.

The fix is fairly straightforward: We need to inform SQL Server Data Tools One Designer that we’re using SQL Server 2016. To do that, right-click the SSIS project in Solution Explorer and click Properties. When the SSIS project Property Pages opens, expand the Configuration Properties node on the left and click the General node. Click the TargetServerVersion property dropdown to change the current TargetServerVersion property setting as shown here:

Change this setting to SQL Server 2016 and click the OK button:

You will be prompted:

Click the Yes button. You will be prompted again:

Click the Yes to All button.

You have now reset the TargetServerVersion property of the SSIS project to SQL Server 2016. I did this during the ExpertSSIS class and then redeployed the SSIS project. When I executed the SQL Agent job again, it succeeded:

I Consider This A Bug

In my opinion, installing SQL Server Management Studio should not make breaking changes to the defaults of SQL Server Data Tools so I consider this a bug. It’s a pretty easy bug to identify and fix, though, so I don’t expect the SSIS Development Team to halt current development and rush to fix it. I think someone should take a look at how the defaults of the One Designer TargetServerVersion property work, though.

* Please see A Note About Delivering Content For Brent Ozar

Keep Up: Join my mailing list.

Need Help Getting Started or Updating SSIS or Azure Data Factory in Your Enterprise?

Enterprise Data & Analytics specializes in training and helping enterprises modernize their data engineering by lifting and shifting SSIS from on-premises to the Azure-SSIS integration runtime in Azure Data Factory. Our experienced engineers grok enterprises of all sizes. We’ve done the hard work for large and challenging data engineering enterprises. We’ve earned our blood-, sweat-, and tear-stained t-shirts. Reach out. We can help.