1

Working on upgrading from SQL Server 2017 to SQL Server 2019. When using a desktop or another server running SQL Server 2017, SSMS v18.12.1 is used to import SSIS packages into MSDB on a remote server running SQL Server 2019, the SSIS package becomes corrupted. All "Analysis Services Processing Task" are replaced with DTS:ExecutableType="SSIS.ReplacementTask".

When I RDP to the SQL Server 2019 server and perform the Import into MSDB no Problem. A Desktop running SQL Server 2019 Developer edition with SSAS installed, has no problem Importing the SSIS Package into the remote MSDB on SQL Server v2019 server.

What changed with SQL Server 2019?

Results of working through some scenarios

DTUTIL v140 and v150 works on Importing and Exporting SSIS Package with SSAS Components. I saw no corruption. SQL Agent Job worked as expected.

SSMS v18.12.1 IMPORT of SSIS packages would give no error, but the SSIS package now has INVALID SSAS Component. SSMS v18.12.1 EXPORT of a working SSIS Package would replace the SSAS component with DTS:ExecutableType="SSIS.ReplacementTask".

Improve this question
3
  • When you import into MSDB with no problem under the RDP scenario, is that also done with SSMS v18.12.1? Does the 2017 SSIS package also live in the msdb? In the scenario where the package becomes corrupt, is UAC running on the machine? Is there any weirdness with regard to domains and accounts between 2017 and 2019 (e.g. corp1\user1 is now corp2\user2)?
    – billinkc
    Commented Oct 3, 2022 at 16:23
  • Yes I'm using SSMS v18.12.1 to import the SSIS Package.
    – David
    Commented Oct 6, 2022 at 14:05
  • Yes I'm using SSMS v18.12.1 to import the SSIS Package. The SSIS package lives in MSDB in the server. User Account Control (UAC) on the server is mostly disabled. Is enabled on switch to secure desktop and Virtualize file and Registry rights. On my desktop it is enabled for Virtualize file and Registry rights. The same domain and accounts are being used for both 2017 and 2019.
    – David
    Commented Oct 6, 2022 at 14:21

1 Answer 1

Reset to default
0

Let's figure out if this is an SSMS issue or something else. dtutil is the command line utility for interacting with SSIS things. The tooling is version specific and by default it will upgrade earlier packages to match the current version. It should be installed on machines with the SQL Server Integration Services Service installed or the SSDT bits for Visual Studio.

In your case, SQL Server 2019 aka 150 should have a

C:\Program Files (x86)\Microsoft SQL Server\150\DTS\Binn\dtutil.exe /sourceserver localhost /SQL "\Package" /copy file;.\Package_2019.dtsx

You'll see an informational message that the package is being upgraded to 2019.

For comparison's sake, I'd also download the same package with the 2017 tooling to have a control version of the package

C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\dtutil.exe /sourceserver localhost /SQL "\Package" /copy file;.\Package_2017.dtsx

I'd then open the Package_2019.dtsx with a text editor and see whether the Analysis Services tags are blank or as expected.

If they're blank, I'd compare to Package_2017 and see if it's similarly afflicted.

If they're populated, then it sure feels like it's specific to how SSMS is interpreting and serializing the package but we can tease this out further by then pushing the local packages to the remote server.

C:\Program Files (x86)\Microsoft SQL Server\150\DTS\Binn\dtutil.exe /file .\Package_2019.dtsx /destserver localhost /COPY SQL;"\Package_2019"
C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\dtutil.exe /file .\Package_2017.dtsx /destserver localhost /COPY SQL;"\Package_2017"

If any of that reproduced the error, then it seems like it's a core issue with the install of the tooling on the 2017 server.

Old blog post on extracting ssis pakcages from the msdb.

Improve this answer
1
  • DTUTIL v140 and v150 works on Importing and Exporting SSIS Package with SSAS Components. I saw no corruption. SQL Agent Job worked as expected. SSMS v18.12.1 IMPORT of SSIS packages would give no error, but the SSIS package now has INVALID SSAS Component. SSMS v18.12.1 EXPORT of a working SSIS Package would replace the SSAS component with DTS:ExecutableType="SSIS.ReplacementTask".
    – David
    Commented Oct 7, 2022 at 14:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.