What ARE the ways to run SSIS packages? Actually, I can think of 5…
- Thru SQL Server Business Intelligence Development Studio (BIDS) 32 bit ONLY
a. on a developer’s box (32 bit only)
b. on the server itself (32 bit only)
c. In Visual Studio environment: still 32 bit BUT connections…
i. DESIGNTIME: at design time, we can only see 32-bit drivers.
ii. AT RUNTIME, if we (have) set the Run64BitRuntime option to true in project properties, it will load 64-bit DTSDebughost.exe and this will use 64-bit drivers for connection. When a 32-bit program is installed, it is registered in the 32-bit registry view. The registry reflector also copied this information into the 64-bit registry section. Therefore, any 32-bit or 64-bit programs calls this 32-bit version of installed program. So in your case, the 32-bit providers that you are using to set up the connection has reflection of registry keys disabled and hence can only be invoked from 32-bit applications.
d. Selecting 32-bit or 64-bit Package Execution in SSIS Designer: In the Project Properties of an Integration Services package, you can select 32-bit or 64-bit execution by setting the value of the (SSIS) Run64BitRuntime property on the Debugging page. By default, the value of this property is True. When the 64-bit version of the Integration Services runtime is not installed, this setting is ignored.
e. To configure a connection manager to use a specific 64-bit provider, you must install the 32-bit version of the provider on the development computer for use at design time. Even though the 32-bit version of the provider is installed, you can still run the package in 64-bit mode, both in the development environment and after deployment. The 32-bit and 64-bit versions of a provider have the same ID. Therefore, the SSIS runtime will select the appropriate version of the provider to use. For example, you run the package in the development environment on a 64-bit computer. By default, the package uses the 64-bit version of the provider because the default value of the Run64BitRuntime project property is True. - Execute DTEXEC.EXE Command Line Utility 64 bit default (based on the path)
a. The 32-bit dtexec.exe loads 32-bit drivers and 64-bit dtexec.exe loads 64-bit drivers. - Execute DTEXECUI.EXE Utility 32 bit ONLY
- Execute SQL Server Agent Job 64 bit default (Based on the registry as to what dtexec is called)
a. Via Job Type SSIS…in which case it appears to call a modified version of DTexecui.exe …which goes 64 or 32 bit based on path precedence normally 64 bit.)
b. Via Job Type OS: (PATH makes the default) can call a specific (normally 32 bit) version (by path) to avoid 64 bit issues.
c. Also: To run a package in 32-bit mode from a 64-bit version of SQL Server Agent, select Use 32 bit runtime on the Execution options tab of the New Job Step dialog box. - SSMS: Connect to the Integration Server; in Object explorer, Navigate down to the package; right-click, choose ‘execute’ … this brings up the DTExecui.exe interface, thru which you can execute the package. Here is a key point about this…if you are running SSMS on your desktop…ie your own machine, (in front of you), but the SSIS package is on the “dev” box (a server, NOT your own box)…when you ‘run’ the SSIS package thru SSMS, the SSIS package actually instantiates to run on your box…NOT the server! Think that thru.
Primary source: http://msdn.microsoft.com/en-us/library/ms141766.aspx :