Download Business Intelligence Development Studio

SQL Server Integration Services, SSIS, covers a whole lot of territory. I'll discuss what's relevant to this question of 32 vs 64 bit-ness.

Installation

As you discovered, the installer is stupid but you typically only need to use it once or twice in a machine's lifetime. I'm ok with this as I'd rather them spend development dollars are the product and not the packaging.

Following SO best practice, the important piece from the externally referenced site is

If you’re running an x64-based SQL instance (64-bit), make sure to select “New Instance” on the Installation Type page, and NOT “Add features to an existing instance”.

Package

An SSIS package is a deliverable unit of work. It is an file with a .dtsx and internally is a bunch of XML.

Development

Typically, development of SSIS packages is completed by using Visual Studio. Whether you call it BIDS, SSDT or just Visual Studio, it's the same thing. There are registered templates for SSIS as well as all the toolbox entries that get put onto a machine during installation. That stuff supports the design time aspect. Visual Studio itself is still a 32 bit executable. Therefore, when you are creating packages using Visual Studio, the editor you are using to create a package is a 32 bit entity. Unless you are creating a multi-gig package, this shouldn't be an issue.

And one might argue that if you get a multi-GB package that even if XML is a bloated storage mechanism, you are "doing it wrong" ;)

On a final note, development of packages is not constrained to Visual Studio. There is a paid-for product, Mist, that uses Biml (a domain specific language that describes BI objects) to generate SSIS packages. Looking at your SO history, you look like you have some PowerShell chops, you can use PowerShell to modify SSIS packages or flat out create them. There the EzAPI to help with the COM side of generating packages.

Execution

The execution of a package can be from a 32 or 64 bit executable. See How to execute dtsx packages through command line By default, the packages will execute in 64 bit mode from Visual Studio. The design time is 32 bit but run time is 64 bit. This always makes for great entertainment when folks use 32 bit drivers (Excel/Jet/ACE) and the packages design just fine but blow up when they run. The other gotcha of 32/64 driver is the creation of DSNs as there are 2 different DSN "spaces" See 32-bit informix drivers in 64-bit windows server 2008 are unavailable