[Translation] execute a 32-bit SSIS package stored in a 64-bit environment

Recently I encountered a well-known 32-bit SSIS package execution problem in the 64-bit SQL server environment. I have read some solutions, such as modifying the debug option (run64bitruntime = false) in ssdt or forcibly running in 32bit mode when creating a job for execution. however, I found that these methods are not suitable for large-scale automated environments. you may use these methods when testing the SSIS package, but you do not necessarily want to do this when deploying it to The SSIS server. I think another way is to create a job and set the mandatory package to the 32bit execution mode. create a script to change the job step to execute a specific SSIS package. but in my opinion it is too troublesome and not perfect.

I recently saw a solution, A customer's Data Warehouse administrator was asked to modify an environment variable to force all SSIS packages to run in 32bit mode (as described in the official management guide)

I tried this method and it was useless (believe me .. I have tried all the methods, but I have not done it ). in addition, you only have one or a small number of 32-bit packages. Why do you need to force all 64-bit packages to run in 32bit mode. this will affect performance.

Well, let's talk about my solution next. But don't trust me in my solution. If you have any better and more convenient methods, please let me know.

If you encounter any problems in the process of using my solution, please let us handle them together.

Problem: Use a 32bit package on 64-bit SQL Server.

Solution: Execute the SSIS package (32 & 64 bit) with the stored procedure ).

Looks good, right?

Quick description:

Before proceeding, let's take a look at what syssubsystems has.

select agent_exe, * from msdb.dbo.syssubsystems where subsystem = ‘ssis‘

Step 1: The storage parameter is @ packagename, which can be passed into the server with an SSIS package name-Line 2

Step 2: Obtain the dtexec.exe location (select * From MSDB. DBO. syssubsystems where subsystem = 'ssis ')-line 16

Step 3: Set the SSIS package location-line 24

Step 4: Check whether SSIS is 64bit-line 25.

Step 5: Verify the package.-Line 27

Step 6: if the error message is 0, it is a 64-bit package. @ cmd: Set 64-bit-line 31.

Step 7: if the error message is 1, it is a 32-bit package. @ cmd: Set the 32-bit package-Line 33.

I realized that there may be other situations that may also cause an error. In this case, no matter whether the package is 64bit or 32bit, It will be executed according to 32bit. However, this does not matter, and 32bit will be executed in the same way anyway.

Step 8: run the package using xp_cmdshell.

In some systems, running powershell (xp_cmdshell) may cause some problems. I don't know if you can use it. If not, you can insert the previous storage into the following code.

The following code is used to temporarily enable xp_mongoshell during storage execution and then disable it:

Resource:

Script click here

Http://www.sqlservercentral.com/articles/Integration+Services+ (SSIS)/102544/