0

I need to migrate several DTS packages as noted above. They're actually running fine when I run them via dtexec.exe on the command-line. But if I try to install and run them via SQL Server Agent - as they were previously and as such are supposed to - I get the bogus error message, that

C:\Windows\Temp\.NETFramework,Version=v4.0.AssemblyAttributes.vb

...couldn't be accessed. I say bogus because this (generated?) file is actually there and should be accessible since I'm running all of this logged into SQL Server via Windows Authentication as local administrator.

(Update: I should explicitly mention that I've tried creating and running these jobs off the SQL Server Integration Services as well as directly off the file system. Both methods end up with the same result described above.)

After trying to read up on the matter I got the impression that you migrate such packages correctly by installing Visual Studio / Data Tools / Integration Services, loading the DTS packages in there, inspecting and fixing any errors - i.e. rebuild the whole thing in the new IDE. So I set out to do that, and got a bunch of the following error messages for VisualBasic scripts within the DTS package:

The binary code for the script is not found. Please open the script in the designer (...)

I did that, and was notified about a missing type:

BC30002 - Type 'Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute' is not defined. (...)

This seems really weird, because I'm able to open the type's sources in question in Visual Studio without problems.

Why does this happen and what can I do about this? Why do I need to bother with all of this if the DTS packages run fine with dtexec.exe on the command-line?

Exact server versions:

  • Microsoft SQL Server 2012 (SP4-GDR) (KB4583465) - 11.0.7507.2 (X64) Developer Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
  • Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)
zb226
  • 9,586
  • 6
  • 49
  • 79
  • Have you rebuilt/upgraded the packages for 2019? – Thom A Nov 09 '22 at 11:34
  • Also, why haven't you updated your 2019 instance yet? There are *multiple* bugs in the RTM version. – Thom A Nov 09 '22 at 11:34
  • @Larnu I'm trying to rebuild/upgrade these packages, that's the reason for this question. – zb226 Nov 09 '22 at 11:36
  • In truth, SSIS normally won't let you deploy the packages to SSISDB unless you've correctly upgraded the project, did you not get an error when you tried to deploy them? – Thom A Nov 09 '22 at 11:38
  • @Larnu: No I did not get an error. Deploying worked fine. I also tried running the packages directly off the file system which behaves exactly the same. – zb226 Nov 09 '22 at 11:40
  • Why are you using File System Deployment and not SSISDB? This sounds more like you *haven't* upgraded the package. Have you opened the project, changed the target service version, and rebuilt your project? – Thom A Nov 09 '22 at 11:42
  • Larnu I tried both ways and I'm stuck in the process of rebuilding. I can very much deploy the package without upgrading it. But it won't run. – zb226 Nov 09 '22 at 11:43
  • To restate/infer: You have SSIS packages with Script Components using Visual Basic built using SQL Server 2012 tooling and now you are attempting to migrate to 2019. The packages will execute fine from the command line but fail as SQL Agent Jobs. If that's accurate, 1) can you edit your question to show a sample of the command line execution + the SQL Agent job definition? 2) Can you run `dtexec /?` on the 2019 server where the package execution works fine and show line 2 of output? 3) When you deployed to 2019 - how did that happen and where did the packages go? – billinkc Nov 09 '22 at 14:41
  • When installing a new instance of SQL Server, you’ll have the option to install SQL Server Integration Services, and this option should be selected for any instance that will store and run SSIS packages. However, doing so will not create the SSIS catalog.https://www.timmitchell.net/post/2017/07/03/creating-the-ssis-catalog/ – Hao Yu-MSFT Nov 23 '22 at 07:04

1 Answers1

0

Thanks for everybody's suggestions and sorry for the long period of silence. What I finally did to come by this problem (after trying out lots of other stuff) was suggested in an answer to a related SO question.

It sounds pretty crude - Open the .dtsx file in a text editor and remove any section defining the target framework version manually:

<PropertyGroup>
    <TargetFrameworkVersion>v4.0</TargetFrameworkVersion>
</PropertyGroup>

If I understand it correctly, this is simply a reference to the version of the targeted .NET framework. If it is removed, once you open and save it in the newer Visual Studio 2019, it will fill in it's own defaults:

<PropertyGroup>
    (...)
    <TargetFrameworkVersion>v4.7</TargetFrameworkVersion>
    (...)
</PropertyGroup>

The (...) denote that Visual Studio 2019 will add other properties which were not there before.

You'd think this should be possible to do via the project configuration, but it my case, it was not.

zb226
  • 9,586
  • 6
  • 49
  • 79