Monthly Archives: July 2014

Running SSIS Packages in 32 bit mode

The original error message will be like this,

[Excel Source from Direct [16]] Error: SSIS Error Code DTS_E_CANNOTACQUIRE CONNECTIONFROMCONNECTIONMANAGER.The AcquireConnection method call to the connection manager “Excel Connection” failed with error code 0xC00F9304.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Solution will be to Open the Visual Studio/BIDS–>Right Click at Project level –> Open Properties–>SetRun64BitRunTime as False

Running SSIS package in 32 bit mode.

In Production mode the Error will be like,

The requested OLEDB provider Microsoft.ACE.OLEDB.12.0 is not registered

Solution is to Right Click the Job from SQL Server Agent–>Open Properties–>Go To Steps –> Click Edit–>Open Execution Options Tab –> Use 32 bit run time

Running SSIS package at 32bit from SQL Server AgentCommentary: I was told the above two error occurs when we have our development machine running a 64 bit OS and our SQL Server is 32 bit so we have to force it to run at 32 bit.I believed the same, but accidentally I found my Server was 64 bit when I was working on an other issue, So what’ causing this? This error happens only when a package uses any of Microsoft Office Product like Excel or Access database, to be more precise the Microsoft Products are installed as 32 bit by default in any machine, so the associated drivers with Microsoft products like ACE.OLEDB.12.0 doesn’t supports our 64 bit environment,hence we force them to run at 32 bit. 

Memory Consumption Report in SQL Server 2014

One day I had an weird error related to memory saying “There is insufficient system memory  to run the query“. I know  two things for sure when this error happened, First is this issue should be something related to the Systems Memory.Second is all data regarding memory should be residing in the sys tables.As a matter of fact I don’t want to dig into sys tables and write queries and want to view it as a report. Was researching across SQL Server Management Studio and came across this Standard Memory Consumption report which helped me on that day.

Its a server level report and you Right Click at the Server level–>Reports –> Standard Reports–>Memory Consumption.

Memory Consumption in Sql Server 2014

The report looks like,

Memory Consumption / Usage Report in SQL Server 2014

Had to clear one of the memory components to fix the issue.

Hope it helps!!


Configure Delayed Transaction Durability in SQL Server 2014

After reading my previous article on Delayed Transaction Durability,one of my Twitter follower posted me  this question. How do we configure our server to work with Delayed Transaction Durability? Does it comes by default?

First thing to note, Delayed Transaction Durability can be applied only at Database level and not at Server level. Here is how you configure Delayed Transaction Durability in a SQL Server 2014.

Step 1: Open your SQL Server 2014 in SSMS and Right click the database you want to set this feature and open the Properties window.

Step 2: Go to Options and Set the Delayed Durability property as Allowed under Miscellaneous section.

Delayed Transaction Durability in SQL Server 2014

Now you are all set:)

Coming to his second question, By default Delayed Transaction Durability comes Disabled with SQL Server 2014 and we can set the property as “Allowed”, “Forced” or “Disabled”.

Hope it Helps!!