Home > SSIS > SSIS – Excel Tip #1:- SSIS Guessing Data Type of Excel Source Column

SSIS – Excel Tip #1:- SSIS Guessing Data Type of Excel Source Column

First we check what the meaning of Guessing Data Type is. Later we try to resolve the issue.

Let’s say we have two excel files with data as given below:-

File: – EmployeeFile_v1.xls

EmployeeID Name Salary
1000 Rahul 6000
1000 Rahul 6000
1000 Rahul 6000
1000 Rahul 6000
1000 Rahul 6000
1000 Rahul 6000
1000 Rahul 6000
1000 Rahul 6000
abc Dilip 8000
1000 Rahul 6000
1000 Rahul 6000

 

File :- EmployeeFile_v2

EmployeeID Name Salary
Abc Dilip 8000
Abc Dilip 8000
Abc Dilip 8000
Abc Dilip 8000
Abc Dilip 8000
Abc Dilip 8000
1000 Rahul 6000

 

Let’s Create one SSIS package where we use Data Flow task, inside it Excel Source and using Data Viewer control we can check what data is giving with different files as discussed above.

Excel Different Sources

Above Screen we have two different Sources.

Source 1:- EmployeeFile_v1.xls

Source 2:- EmployeeFile_v2.xls

Using Data Viewer Control we will see what kind of data we are getting .

Below screen shows the data output of both the sources.

Excel Data Output

In above screen we can see the first file i.e. EmployeeFile_v1.xls has Dilip with EmployeeID =”Abc”.

However the output is converted to NULL in highlighted box above.

SSIS excel source guess the data type of the EmployeeID as Integer in first file as it has most of the data Integer. When String data came it can’t fit data into Integer data type so it stores as null.

However in second file also it guesses the data type as string, but numbers can be put into string.

To prevent Excel on TypeGuessing.

We need to do two steps as below:-

    • Connection String in Excel Connection manager should be written as Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\EmployeeFile_v1.xls;Extended Properties=”EXCEL 8.0;HDR=YES;IMEX=1″;

IMEX

    =1 should be added at the end.

  • We need to modify in the registry, where there is a parameter

Path to reach that parameter is

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel

By default TypeGuessRows has value 8, modify to 0.

After above steps just execute the package.

Excel Data Output Without TypeGuess

Now in above result we are not getting null in string data , as it is converted into string data type.

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Advertisements
Categories: SSIS Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: