question

NicoVerwer-6885 avatar image
0 Votes"
NicoVerwer-6885 asked AshokkGeetha-4773 answered

SSIS DTSX package canceled during validation phase

Since today, when I try to execute a package, or task within a package, I get the following output:

 SSIS package "C:\ssis-<package>.dtsx" starting.
 Information: 0x4004300A at Validate Tables, SSIS.Pipeline: Validation phase is beginning.
 SSIS package "C:\ssis-<package>.dtsx" finished: Canceled.
 The program '[15628] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).

That's all. Of course I have not pressed the stop button to terminate execution.
I run this in debug or release mode, using VS2019 and SSIS. The yellow spinner on the design panel keeps spinning, but nothing else happens.

This problem happens when one of the dataflow tasks I am executing contains custom components. However, the same custom components worked without any problem until today. I went back in my Git history to an earlier version, and the same problem occurs. (I did recompile the custom components and registered them with the gcautil.)

The problem happened before I updated SSIS projects to version 3.14, and still happens after updating.
Running VS2019 as administrator does not make any difference.

At this point, to me it looks like something changed outside of my SSIS project, which causes the problem. However, I have no idea where to look because SSIS does not provide much information about what is (not) happening.

If anyone can shed some light on this, that would be much appreciated.



If you read this because you have the same problem, please read beyond the answer that I accepted. MS only lets me accept one answer, but there is more information here that might help you solve this problem.

sql-server-integration-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello
Could you please let me know how you fixed the problem of execution not getting past the Validation phase? I am facing similar problem.
Thank you

0 Votes 0 ·
ZoeHui-MSFT avatar image
1 Vote"
ZoeHui-MSFT answered NicoVerwer-6885 edited

Hi @NicoVerwer-6885,

As you said that the new issue is VS2019 just won't open a new window for VSTA scripting.

Please refer this for more details to see if it could resolve the issue.

As SQL Server Integration Services Projects mentioned, sometimes this product or Visual Studio Tools for Applications 2019 may be somehow deleted during VS instance upgrade.

If VS doesn't pop up when clicking on "Edit Script", please try to repair VSTA 2019 via control panel.

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you @Zoehui-MSFT !
Repairing VSTA did indeed solve this problem, and VSTA works again for editing script components.

1 Vote 1 ·
AshokkGeetha-4773 avatar image
0 Votes"
AshokkGeetha-4773 answered

Hello @NicoVerwer-6885
Could you please let me know how you fixed the problem of execution not getting past the Validation phase? I am facing similar problem.
Thank you

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NicoVerwer-6885 avatar image
0 Votes"
NicoVerwer-6885 answered NicoVerwer-6885 edited

After a lot of digging, I found this thread, that refers to this one.
Apparently, my packages were executed in a 64-bit runtime instead of 32-bit runtime.
Why this suddenly happened I do not know, and I do not recall changing any settings related to 32 / 64 bits.

The packages that do not use custom components execute correctly now. It also works for packages with custom components. I am happy now, after almost two days of pulling out my hair.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

So setting the package to x86 allows it to run correctly then?

0 Votes 0 ·

I changed the value of the Run64BitRuntime to False. This probably has the same effect as setting the execution environment to x86. The second link in my comment above contains more information:

  • Project menu | <project> Properties | Configuration Properties | Debugging | Run64BitRuntime.

This is in VS2017 + SSDT. It may be in a different place in other versions of Visual Studio + SSDT.

0 Votes 0 ·
cooldadtx avatar image
1 Vote"
cooldadtx answered NicoVerwer-6885 edited

Turn on diagnostics logging for the package and run it again. This should show you a detailed log of the SSIS processing including the validation of each component. Using that you should be able to narrow down which component (if any) is failing. After that I think some debugging by stepping through will potentially indicate what is wrong since you said it fails in either mode.

As for the older vs newer package note that SSIS components are based upon versioned assemblies (for .NET components). These are stored in the GAC and the GAC can have any # of versions of an assembly. The SSIS package will use the exact version of the assembly it was built against. So if you were using v1 of a component for your package and it was working fine, you then upgraded your package and switched to v2 of the component then it'll use the newer version. If you later reverted your changes the package would be back to v1 of the component (which would likely never have been removed). This is what allows us to have different packages using different versions of the same component. So if the older version doesn't work anymore either then it seems like it isn't related to a component you rely on as the older version should just work.

Additionally updating to a newer component is not automatic when opening a package. Starting with SSIS 2012 (I believe) components can upgrade a package to a newer version when opened in the designer (note that components have a designer and runtime component) but only if they opt into that feature. If a component doesn't do that then you have to do it manually otherwise the component will remain on the older version. This is partially to avoid breaking things but is a pain if your components update frequently.

Given all this I believe the issue isn't with an upgraded component but rather a configuration on the server level or perhaps a server-level component like the framework but the logs will hopefully provide better information.

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you, @cooldadtx, for this answer. It is helpful to know more about how the GAC works, and also I was not aware of the logging options.

I turned on diagnostic logging. But no messages appear in the csv file that I have connected.
What is even weirder, is that with logging enabled, execution of a task is no longer canceled in the validation phase.

In the meantime, a new problem has popped up. I can no longer open the code for script components. VS2019 just won't open a new window for VSTA scripting.

I see three options now:

  • I could be doing something very wrong, which causes SSIS / VS / SSDT to misbehave.

  • Something on my development PC has changed which causes the strange behaviour.

  • I have a hardware problem that causes weird failures.

Of course I have rebooted multiple times. As I learn more, I will add to this issue.

0 Votes 0 ·

VS2019 doesn't support SSIS script tasks but the SQL Server Integration Services extension does. However that extension keeps breaking on newer VS updates. In all cases it seems to break when trying to edit script tasks. You should follow up on the extension to see if it is broken again. A recent Q&A post indicates 16.10.0 is still being tested. Previously you could revert back to the previous version of the extension but once you upgrade VS it is too late. Not sure what is going on with that extension because it used to be pretty solid. Seems each new VS update these days breaks it again.

1 Vote 1 ·

Thank you, @cooldadtx .
Updating VS2019 did indeed break a few things, apparently. VSTA works again, but I am still working on getting messages into the log-file.

To be continued...

0 Votes 0 ·

Today, with logging enabled, I get the 'Canceled' problem again.
Also, no log messages appear in the CSV file that I try to use for logging.

I am beginning to think that I should downgrade to VS2017 to have a stable SSIS development environment.
So I downgraded to VS2017, and my task is still canceled. Diagnostic logging does not produce anything.
The canceling now also happens on tasks that do not use any custom components, so I believe that that is not the problem.
Maybe something has changed in my SQL Server installation that causes the problem. That will be my next focus.

Although @cooldadtx 's answer is very helpful, my problem is still not solved, so I upvoted it, but did not accept it yet as an answer.

0 Votes 0 ·

Looking back at the original post again the validation was happening on Validate Tables. Is this one of your tasks? What is it doing?

If it is a script task then there is probably a bad reference to an assembly.
If it is another task then I wonder about the version of SSIS your package is targeting.

1 Vote 1 ·
Show more comments