Mixed Data Type issue in Excel in SSIS

Issue: Excel columns having mixed data types are loaded with NULL instead of actual value.

Replicating the problem

1) Created excel with two rows. Slno and Datatypevalue

2) Inserted 10 rows. slno as serial numbers from 1 to 10 and Datatypevalue as first 7 rows are serial numbers and 8 to 10 as text value of numbers.

Slno	DataTypeValue
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	Eight
9	Nine
10	ten

3) Add a DFT in SSIS. Pull Excel source connection task and ole db destination task in data flow.

4) Setup the source and destination connections.

5) When excel data is previewed value in 7 rows and next three rows as NULL.

Cause and Solution

Cause: This is because excel guesses data type by based on TypeGuessrows mentioned in registry settings for excel in the server. BY Default 8 rows as mentioned in registry for excel connection.

Solution 1:  Works most of the times.

This solution involves changing the connection string of excel and registry settings in server where excel is installed.

            1) Set ImportMixedTyes value in registry is “Text”

2) Update TypeGuessrows to 0 in registry. Value 0 means excel considers all the rows to guess the data type of the column.

3) Update IMEX settings in Excel connection string to 1. IMEX = 0 and IMEX = 2 result in ImportMixedTyes being ignored. (Extended Properties="EXCEL 12.0 XML;HDR=YES;IMEX=1";)

Solution 2:  Works at all times.

As generally thought TypeGuessrows does not always scan all rows in excel but instead it scans 16284 rows, this means if any column contains first 16384 rows as numerical and next rows as text value then it will also be loaded as NULL.

To resolve this we need to use a tactical solution by loading the data along with column name. This will ensure column evaluation to text and all the data is retrieved as source value. Later this row data can be deleted by using conditional split in SSIS or delete from table option. Additional to column change connection string in excel needs to change to expect mixed data type data in the columns. Append IMEX=1 in extended properties of connection string.

Also note this solution does not require change in the registry settings.

Note:

1) Registry settings for excel can be found in below path

Path: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel\

2) Care should be taken during browse for registry settings. Registry settings can be viewed with the help of information available in excel connection string details. Check OLE DB provider version, in this case it is 12.0.

Connection String: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=O:\Users\Vinay Kumar\ETL Projects\Excel Data Type issue\Excel_DataType_Issue\Test.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES;IMEX=1";

3) By default IMEX=1 won’t be available in connection string. This is updated to resolve the mixed data type issue. 4) JET OLE DB provider – used to read Excel 2003 or older – or with the ACE OLE DB provider, which is used to read Excel 2007 or newer. 

Reference Link:

http://www.dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/

http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/mssqlserver/what-s-the-deal-with/

SSRS Common Issues and Solution Links

1) How to make header to repeat in each page for tabular report.

Solution can be found at http://www.sqlchick.com/entries/2011/8/20/repeating-column-headers-on-every-page-in-ssrs-doesnt-work-o.html


2) Split reports in pages where value is taken from group by column. Basically this is a row grouper.

Provide the pagename and page split details

a) Click on Column which need to group report into pages.
b) Click on properties









c) Update Page break settings and provide pagename based on the requirement.

Best way to Document Code History in T SQL Scripts

/*
==========================================================================================
Author: 

Create date: DD-MON-YYYY

Description: 

Code Steps:   

Change History: 1) Date: DD-MON-YYYY
          Description: 
   
==========================================================================================


*/

Script to find dependent objects

I found that sp_depends does not guarantee correct results, Since Sql 2008 have provided useful way to get correct results. Here is sample script.

For finding Referencing Entities. Typically usage is to find out where does particular table is used in entire database.
SELECT  distinct referencing_schema_name, referencing_entity_name
FROM sys.dm_sql_referencing_entities ('Object_Name', 'object');

For finding Referenced Entities.Typicall usage is to find out tables which are used in stored procedure.
SELECT  distinct referenced_schema_name, referenced_entity_name
FROM sys.dm_sql_referenced_entities ('Object_Name', 'object');

Script to Get Rowcount and Data Size of all the user tables

Script to Get rowcount and Data Size

CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11), reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18), unused VARCHAR(18))
EXEC sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' ' ;
WITH TABLES_ROWS_AND_SIZE AS (
 SELECT TableName , NumberOfRows = CONVERT(bigint,rows) , SizeinKB = CONVERT(bigint,left(reserved,len(reserved)-3))
FROM #RowCountsAndSizes )

select TableName , NumRows = REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,NumberOfRows),1), '.00','')
, SizeKB = REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,SizeinKB),1), '.00','')
FROM TABLES_ROWS_AND_SIZE
 ORDER BY NumberOfRows DESC,SizeinKB DESC,TableName
DROP TABLE #RowCountsAndSizes


Alternate way to get row count of existing tables.

SELECT o.name,
  ddps.row_count
FROM sys.indexes AS i
  INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
  INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
  AND i.index_id = ddps.index_id
WHERE i.index_id < 2  AND o.is_ms_shipped = 0
ORDER BY o.NAME

Script to Check Sql Job Status

/***
When there is need to check for job is completed or not before triggering next process or job. we can use this script.

Setp_id is key column which provides details on Job Status.


**/

USE MSDB
SELECT name AS [Job Name]
         ,CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60 /* hours */  
          +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */  
          + (run_time - (run_time/100) * 100)  /* secs */
           ,CONVERT(DATETIME,RTRIM(run_date),113)),100) AS [Time Run]
           ,cast(run_date as varchar(10)) as Run_Date
         ,CASE WHEN SJH.run_status=0 THEN 'Failed'
                     WHEN SJH.run_status=1 THEN 'Succeeded'
                     WHEN SJH.run_status=2 THEN 'Retry'
                     WHEN SJH.run_status=3 THEN 'Cancelled'
               ELSE 'Unknown'  
          END [Job Outcome]
          ,step_id
          ,step_name
          ,run_status
FROM   sysjobhistory SJH  
JOIN   sysjobs SJ  
ON     SJH.job_id=sj.job_id  
WHERE  step_id=0  
AND    cast(run_date as varchar(10)) = CAST(getdate() as DATE)
AND name = 'Jobname'

ORDER BY name,run_date,run_time

Great Microsoft BI Blogs and Resources

For All MSBI

Sql Server Central

BI Monkey

Code Project

SQL Server 2008R2 Videos

SSIS

F5 Debug Blog