SSIS package runs fine in VSTS 2019 but not in SQL Server 2017 via SQL Agent or DTEXEC

Question

I have a very basic SSIS package with one SQL Task. It works fine in Visual Studio 2019 but doesn't run in SSMS or via DTEXEC on the server.

I have my package set to EncrptSensitiveWithPassword. I have developed the package locally, with my machine user.

To deploy, I copied the DTSX onto the server and used a sysadmin account to set up a sql agent job to run it from the file system.

With my package target server set correctly to 2017 I get:

Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 13.0.4574.0 for 64-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 8:54:27 AM Error: 2019-06-27 08:54:27.23 Code: 0xC0010018 Source: Package_v2
Description: Error loading value DTS:ConnectionManagers xmlns:DTS="www.microsoft.com/SqlServer/Dts"DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[Target DB]" DTS:CreationName="OLEDB" DTS:DTSID="{7DB8E823-90A7-499C-85AF-8304FAC5E575}" DTS:ObjectName="Target DB"DTS:ObjectD" from node "DTS:ConnectionManagers". End Error Could not load package "F:\SSIS\Data Warehouse Import\Package_v2.dtsx" because of error 0xC0010014. Description: The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails. Source: Package_v2 Started: 8:54:27 AM Finished: 8:54:27 AM Elapsed: 0.079 seconds. The package could not be loaded. The step failed.

Changing the target SQL Server to 2016 or prior (incorrect) gives me a database connection timeout. Switching from 64 to 32 bit makes no difference (Both my machine and the server are 64-bit anyway).

Google has been pretty unhelpful. I am at my wits end.

Answer 1

With the 2016 release of the SSDT/Visual Studio tooling for BI projects, we have the ability to specify what version of SQL Server Integration Services packages the project should build. The default is to target the most recent version of SQL Server available.

When you deploy to the SSISDB, no checks are performed to verify the blob (ispac) we're stuffing into the database aligns with what the database can "speak." So, you built a project using the 2019 version of data tooling which assumes you're be deploying to a 2019 instance of sql server but you actually deploy to 2012 and hilarity ensues because the api for deployment is the same.

How can I tell?

Open your SSIS project and if looks like this, it's targeting whatever VS thinks is current. This being a 2017 Visual Studio, it thinks SQL Server 2017 is what we're targeting.

Right click on the Project (SO_Trash in my example) and select Properties

In this window, expand the Configuration properties, select the General section and there in the Deployment Target Version, change the value of TargetServerVersion from SQL Server 2017 to SQL Server 2016

After clicking OK and confirming to Visual Studio that you want to reload your project, you should see something like this.

My project name now indicates the targeted version in parenthesis, thus SO_Trash (SQL Server 2016).

With all of your project bits matching what the server expects, deploy and then try running your package (or click the Validate button in SSMS but no one does that)

Answer 2

I got this message when I thought I knew the encrypt SSIS Package password but I didn't. I checked with a colleague and he straightened me out. You would think the error message would be "invalid password" or it would ask for the password a second time but it doesn't here. Both VS 2017 ent and VS 2019 Pro showed this error message for an invalid ssis package password. It's nasty. It won't open the package and you think the package is so corrupted it won't open, but it's just the password.