Tag Archives: Visual Studio 2013

Creating a SSRS report to show SSIS package run time statistics

In most companies developers are restricted from accessing the MSDB database and they rarely know the performance of their packages in a production environment unless they have access to third party software tools or a friendly DBA. This happened to me once when I wanted to know how long my packages ran in a production environment and I had no access to the MSDB database to look at the sysjobs and sysschedules tables. The work around is to enable SQL Server logging in SSIS packages and to create a SSRS report from the sysssislog table.

The logic behind this solution is to enable SQL Server Logging in SSIS packages while we create/develop the package and send it for deployment.

Follow the rest of the article from here

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

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.

Why you should upgrade to SQL Server 2014:

I shared my blog with my brother Zulfi Haja, he is a SQL Server Consultant too and works for an investment bank in London,UK. Having reviewed the top 5 features of SQL Server 2014, He shot me an email saying, Bro the effort you put on summarizing the top 5 features was worth reading and it’s a neat approach, would definitely recommend it to my colleagues, but I am still not convinced for an upgrade to SQL Server 2014. Can you help?

I thought only a Tech Evangelist from Microsoft will be able to answer him. I took the question from him with a little hesitation and replied back saying, “I don’t know how long will it take but surely will get back to you once I find a convincing answer”. He said, All the best!!

Was looking out for users experience with Enterprise 2014 version across my network circle and over the blogs, it was of no use. In the beginning of May I joined a local SQL Server User group and they invited me for their monthly meeting. To my surprise there was a presentation about In Memory OLTP and SQL Server 2014 by a Product Specialist from Microsoft. I said to myself, I found the guy and this Product Specialist and Tech Evangelist should be the one to answer my question. His presentation was very convincing enough and I found the answer from his talk. After the presentation the Q&A session was open and I asked him the same question and he gave me the expected answer from presentation. It is SQL Server 2014 can now support Tier1 application.

Let me elaborate this, if you have hanged out with a developer crowd from different domains they have this pre destined notion with them where they will give you an analogy, Oracle is like a business class of a flight and major critical applications are built using it and SQL Server is like an economy class where middle tiered companies uses it. In fact, of all my projects 60-70% of them will be the front end applications supported by Oracle and we had the data replicated to us in SQL Server environments for our reporting and analysis purposes. With SQL Server 2014 that pre destined notion is no more and as per my conversation with the Tech Evangelist, SQL Server 2014 will be good enough to support Tier1 applications and compete with Oracle supported systems because of its new DB engine design. Also he hinted out saying, if NASDAQ uses SQL Server why not your applications?

So Oracle folks better be aware of it

#JunaithHaja

Subversion in Visual Studio 2013 using TortoiseSVN and AnkhSVN:

I would highly recommend a developer to understand the concept of Subversion before accessing any projects stored in a repository.

Subversion is a generic term and developers uses this technology to store their source code, .proj, .soln,web pages and other project related files in a repository. Developers working in a team will be able to download the files and upload the files to the repository with any code related changes thereby providing a single point of access. Subversion belongs to Software Configuration and Change Management field. Different subversion products are available in the market like ApacheSVN, TortoiseSVN, etc. We would use TortoiseSVN for our below illustration.

TortoiseSVN is an open source subversion product developed by Collabnet. We create our repository using TortoiseSVN software. We use the AnkhSVN plugin to access the repository through Visual Studio. In other words, AnkhSVN is an add-on used to integrate Microsoft Visual Studio and Tortoise SVN.

First download and install the latest version of TortoiseSVN 1.8.7 and AnkhSVN 2.5.124751. The installation is straight forward process.

You can verify the installation of TortoiseSVN by right clicking your mouse on any Windows Explorer and you will be able to see options for SVNCheckout and TortoiseSVN in it. The AnkhSVN installation can be verified by opening the Visual Studio and click File, you should see an option for Subversion.

In most real time scenarios a repository will be already existing, so let’s jump on How to access an existing repository.

Copy a Read only Solution from Repository:

If you want to get a read only copy of a solution file,

Step 1: Create an empty folder at your desired location. Let’s call it SQLCASTReadCopy.

Step 2: Right Click the folder, Select TortoiseSVN and Click Export

Step 3: Enter the location of the Solution/Project file in the URL Repository drop down and the location of the above created folder SQLCASTReadCopy in the Export Directory field and click OK, you will have a copy downloaded from the repository to the above folder.

Now you can access the downloaded copy from your local machine and you will not be able to write any changes to the repository.

Copy a Read and Write Solution from Repository:

Step 1: Create an empty folder at your desired location and call it SQLCASTWriteCopy

Step 2: Open the Visual Studio 2013, Click File -> Open -> Open from Subversion

Step 3: Enter the location of the solution/project file at the repository in the From field of the dialog box and enter the location of above created local folder location SQLCASTWriteCopy from Step1 in the To field and Click OK.

Now you will have a read/write copy downloaded to the above location.

Your changes can be written to the repository by clicking COMMIT and UPDATE in the Pending Changes window of Visual Studio 2013.

In any of the above process if the dialog box prompts for your NTlogin and password, enter it and it will validate with the Active Directory.

Hope it Helps:-)