Issue Description: Facing issue in setting up and running SSIS in debug mode in VS2015 that can recognize 64-Bit C# custom components in Data Tools Tool box. Created MyCustomComponent.dll by referencing below dlls. Took the dlls from 140\DTS\Binn Microsoft.SqlServer.DTSPipelineWrap.dll Microsoft.SqlServer.DTSRuntimeWrap.dll Microsoft.SqlServer.ManagedDTS.dll Microsoft.SqlServer.PipelineHost.dll Added the MycustomComponent.dll into GAC using C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.6.2 Tools\x64\gacutil.exe Then, copied to C:\Program Files\Microsoft SQL Server\140\DTS\PipelineComponents\ C:\Program Files (x86)\Microsoft SQL Server\140\DTS\PipelineComponents\ DEV Setup: Installations -> Visual Studio 2015 Update 2 SSDT for VS2015 17.4 Opened VS2015 SSIS Project in 64-bit mode => the Components under MyCustomComponent.dll is not being loaded into Data Tools tool set. We need the cusom components to be loaded into Data Tools tool set. Additional Info: Control Panel entries -> Microsoft Sql Server 2017 (64-bit) Microsoft SQL Server Data Tools - enu (14.0.61712.050) Microsoft SQL Server Data Tools - Visual Studio 2015 (13.0.3225.4) Microsoft SQL Server Data - Tier Application Framework (x86) (13.0.3225.4) Services.msc entries -> SQL Server Integration Services 13.0 SQL Server Integration Services 14.0 SERVER Setup: Same as local workstation, except that the server doesnt have VS2015 IDE Control Panel entries -> Microsoft Sql Server 2017 (64-bit) Microsoft SQL Server Data - Tier Application Framework (x86) (13.0.3225.4) Services.msc entries -> SQL Server Integration Services 13.0 SQL Server Integration Services 14.0 dtsx from commandline executes fine. Challenge is running it only from an IDE. Could you please let us know if we missed something in our dev machine to load the custom components in ssis toolbox in x64? Also, could you please suggest the list of softwares needed to run the same package using VS2019 in x64.

That's not how Visual Studio works. The designer and VS is an x86 app. You can only load the designers for x86 components. The designers needed to be registered in the x86 GAC. Once the designers are installed then your package designer should be able to load the designer files and allow you to edit the components/tasks/whatever. At runtime the mode of SSIS determines which version of the component is needed. If you have configured your SSIS package (SSIS package property) to run in x64 mode then you need to have the runtime DLL components installed in the x64 GAC. If you are instead running in x86 mode then you need to have the runtime DLL components installed in the x86 GAC. If you need to support both then you need to register them twice.

Could you please suggest the list of software needed to register and use a custom component in data tools tool box using VS2019 in x64

Accepted answer

Michael Taylor 48,486 Reputation points

2024-04-08T15:25:23.4666667+00:00

That's not how Visual Studio works. The designer and VS is an x86 app. You can only load the designers for x86 components. The designers needed to be registered in the x86 GAC. Once the designers are installed then your package designer should be able to load the designer files and allow you to edit the components/tasks/whatever.

At runtime the mode of SSIS determines which version of the component is needed. If you have configured your SSIS package (SSIS package property) to run in x64 mode then you need to have the runtime DLL components installed in the x64 GAC. If you are instead running in x86 mode then you need to have the runtime DLL components installed in the x86 GAC. If you need to support both then you need to register them twice.
Please sign in to rate this answer.

1 person found this answer helpful.
SAILANATHAN Balaji 20 Reputation points

2024-04-09T06:42:34.6833333+00:00

Thanks Michael, I shall try to register in both x86 and x64 GACs . Having the component dll created in AnyCPU mode, will it work around registering twice, once in x86 and 2nd time ion x64?

Michael Taylor 48,486 Reputation points

2024-04-09T14:27:02.6033333+00:00

AnyCPU will work in either mode. However it is 2 different calls to register (either gacutil or Add-GacAssembly). You need to run the x86 version to register in x86 mode and the x64 version for x64 GAC. That requires 2 different sessions (one using x86 CLI and the other using x64 CLI).

SAILANATHAN Balaji 20 Reputation points

2024-04-18T15:17:46.85+00:00

Thanks Michael!

I have a challenge here. I couldn't compile my custom component in AnyCpu or in x86 mode as it has external libs dependencies which doesn't support x86 compilation. I could compile my custom component only in x64 mode due to these dependencies. Is there any other work around? Is there a x64 installer for SSDT or VS 2015/2019/2022 which supports custom components (compiled in x64 mode) detection in data tools toolbox of SSIS?

Responses are greatly appreciated!

Michael Taylor 48,486 Reputation points

2024-04-18T15:30:27.1433333+00:00

Maybe I should clarify something that might not have been clear before. There are 2 components to an SSIS task/pipeline/whatever: designer and runtime. The runtime component is loaded by SSIS's host and executed at runtime. This needs to be the same bitness as the host (x64 in your case). It is what does the actual work at runtime.

At design time the runtime component isn't needed. The design component (which is a completely separate assembly) is hosted in VS and needs to be x86 up until VS 2022. Once you start using VS 2022 then it needs to be an x64 component. In general the design component should be compiled as AnyCPU. It should have no dependencies on anything that is used at runtime. The sole purpose of the designer component is to display a UI that allows the developer to configure the component and to persist that information into the package file that is being used. Since the component is just a UX then it should have no issues running on either x86 or x64 hosts. It won't be loading any runtime components so it shouldn't have any dependencies that would require one or the other. In most cases it is just a series of Winform dialogs that are used to populate the designer UX.

SAILANATHAN Balaji 20 Reputation points

2024-04-22T05:09:36.5633333+00:00

Hi Michael, Thanks for the clarity. We shall try using VS2022. However it demands our db to be upgraded from sql server 2017 to a higher version. That should be fine.

Great, thanks for all your efforts in describing things clearly :) Have a great day!

SAILANATHAN Balaji 20 Reputation points

2024-04-29T06:49:33.7033333+00:00

Hi Michael,

I tried VS2022 as it is a x64 installation. For Integration Services Projects, i downloaded SQL Server Integration Services Projects 2022 from Extensions. Also, i downlaoded separately from https://marketplace.visualstudio.com/items?itemName=SSIS.MicrosoftDataToolsIntegrationServices .

However, there are issues in the SQL Server Integration Services Projects 2022. When i install the downloaded exe, it does not detect my VS 2022. I see the same complaint from many other users. Any idea how can i get the SSIS working in VS2022?

Michael Taylor 48,486 Reputation points

2024-04-29T13:55:11.38+00:00

You install SSIS by going to Manage Extensions in Visual Studio 2022 and then searching for SQL Server Integration Services Projects 2022. Then install it directly from VS and restart. Note that it may already be installed so check your Installed tab first. If it is already installed then it won't install again.

Note also that SSIS is heavily tied to the versions of SQL it was built for and therefore if you're using an older version of SQL Server then you may not be able to use VS 2022 for it. Refer to the release notes for the SSIS extension to determine what versions of SSIS it supports. If it doesn't support your version then you'll have to continue to use a previous version of VS. This shouldn't have any impact on your runtime components, only the design time components are loaded by VS.

SAILANATHAN Balaji 20 Reputation points

2024-04-30T05:51:58.5466667+00:00

Thanks Michael! I tried installing from Manage Extensions as well. Same issue. It is not in already installed tab. Even while installing from extensions, it doesn't show the installed VS2022 instance for selection. Strange, i have the VS2022 installed and i am using it.

Regarding the Sql server version SSIS in VS2022 supports from Sql server 2019 and above which is fine in our case. All we struck up now is with the installation of Server Integration Services Projects 2022

Michael Taylor 48,486 Reputation points

2024-04-30T14:13:31.1433333+00:00

Run the VS Installer and ensure you have the Data storage and processing workload installed. Then try again.

Can you share a screenshot of what the VSIX installer looks like when installing SSIS extension? Can you also look at what extensions are installed in your VS instance and share those?
Sign in to comment

Could you please suggest the list of software needed to register and use a custom component in data tools tool box using VS2019 in x64

0 additional answers