32 bit SSIS in 64 bit environment

  • michal.giczewski

    SSC Veteran

    Points: 294

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

  • Koen Verbeeck

    SSC Guru

    Points: 258909

    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

    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: 258909

    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

    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

    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: 4052

    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

    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: 5385

    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

    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

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

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