32 bit SSIS in 64 bit environment

Question

32 bit SSIS in 64 bit environment

michal.giczewski

SSC Veteran

Points: 294
More actions
October 13, 2013 at 10:01 pm

#299181

Comments posted to this topic are about the item 32 bit SSIS in 64 bit environment

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Interesting point of view. I find it quite a hassle though to execute SSIS packages using the way you proposed. Validating a package to find out if it is 32-bit or 64-bit? And using xp_cmdshell? :ermm:

Why don't you schedule packages with SQL Agent the usual way and configure the packages that you'd want to run in 32-bit mode to actually run in 32-bit? You can do this for each individual package, there's no need for an all-or-nothing approach as you mentioned in the article.

ps: the reason Run64BitRunTime doesn't work is because it is a Visual Studio debug setting. It won't work on a server, as there's no Visual Studio environment to run the package.

pps: you're not executing PowerShell with xp_cmdshell. You're just starting an .exe file in a shell.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sureshdk Grasshopper Points: 11 More actions · Answer 2

In some environrment xp_cmdshell is disabled/removed because of the hardening purpose. So we may not use this, is there any alternate method?

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

sureshdk (10/14/2013)
In some environrment xp_cmdshell is disabled/removed because of the hardening purpose. So we may not use this, is there any alternate method?

Yes, use the options in SQL Server Agent SSIS jobstep to indicate if a package should run in 32-bit or not.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

michal.giczewski SSC Veteran Points: 294 More actions · Answer 4

Hi Koen

Thanks a lot for you comment.

The reason why I haven't used SQL job agent is because this solution has been implemented for a client using SQL server with a limited permissions. They can't schedule jobs. Moreover there is a lack of technical / admin skills within client's environment. So they simply use our data warehouse front end to do all required admin stuff. When they need to execute SSIS or a job they do it via front end application.

As much as I agree that front end should be changed in a way that you've described (if needed run SSIS via SQL agent with 32bit settings enforced, probably additional tick box on already existing form) but changes to the product take time. They need appropriate design, approval, QA ETC).

Also I’m not sure if I would like to ask our customers (often with minimal tech skills) to choose if package is 32 or 64 bit as they wouldn't know it. Knowing life, everything would be executed in 32 bit mode – just in case:)

The idea is that whatever SSIS package they are going to throw at the system it should work. Number of customer support calls, involved investigations should go down.

But all of this as I've mentioned above: when there is a lack of skills and you want to cut down on support.

I hope I have explained reasons behind to your satisfaction.

Take care

Mike

michal.giczewski SSC Veteran Points: 294 More actions · Answer 5

I guess if you have a problem with using xp_cmdshell then Koen suggestion is quite good. You could create a generic job and write a stored procedure dynamically changing a job step(s). You could then make this stored procedure to execute SSIS in 32 bit mode.

Chris Hurlbut SSCarpal Tunnel Points: 4162 More actions · Answer 6

Hmmm..Seems like there would be security concerns with this approach. Also doesn't seem to follow the KISS philosophy, don't know how junior developers would do setting up this as a solution.

michal.giczewski SSC Veteran Points: 294 More actions · Answer 7

pps: you're not executing PowerShell with xp_cmdshell. You're just starting an .exe file in a shell.[/quote]

You are absolutely correct here. I was working on a project few weeks ago involving execution of powershell scripts from xp_cmdshell.

Looks like i have midex sorry mixed things up. Too many things at once:)

Misha_SQL SSCertifiable Points: 5407 More actions · Answer 8

Note that if you are using SQL 2012, you have the ability to run SSIS packages from T-SQL without having to resort to xp_cmdshell:

http://sqlblog.com/blogs/davide_mauri/archive/2011/11/24/execute-a-ssis-package-in-sync-or-async-mode-from-sql-server-2012.aspx

lesliel Valued Member Points: 72 More actions · Answer 9

I'm trying to do use the SP on a Standard Edition 2005 SQL Server that can't be upgraded to 2008 R2 yet. You can't set the run in 32 bit mode on the job, so I can't do run my package via a job currently. I updated the package to not be 64 bit and uploaded it to the server.

I'm able to run the package manually on the server, and if I open integration services and run the package on the server it works... but this SP gets the same errors as calling it via a job 🙁

ugh...

Not sure what to do here as drivers for this connection are only 32 bit.

Any suggestions?

Thanks,

Linda