The original error message will be like this,
[Excel Source from Direct ] Error: SSIS Error Code DTS_E_
Solution will be to Open the Visual Studio/BIDS–>Right Click at Project level –> Open Properties–>SetRun64BitRunTime as False
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
Commentary: 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.
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.
The report looks like,
Had to clear one of the memory components to fix the issue.
Hope it helps!!
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.
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!!