Home > SSIS > SSIS in 64-bit environment

SSIS in 64-bit environment


The Integration Services runtime has a 64-bit version!

Integration Services packages can be create and execute in both 32bit and 64bit environment. The default mode for the BIDS (Business Intelligence Development Studio) is 32bit; if we required running the package on 64bit environment then we have to specify it in the design time. In this article I am going to explain some of the key items which requires developer attention while create a package.

Design phase

We cannot design packages on 64bit mode in BIDS. But, we can set a property to execution the package on 64bit mode. Some data provider and native OLE DB Provider are not available in 64bit environment. So, install both 64bit and 32bit providers and choose the 64bit supported provider while creating connection manger.  SSIS runtime will choose the appropriate version when package runs. It can be handled using Run64BitRuntime project property is debugging section. The default value of this property is true.

We cannot use Execute DTS 2000 Package task to run DTS on 64bit runtime. Because, It does not support 64bit.

We cannot log the SSIS package with SQL Server Profiler log provide in 64 bit mode.

Package running phase

There might be lot of situation where we need to run the package in 32bit mode even in 64bit environment. It is very simple by changing the project property Run64BitRuntime value to false.

We often use SQL Server agent job to run SSIS package. There is an utility dtexec used to invoke the SSIS package. This utility is available in 32bit and 64bit. So, we can set the package execution mode in SQL Server Agent. If the SQL Server and Agent is installed in 64bit then SQL Agent will invoke 64bit dtexec. So, if you wish to execute 32bit version package in this environment then choose run 32 bit runtime while as show in the picture.

SSIS Tools for 64 bit version

The following Integration Services tools also have 64-bit versions:

  • The dtexec utility (dtexec.exe)
  • The dtutil utility (dtutil.exe)
  • The SQL Server Import and Export Wizard (DTSWizard.exe)

Others

Download Microsoft Access Database Engine 2010 Redistributable for data transfer between Office 2010 to SQL Server in both 32bit and 64bit versions. Download now.

For more information read MSDN article

Thanks for reading. For latest technology updates please go to Facebook and like the page.

Advertisements
  1. Niraj
    May 19, 2014 at 11:34 am

    Thanks, with the help of this article I have succeeded in importing excel sheet data in a 64 bit enviornment

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: