January 17, 2022 at 3:01 am
Hi
I am using SQL Server 2019 and Visual Studio 2019.
There are two components - Power Query Source and OLE DB Database. I went to the connection manager and tested each connection. The system was able to connect successfully for both components. These "look" ok.
I am getting below error when I try to run the package in Visual Studio. I am new to SSIS so I am not sure how to fix it. How can I fix it?
ERROR MESSAGE:
SSIS package "C:\Users\...\Package.dtsx" starting.
Error: 0xC004801F at Data Flow Task, SSIS.Pipeline: The component metadata for "Power Query Source, clsid {7CDF593F-DE06-4ABD-B356-7976EF7AC8E0}" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC001000E at Package: The connection "{93FCED95-1D22-4CE3-9F3A-55D4485937FE}" is not found. This error is thrown by Connections collection when the specific connection element is not found.
Error: 0xC004800B at Data Flow Task, SSIS.Pipeline: Cannot find the connection manager with ID "{93FCED95-1D22-4CE3-9F3A-55D4485937FE}" in the connection manager collection due to error code 0xC0010009. That connection manager is needed by "Power Query Source.Connections[Web Connection]" in the connection manager collection of "Power Query Source". Verify that a connection manager in the connection manager collection, Connections, has been created with that ID.
Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: Power Query Source failed validation and returned error code 0xC004800B.
Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS package "C:\Users\...\Package.dtsx" finished: Failure.
The program '[17432] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).
January 17, 2022 at 6:06 pm
I am not sure if that is 1 error or 2, but I would try to solve the first error first:
The component metadata for "Power Query Source, clsid {7CDF593F-DE06-4ABD-B356-7976EF7AC8E0}" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
To me, that looks like the SSIS package was created for an older version of Visual Studio and/or SSDT and the Power Query Source is busted. I would first look at the UI side of things in the SSIS package for any red X's or yellow triangles and resolve those. Chances are you just need to open the Power Query Source and make sure things look good, then click on OK and the error/warning should go away. that is the solution to MOST metadata errors. In the event that doesn't fix that error, I would remove the Power Query Source and re-create it and see if that error goes away.
Once that error is fixed, I would look at fixing the second - The connection "{93FCED95-1D22-4CE3-9F3A-55D4485937FE}" is not found. This error is thrown by Connections collection when the specific connection element is not found. That one MAY be related to the first and solving it may make that error go away, or it could be unrelated and it MAY be a problem with your connection manager. IF fixing the first problem doesn't solve the second problem, I would try removing and re-adding the connection and then re-mapping all objects that used that connection manager back to the one you just created.
Now, depending on how complex that SSIS package is, it MAY be easier to start fresh as it could also be something busted in the SSIS package XML file and modifying that by hand is error prone. As you can open the SSIS package, re-createing it from scratch shouldn't be too hard. I've had the XML get corrupted when the SSIS package is a little complex, has had a lot of changes since conception, and has been updated from an older version or format stored on the server (ie SSIS Package vs SSIS Catalog).
The absolute first step though is to take a backup. Don't want to lose current state even if current state is broken. Next step, I'd grab a known good copy and make sure your known good copy still works.
If you exported it directly from SSMS to disk, make sure you were using the same version of SSMS as the SSIS instance is (ie if it is SSIS 2012, use SSMS 2012) or you can have weird behavior. Same thing when uploading it into SSIS IF you use SSMS to push it up to the SSIS server.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 18, 2022 at 1:27 am
Thanks for the reply. I took a look at version. Here is what I have installed. I have VS 2019 and SQL Server 2019. Does below show any version incompatibilities?
Microsoft Visual Studio Community 2019
Version 16.11.8
VisualStudio.16.Release/16.11.8+32002.261
Microsoft .NET Framework
Version 4.8.04084
Installed Version: Community
Microsoft Visual Studio Tools for Applications 2019 00435-60000-00000-AA039
Microsoft Visual Studio Tools for Applications 2019
Azure App Service Tools v3.0.0 16.11.94.52318
Azure App Service Tools v3.0.0
C# Tools 3.11.0-4.21403.6+ae1fff344d46976624e68ae17164e0607ab68b10
C# components used in the IDE. Depending on your project type and settings, a different version of the compiler may be used.
Common Azure Tools 1.10
Provides common services for use by Azure Mobile Services and Microsoft Azure Tools.
NuGet Package Manager 5.11.0
NuGet Package Manager in Visual Studio. For more information about NuGet, visit https://docs.nuget.org/
ProjectServicesPackage Extension 1.0
ProjectServicesPackage Visual Studio Extension Detailed Info
SQL Server Data Tools 16.0.62111.25150
Microsoft SQL Server Data Tools
SQL Server Integration Services 15.0.2000.170
Microsoft SQL Server Integration Services Designer
Version 15.0.2000.170
Visual Basic Tools 3.11.0-4.21403.6+ae1fff344d46976624e68ae17164e0607ab68b10
Visual Basic components used in the IDE. Depending on your project type and settings, a different version of the compiler may be used.
Visual Studio Code Debug Adapter Host Package 1.0
Interop layer for hosting Visual Studio Code debug adapters in Visual Studio
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply