I have recently come across widely known problem where people have issues when executing 32bit SSIS packages in a 64bit SQL Server environment. I have found many solutions including changing debug options within BIDS (Run64BitRuntime = false) or scheduling a job and forcing 32 bit execution on this job, but to be honest I didn’t find these solutions particularly useful in the large automated environment. You can use these methods when you test your SSIS package but not necessarily when you want to deploy it to a SSIS box. I guess the other method would be to create a job with forced 32 bit execution mode and then create a dynamic script that changes job step to execute particular SSIS packages. In my opinion to much hustle and not necessarily the prettiest solution.

I also recently came across a solution while working with customer's data warehouses where data warehouse admin has been asked to change an environmental variable (path) in order to force all SSIS packages to run in a 32 bit mode (info coming from the official admin guide).

Not only when I’ve tried this method it didn’t work (believe me I’ve tried all possible ways) but also when you think about it why would you want to execute perfectly working 64bit SSIS packages in 32bit mode when you don’t have to. Just because you’ve got one or few 32bit packages to deal with it why would you affect performance of the rest of them?

I think it’s time for me to explain what I’ve done in order to deal with this problem. That being said, please don't take this solution as gospel. I’ll bet there are many other smarter developers out there whom will probably prove me wrong shortly after publishing this article.

Problem: run 32 bit solution on 64bit SQL server.

Solution: stored procedure executing SSIS packages (32 & 64 bit).

Right so it doesn't look that bad does it?

Quick explanation:

Before moving further, it would be a good idea to have a quick look at what happens in the syssubsystems table.

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

Step 1: you feed this SP with SSIS package name stored on the SSIS box (if needed this script could be re-build to use SSIS from the file system) – line 2

Step 2: Check for the location of the DTExec.exe file (select * from msdb.dbo.syssubsystems where subsystem = ‘ssis’) – line 16

Step 3: set up SSIS package location – line 24

Step 4: set up code to check if SSIS is 64bit – line 25.

Step 5: Validate package. The package is going to be executed in “Validate” mode only – line 27

Step 6: If error message = 0 then it is 64 bit package so set up @cmd in 64 bit setting – line 31

Step 7: If error message = 1 then it is 32 bit package so set up @cmd in 32 bit setting – line 33

I appreciate that there might be other issues than 32 / 64 bit within the package that can cause its failure. This stored procedure will try to execute it in 32 bit mode in case of any validation issues. But guess what: I don’t care because if the package is broken I’m not bothered whether it’s going to fail in 32 bit or 64 bit mode:).

Step 8: execute SSIS package using xp_cmdshell.

There might be a slight problem with executing powershell (xp_cmdshell) on some systems. I’m not sure if you are allowed to use it or not. This might be a bit of a stopper if you can’t use it. It is possible you might be able to use powershell only for duration of this stored procedure.

You could add couple of bits to my stored procedure in order to make it work i.e.:

If for some reason your SQL server has different program files location then just change the replace bit to whatever you need.

Please let me know if you find any problems with this solution.