How to fix the Non-unicode to unicode data type conversion problems in SQL Server Integration Services?



Are you trying to import an Excel file into SQL Server using SQL Server Integration services…And ran into error that has words like “Non unicode” and “unicode”? Then this blog is for you.

Why does this error occur?

Well it turns out that things like SQL Server and Excel have encoding standards that they follow which provides them a way to process, exchange & store data. BUT turns out that SQL Server and Excel use different standards.


So, the solution is simple right? Import the data from Excel into non-Unicode format because that’s what you need for SQL Server.

So how do you that? Between your Source and Destination tasks, include a task called “Data conversion” and do the following for all columns that have text:

Excel SQL Server Unicode Nonunicode

And in the destination task, you’ll have to make sure that the mapping section using the new output aliases that you defined in the “data conversion” step.


In this post, we learned about how to solve a common error that pops up when you try to import excel file to sql server using SSIS. Hope that helps.

Author: Paras Doshi

Titanic Data


Here’s a link to download the Titanic data — — it’s really useful in analytics and data science projects. You can:

  1. Build a predictive model. Example:
  2. I also use this data set to create interactive dashboards on tools like Qlik and Tableau to understand their features.


If you liked this, you may also like other data sets that I have here:

Productivity Tip: Learn to Comment/Uncomment SQL code using shortcuts


I spend a lot of time writing SQL code — and as a reader of this blog, You might be in the same boat. So any productivity gains that we could get here could go a long way. On that note, here’s a quick productivity tip: Learn to comment/uncomment multiple lines of SQL code using keyboard shortcut.


If you are using SQL Server Management Studio, it’s “CTRL-K followed by CTRL+C” for commenting AND “CTRL+K followed by CTRL+U” for uncommenting.

If you are using some other Data Management Software tool, I am sure you can find it using their HELP section or googling around.

Either ways, these shortcuts go a long way in making you more productive! What is your favorite productivity tip?

Qlik sense: How to see Data Load Editor scripts for apps developed by your Team members?


(This post first appeared on the Qlik Community. here)


So you just joined a Business Intelligence Team and one of the responsibilities include building apps for your business users. Eventually, you would have a need to see Data Load editor scripts for apps developed by other members in the team. So what permission do you need to be able to do that?

Credits: darkhorse

Qliksense Version: Enterprise Server 2.0

Source: can’t see a peer’s data load editor scripts


This a two-step process.

1) Get “content admin” access (or “higher” level access)

2) Double check if you have access to see data load scripts for ALL apps

Step 1:

The short answer is that you need “Content Admin” permission from your Qlik sense admin…But with this access level, you will have access to other developer’s app via QMC. If you need to do this via HUB as well then you will have to change the content admin role.

Here’s how Serhan ( darkhorse ) explained how to get this done:

QMC–> Security Rules–>Content Admin–> Edit–> Context–> Both in Hub and QMC

Qlik sense management console

Step 2:

Now, once you get the “content” admin access, you might want to double two things:

1) You can get access to data load scripts on published apps — (I was able to do this but there still seems to some open questions around some folks not being able to see the data load scripts for published apps. If this is the case for you, you need to duplicate the app on your “my work” area and see the scripts)

2) You can duplicate apps on your “my Work” area and see scripts — this is also useful if you want to make changes to published apps that are out there.


I hope this helps you resolve the permission issues and help you collaborate with your team members!

How to add Sparkline data visualization to Google spread sheets?


I like using spark lines data viz when it makes sense! It’s a great way to visualize trends in the data without taking too much space. Now, I knew how to add sparklines in Excel but recently, I wanted to use that on Google sheet and I had to figure it out so here are my notes:

1. Google has an inbuilt function called “SPARKLINE” to do this.

2. Sample usage: =SPARKLINE(B2:G2) — by default you can put line chart in your cells.

3. Then there are other options including changing the chart type. You can find them documented here:

4. One of the best practices that I advocate when you spark-line to “compare” trends is to make sure that you have the consistent axis definition. So the sample usage for that could like this:


(if you want to do this for excel then here’s the post: )

After you’re done, here’s what a finished version could like on Google sheet:

Google Sheet Data visualization spark line

Here’s the working google sheet:

Data -> Insights -> ?


I was at the HP Big data conference last week and I heard something during the keynote that’s worth sharing with you.

As Data & Analytics professionals, we spend a lot of our time on finding insights, trends & patterns out of the data but the keynote speaker (Ken Rudin, Facebook) encouraged everyone to take that a step further = Think about Driving impact based on the insights. It’s simple yet a powerful idea! Over past few months, I have started working closely with decision makers and helping drive impact vs just “handing-off” insights.

I hope that helps! Just wanted to share that with you. What do you think?


How to add custom User-ID to your Universal Analytics (Google) implementation?


There are three different implementations that you could have with Google Analytics:

  1. Classic Google Analytics
  2. Universal analytics
  3. Universal analytics with Google Tag manager.

If you fall under “2. Universal analytics” then this post is for you since I’ll walk you through steps that you need to take see UserID’s on your google analytics report.

First why do you want UserID on your GA reports? 

1) Data blending is powerful. If you can combine your transactional datasets with web traffic data then you can extract some powerful insights! To be able to join your transactions data with web traffic data, you need some field that is common across those datasets. UserID might be one of the most useful fields that you could use for data blending.

2) Also having UserID in your reports let’s you perform some user behavior analysis at individual level and that could be pretty powerful too!

Now, How?

There are three steps:

  1. Identify the User ID
  2. Create a Custom Dimension on Google Analytics
  3. Modify your tracking code to send data for the custom dimension

Step 1. Identify the User ID

The first step is to identify the ID that you are going to send to Google analytics. Remember that you can’t send PII (personally identifiable information) so you can’t send something like an email id but you can send other ID’s that may be used in your database.

Step 2: Create a custom dimension on Google Analytics

Go to google analytics admin section > Select your account & property > Go to Custom dimensions

Custom dimensions google analytics

Now, create a new custom dimension. Give it a name, scope it and make sure it’s marked active.

Custom dimension universal analytics User ID

Step 3: Modify your tracking code to send data for the custom dimension

Notice that once you create the dimension, it will also show the example code (on the right side in the picture above). Send this to your web developer!

Just a note on this: The custom dimensions for which you are passing value using the tracking code are referenced as dimensionxx and you don’t use the Name like “Custom User ID” — if it’s the first custom dimension that you will refer to it as dimension1 in the code.

Next steps: Let me know if you have any further questions and if you are interested in seeing the steps for classic GA and Universal analytics w/ Google Tag manager then here’s a blog for you:

I hope this was helpful!

What percentage of users are authenticated? (Google Universal Analytics)


You’re using Google’s Universal Analytics — That’s great! They key to make sure that you get the most out of it is to make sure that you incentivize your users to log-in aka authenticate. First step in doing that is to figure out percentage of users that are authenticated…Here’s how you can see that report:

1. Login to Google Analytics

2. Select your view > Go to “Reporting” section

3. Navigate to Audience > Behavior > User-ID coverage

Google Analytics User ID Universal

4. On this report, you can see authenticated vs unauthenticated sessions:

Percentage of authenticated users google analytics Universal


In this post, we talked about how to run a report that shows you percentage of authenticated users. (In google’s Universal analytics)

SQL: How to get first/last transaction details for each user account?


Looking at user’s first/last transaction details is a common requirement. So given that you have a “user_transaction_details” table which looks something like:

Transaction_id | user_id | transaction_date | purchase amount

How would get first/last transaction details for each user account?

Here’s a design pattern:

select AllTransactions.user_id,AllTransactions.purchase_amt from user_transaction_details AllTransactions
inner join 
select user_id, min(transaction_timestamp) as first_transaction_timestamp from user_transaction_details
group by user_id
) FirstTransaction
on AllTransactions.user_id = FirstTransaction.user_id and AllTransactions.transaction_timestamp = FirstTransaction.first_transaction_timestamp

To get the last transaction details for each user account, replace min function with max and that should be it.

Note: Depending on your data model and how you used it in the join, it might be that there would be multiple rows marked as “first/last” transaction and so would want to spend some time figuring out how to deal with these multiple row situation especially if you’re planning to further join this data.

In this post, I shared a design pattern to write a SQL query to get first/last transaction details for each user account type.

Question for you:
How would you solve this problem? Share you query in the comments section!

PASS Business Analytics conference 2015 – Keynote Day 1. #passbac


I’m at PASS BA conference this week to learn about current & future state of business analytics world. I would categorize today’s keynote as a session that gave me an insight what’s our “future”.

Before I begin describing my takeaway’s, I would provide some context by providing keynote speaker’s background.

The keynote speaker was Carlo Ratti who directs MIT’s seanseable city — a group that focuses on how to use data to understand our cities and works on research projects to make it better!

So, the theme of the keynote was to show some of the research projects and inspire audience on how big data was used to come up with actionable insights to make our cities better. The projects are usually at the intersection of Internet of things and Big Data.

With that context, here’s are my notes:

– What is Big Data? 

(I thought that was funny but behind it, there’s an interesting takeaway: we need NEW tools and approach to deal with Big Data! btw, MIT has their in-house tools to generate data viz’s on bigdata — one such example: Data Collider

– Internet of things & Big data

Carlo also showed the relation between Internet of things and Big data by using data to show exponential growth that we have seen in mobile connections and how it’s poised to grow further in coming decade because of internet of things.

– Big Data = opportunistic + user-generated + purposely sensed. 

Carlo then categorized his projects in three categories: Opportunistic, User-generated & Purposely sensed.

– Opportunistic

couple of examples shared by the speaker:

1. Re-drawing Great Britain’s map from a network of human interactions:

2. A better way to handle traffic at traffic lights. (Project Wave)


1. using user-generated content to figure out where’s the best party in Barcelona? (project World’s eye)

2. analyzing user tweets during an event. (Project: Tweetbursts)

– purposely sensed

1. Having sensors in the bike that gives you info like pollution, traffic congestion and road congestions in real-time.

(Project: Copenhagen wheel.)

copenhagen wheel

2. Following e-waste around the world. Sometimes the energy put into discarding the waste is more than we can ever get out of it after its recycled. This should be improved! (Project: Trash Track)

That’s it for the notes.

You can check out all of their projects on their site!

After seeing the demo’s, I can’t wait to live in one of this “smarter” city. What a fascinating application of data! — The future is awesome!