SSIS' DtExec 32bit vs 64bit Confusion

Question

this question has been asked before in different flavours, but I cant find a satisfactory answer.

We are an MS based shop and we have plenty of SQL 2016 databases. We also use SSIS 2016 a lot and in my area we exclusively use the catalog (SSISDB) deployment model.

A lot of our SSIS ETLs arent just simple loads or extracts. They are part of nightly batches. They run on a scheduler. A typical batch could be something like this

run SSIS project A
pick up some files produced by A
ftp them somewhere and archive the originals
pickup some other files and put them somewhere
then run SSIS project B processing the above
etc

Our batch scripts are typically python and we use DtExec to run the ETls. Our scheduler machine where the python scripts reside is never the same machine as the SSISDB or SSIS machine. We use DtExec to call out to the remote DB servers and invoke the ETLs.

The scheduler machine itself is not a DB server and does not have SQL 2016 installed only SSMS 2016 which gives us 32bit DtExec.

But now it appears I also need 64bit DtExec and it appears I will only get that if I install Integration Services on the scheduler box also.

This is a problem because of licensing and plus I dont want another SSIS service instance running there. It wont be used for anything anyway. I just want 64 bit client tools like DtExec.

The question is basically how do I get 64bit DtExec onto a machine that is not a SQL Server or even an Integration Services server. How do I get 64bit DtExec on a simple client machine?

Edit: On another note do I really need 64bit DtExec to run SSIS in 64bit mode on 64bit server? Surely thats a remote invocation and it shouldnt matter how many bits the DtExec client was compiled under?

Thank you for reading

Carsten

I have seen some issues with this when using Script Tasks (C# code) in SSIS packages. I believe I had the script task set as execute as 64bit in the script task. If you have this you may want to check into that. — Brad

Ferdipux Ferdipux · Accepted Answer · 2021-01-20T14:48:24

According to MS Docs on DTExec, it can be incurred that in order to run package in 64-bit mode, you need 64-bit version of DtExec.
However, this is not the only way to run SSIS package from SSISDB. You can also start execution of the package with T-SQL commands (see catalog.start_execution or T-SQL commands). This way does not require DtExec at all, and you choose 32 or 64 bit environment with @use32bitruntime of catalog.create_execution.
Some code sample

EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Child1.dtsx', 
@execution_id=@execution_id OUTPUT, 
@folder_name=N'TestDeply4', 
@project_name=N'Integration Services Project1', 
@use32bitruntime=False, 
@reference_id=Null  
Select @execution_id  

DECLARE @var0 sql_variant = N'Child1.dtsx'  
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, 
@object_type=20, 
@parameter_name=N'Parameter1', 
@parameter_value=@var0  

DECLARE @var1 sql_variant = N'Child2.dtsx'  
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, 
@object_type=20, 
@parameter_name=N'Parameter2', 
@parameter_value=@var1  

DECLARE @var2 smallint = 1  
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, 
@object_type=50, 
@parameter_name=N'LOGGING_LEVEL', 
@parameter_value=@var2  

EXEC [SSISDB].[catalog].[start_execution] @execution_id  
GO

You can always use Script feature of Execute Package in SSMS for relevant T-SQL samples.

A good article on this, included a code sample to check environment type inside package.

SSIS' DtExec 32bit vs 64bit Confusion

1 Answers