August 30, 2024 at 10:36 am
I posted this over on Stack Overflow and got bupkis and was hoping someone over here would be able to help me.
I have an SSIS package that has been tested, and ran in Visual Studio 2019. It was originally created, tested, and ran in VS 2017 but when I started having the issue I figured I would make sure it was working fine in 2019. It was running fine on a server(Windows Server 2019) for a while now and we are working to migrate it to a newer server(Windows Server 2022). On the new server I am getting the following error:
Error: 2024-08-27 11:50:10.94 Code: 0xC00490F7 Source: ConvertData ConvertData (GetExpirationDate [51]) Description: The managed pipeline component "DTSTransform.DerivedColumn.7" could not be loaded. The exception was: Could not load type 'DTSTransform.DerivedColumn.7' from assembly 'Microsoft.SqlServer.PipelineHost, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'.. End Error Error: 2024-08-27 11:50:10.94 Code: 0xC0000036 Source: ConvertData ConvertData (SSIS.Pipeline) Description: Failed to create COM Component Categories Manager due to error 0x80131600 "". End Error Error: 2024-08-27 11:50:10.94 Code: 0xC0048021 Source: ConvertData ConvertData (GetExpirationDate [51]) Description: The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "Derived Column;Microsoft Corporation; Microsoft SQL Server; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0". End Error Error: 2024-08-27 11:50:10.94 Code: 0xC004801F Source: ConvertData ConvertData (SSIS.Pipeline) Description: The component metadata for "GetExpirationDate" could not be upgraded to the newer version of the component. The PerformUpgrade method failed. End Error
I thought maybe it was using some other version on the other server so I went and looked in the C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.PipelineHost folder on the server it was working and the only dll in there is for version 15. The new server does have version 15 and version 16 in there. I am not sure if that makes any difference or not though. I will also point out that the development box I built and ran this on had versions 11-14 but not 15 or 16. Once again, not sure if that makes any difference but just wanted to mention it.
Can anyone point me in the right direction on this? Any other information you might need to help solve this?
August 30, 2024 at 10:49 am
Are you editing the SSIS project in VS2022?
Have you set TargetServerVersion to SQL Server 2022 in the project's properties?
Have you taken the 'Upgrade All Packages' option to make sure they're ready to be deployed to 2022?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 30, 2024 at 12:29 pm
No, just VS2019. Just because the Server is 2022 I shouldn't have to build it in VS2022 should I? I mean, I had originally built it in VS2017 and was running it on a 2019 Server. I really don't think the Server version has much play in it does it? I just mentioned it as to try and give all the information I have. The error seems to be specific to the version of a dll/assembly. I have that dll installed on the Server and it seems to be finding it. It just says a component isn't available within that dll/assembly. But that seems to be the only version of dll/assembly I am running on the other server where it is working just fine.
But I am definitely not a dll/assembly expert. I may be even looking in the wrong folder for the version it is using.
August 30, 2024 at 1:05 pm
I don't know how much it matters, because I always make sure my version of VS is at least as high as the version of SQL Server I am deploying to.
But the fact that there is a 'TargetServerVersion' property at the SSIS project level suggests that it does matter, does it not?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 30, 2024 at 1:35 pm
Sorry, I guess I was talking about the Windows OS. The TargetServerVersion is the SQL version. I actually have SQL 2019 installed on both boxes. But I do also have 2022 installed on the newer box too.
I believe those correspond with the versions of the DLL/assembly files I am seeing in that folder.
August 30, 2024 at 3:20 pm
I just wanted to point out that it also depends on how you deploy.
If you deploy using SSMS, the SSMS version MUST be the same as the SSIS version. I've pushed out new SSIS packages that work fine in VS (2017, 2019, and/or 2022) from a mis-matched SSMS and it deploys successfully but fails to run. I am not sure if it applies to the "newer" SSMS's that aren't bundled with SQL Server (19 and 20), but with 2017 and older, I always have to match the SSIS instance to the SSMS version or it will fail to run after I deploy.
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.
August 30, 2024 at 3:53 pm
main thing is not to use native SSMS to deploy - either use VS deploy or T-SQL
August 30, 2024 at 4:16 pm
main thing is not to use native SSMS to deploy - either use VS deploy or T-SQL
The risk with a VS deploy though is if you have multiple SSIS packages in your project. The way we designed a bunch of our stuff is related SSIS packages exist in a single package. If we make change to a single SSIS package, we often don't want to push EVERYTHING out - we only want to push out the ONE thing that was changed. The reason being the last modified date will remain static this way.
I didn't know you could deploy using TSQL. Learned another new thing today!
When you deploy via TSQL, does that keep the version information or does it muck things up like deploying using the GUI?
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.
August 30, 2024 at 4:50 pm
it does keep version information.
code below from documentation - https://learn.microsoft.com/en-us/sql/integration-services/packages/deploy-integration-services-ssis-projects-and-packages?view=sql-server-ver16
other ways of doing it - including ci/cd pipeline - the one below just requires a share accessible by the server (on my case we have a deployment folder on the server itself where we dump the ispac)
DECLARE @ProjectBinary as varbinary(max)
DECLARE @operation_id as bigint
Set @ProjectBinary = (SELECT * FROM OPENROWSET(BULK 'C:\MyProjects\ SSISPackage_ProjectDeployment.ispac', SINGLE_BLOB) as BinaryData)
Exec catalog.deploy_project
@folder_name = 'SSIS Packages'
, @project_name = 'DeployViaStoredProc_SSIS'
, @Project_Stream = @ProjectBinary
, @operation_id = @operation_id out
August 30, 2024 at 7:26 pm
Just for some clarification here, the servers we are(and will be) running this on only have the components installed from SQL Server to run the SSIS packages. These servers are execution agents for a scheduling software. They run all sorts of different jobs. I built the SSIS package on the development box and then take the files out to a network share and simply run the dtsx file from a command line using dtexec.exe. Not sure if that makes any difference but just thought I would share additional information.
August 30, 2024 at 7:56 pm
I hope that server is fully licensed for SQL Server - whatever version you have installed.
running filesystem packages with dtexec directly MAY require the packages to be fully "upgraded" to the version of SQL installed - sometimes they aren't upgraded on the fly, specially if they have c# scripts.
so you need to ensure you do have the solution containing the packages targeting SQL 2019 to ensure they work on that server.
September 3, 2024 at 12:44 pm
The other server, it is setup and running fine on, is setup the exact same way. Everything is licensed properly. The only differences that I am aware of between this server and the other server is one is Server 2019 vs Server 2022(OS not SQL). And they both have the SQL Server components for 2019 but the newer server also has the components for SQL Server 2022. I am executing on both using the DTExec from the SQL Server 2019 folder and not the 2022 folder.
I am going to installing VS 2022 on my development box and try running it on that version and see if that changes anything. I am just waiting on my IT department to add some space to my drive on that dev box. It was already low and the VS install was going to put it over the top.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply