Tag Archives: SSIS

3-D’s of Business Intelligence

A friend of mine aspiring to become a BI professional asked me what it takes to become a Business Intelligence professional?

Since the answer to the question is not a one liner I thought of answering it in a post as it will helps others.Before we get in detail let me define BI first,”Business Intelligence is the process of getting useful  and simpler analytics about business from its raw data sources“.

With that being said, it takes one to master 3-D’s (Data Building. Dashboards and Decision Making) to become a successful BI professional.

Data Building: Building your data from raw data sources is fundamental and crucial part of Business Intelligence. One should not use the data directly from real time systems for analytics – the reason is most of the time a company’s data systems are designed in the form of numerical values for easy computing and the numerical data doesn’t makes sense unless Attributes are added to it. Data building is like laying a foundation to your house, so extensive care and research should be taken at this step to avoid future costs.

Depending on the organization size your data building specs might change.Say If you’re a small company, I wouldn’t suggest you for a data warehousing and you can build one Master table from your Work orders or invoices and add multiple metrics to it.

Say If your company is dealing with lot of media files, I would advise you to have a non relational Hadoop kind of database like Azure.

Say if you’re running a massive Billing and Support Call center and  has to deal with tons of invoices and call volumes, a good approach is to have a datawarehouse  and track everything.You can have a combination of Type1 and Type2.

Key Tools and Technologies: SqlServer,Oracle,Teradata,Apache Hadoop,Azure

Dashboards: Once your data is built, the next step is to derive useful information from data in form of reports. By saying reports, I don’t mean long tabular ones- Why do I say that? Working with executives made me learn this trick,let me say it out for you. “Data doesn’t lie at detail and summary level“. You don’t need to have long detailed tabular reports to understand your business you can have them summarized by multiple metrics in a single dashboard. What if you don’t have one?try to combine as many reports as possible and summarize them. Dashboards allows us to view the business through different angles and gives deeper insights in a single view.

Key Tools and Technologies: Power BI, Tableau, SSRS, Business Objects and Excel

Decision Making: One may think a BI professional’s job concludes by creating dashboards. I wouldn’t agree with it, go ahead and analyse the dashboard and try making decisions yourself as if its your own company. This will embark a business mind in you, you may not become a business person overnight but with time it will increase your learning curve and establish yourself as valuable asset to your organization.

Would’t it be nice if you hand over a deck to your supervisor and say “I looked at it and our sales are dropping due to less price offers by our competitors”

Key Tools and Technologies: Your own Brain and curiosity.

Hope it helps:)

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

Configuring Protection Levels of an SSIS Package

We work with SSIS packages holding sensitive data like server credentials and customer information,it is always a  good practice to use Protection Level property to secure the credentials and prevent it from unauthorized access.The Protection can be set up either to an entire package or only to the part holding the sensitive data. Hence its very important for us to know where to go to enable the Protection Level while working with packages.

Thumb rule to be followed is the Package should have same protection level at Project and Package level.

It can be configured in any of the following ways

1. Project Level

Right Click at the Project level and open its Properties window, You can configure the protection level you want from the drop down available at Security part.

Protection Level in Package at Project Level

2. Package Level:

Click anywhere in the designer and open its Properties Window by pressing F4 or View -> Properties

Protection Level at Package Level

3. During Deployment:

If you haven’t configured the Protection Level, you can still configure the package before deploying it to server by this method if you host your solution in a repository say using TortoiseSVN.

Click anywhere in the designer and Go to File ->Save Copy of Package as 


Hope it helps!!



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. 

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

Export a Project File from Repository using TortoiseSVN

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.

Export an SSIS Solution from Repository using TortoiseSVN

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:-)


Top 5 features of SQL Server 2014:

Microsoft has officially launched its SQL SERVER 2014 this April at an event held in San Francisco, CA. Microsoft adds some new features and updates some of its existing features in every other SQL Server version. Sooner or later all our systems will be updated with this version.

So what does it means to a developer?

Is it a good sign or bad sign to a developer?

Did Microsoft made any ground breaking changes to its product?

Should I have to reinvigorate my T-SQL skills to work with 2014?

A Developer mind is bombarded with so many questions like above when we hear about a version upgrade. Don’t worry folks, I have got your back. I was excited enough like every other developer to get the 2014 version within first few weeks of its launch and done my review of it. I did learn a lot and my goal is to share some of its valuable insights a SQL Server developer must know to work with 2014. I will be posting my top 5 favorite features of SQL Server 2014 as a series over next few days. Please keep tuned.


-Junaith Haja