Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SSDT-BI SSIS in x64?

I run Visual Studio 2012 and I'm new to SSIS. I attempted to install SSDT-BI (June 2013 version) in order to research SSIS, but I was unable to complete the install with my default instance because the architectures don't match--my server is x64 while the installer is x86. There doesn't appear to be an x64 version of the installer.

Does SSDT-BI not support x64 databases?

like image 432
bwerks Avatar asked Aug 26 '13 23:08

bwerks


People also ask

How do I run a SSIS package in 64-bit mode?

In the Project Properties of an Integration Services package, you need to select 64-bit execution by setting the value of the Run64BitRuntime property to true on the Debugging page. By default, the value of this property is True.

Is SSIS 32 or 64-bit?

SSIS packages can be run using either the 64-bit or the 32-bit SSIS engine (64-bit is default, but this can be changed by setting the Run64BitRuntime property to false). If you do not have 32-bit drivers installed on your machine, you will not be able to connect to your data sources at design-time.

Is SSIS included in Ssdt?

SQL Server Data Tools (SSDT).Installing SSDT lets you design and deploy SSIS packages. SSDT installs the following things: The SSIS package design and development tools, including SSIS Designer.

How do I get Ssdt in Visual Studio 2017?

Launch the Visual Studio Installer. In the Windows Start menu, you can search for "installer". In the installer, select for the edition of Visual Studio that you want to add SSDT to, and then choose Modify. Select SQL Server Data Tools under Data storage and processing in the list of workloads.


1 Answers

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

In 2012, if you use the Project Deployment Model, the packages are stored in the SSISDB catalog. SQL Server only comes in 64 bit flavors but again you can call the 32 bit runtime for SSIS from the catalog by specifying the appropriate Parameter.

like image 66
billinkc Avatar answered Sep 29 '22 04:09

billinkc