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 54,811 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 50 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 54,811 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 50 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 54,811 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 50 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 50 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 54,811 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 50 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 54,811 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?

SAILANATHAN Balaji 50 Reputation points

2024-05-02T08:39:14.8966667+00:00

Hi Michael,

The Data storage and processing workload is installed fully and correctly.. Below are the screenshots while installing the extension.

Machine 1: VS 2022 is not detected at all. Hence, the install button is disabled without

Machine 2: VS 2022 is detected. However, the selection checkbox is disabled, hence unable to continue clicking Install button as it is also disabled.

Michael Taylor 54,811 Reputation points

2024-05-02T14:00:48.1533333+00:00

Hmm, there is a slightly newer version available but I doubt it resolves your issue. Please show us what extensions you have installed. Also confirm that you're running at least VS 17.6 or higher.

SAILANATHAN Balaji 50 Reputation points

2024-05-08T09:19:14.1533333+00:00

I was using VS 17.4 with SQL Server Integration Services Projects 2022 v 0.3 as recommended in https://marketplace.visualstudio.com/items?itemName=SSIS.MicrosoftDataToolsIntegrationServices&ssr=false#overview

It dint work. But, as you suggested I upgraded to VS 2022 v17.8 with SQL Server Integration Services Projects 2022 v 1.4. It started working . I am able to install it now . VS instance got detected and it allowed me to install. Thanks a lot for the suggestions . I need to check the x64 components in designer though. Hope it should load.

Thanks Michael for guiding through the whole issue.

SAILANATHAN Balaji 50 Reputation points

2024-05-10T06:24:04.49+00:00

Hi Michael,

Looks x64 custom components are not getting detected in SSI Tool box of VS 2022 v17.8. VS 2022 is x64 architecture isnt it. Anything specific to be done to get he x64 custom component in ssis toolbox? i just aded it in GAC 64 . Also, copied the assembly to program files (x86)/....140/DTS/Pipeline components... Anything else to be done?

Michael Taylor 54,811 Reputation points

2024-05-10T16:40:41.05+00:00

The Program Files (x86) folder is for x86 components. For DTS running in x64 mode you need to copy the binary to Program Files\Microsoft SQL Server\<version>\DTS\.... Ensure you copy the binary into the SSIS version folder(s) that you support.

If that doesn't work then I'll have to look closer. I haven't had to do any SSIS design work since SSDT was updated for VS 2022 so I'm not sure if something else changed.

SAILANATHAN Balaji 50 Reputation points

2024-05-12T17:43:03.62+00:00

I can copy the binary into the Program Files\Microsoft SQL Server<version>\DTS... But, i could not see any default installation folder in this location. I think installing SSMS or SSDT should add entries to Program Files\Microsoft SQL Server<version>\DTS... .. But in my case it is adding entries only to Program Files (x86)\Microsoft SQL Server<version>\DTS... Is there a separate x64 version of SSMS that needs to be installed. We use Sql server 2017. So it needs to add entries to 140 DTS folder. What should I be installing to get this below folder rightly setup?

Program Files\Microsoft SQL Server\140\DTS...

Thanks in advance

Michael Taylor 54,811 Reputation points

2024-05-12T19:58:03.98+00:00

SSMS is still using VS 2019 so it is still an x86 binary. However SSMS doesn't load any SSIS designer stuff so it isn't relevant here. VS 2022 + SQL Server Integration Services is what loads designer components and it is now x64.

I cannot test this right now but change to the x64 version of the NET Framework on the command line (C:\windows\microsoft.net\Framework64\v4.0.30319) and run gacutil -l and confirm your assemblies are listed. Then open VS and go to the toolbox with a dtsx package open and refresh toolbox.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

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

Your answer