0

I am working on data import from Excel to Sql Table using SSIS.

I am facing the issue of some of string values replaced by NULL values(since the first 8 records contains only numeric values). Needless to say I tried with appending the connection string with IMEX=1, but the problem still persists and I dont want to tamper the REGISTRY as recommended in few articles.

Can you guys suggest a resolution to this issue, where there could be string value in a column after the first 8 records in Excel, but it should go with original data in DB. I am looking for a good workaround, knowing that this seems a standard issue.

Justin Samuel
  • 1,063
  • 4
  • 16
  • 30

2 Answers2

1

See my answer to a similar question about how to fix the metadata of an excel source after the fact.

https://stackoverflow.com/a/13459855/236348

Also try this:

Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance. Please also note that adding the IMEX=1 option might cause the IMEX feature to set in after just 8 rows. Use IMEX=0 instead to be sure to force the registry TypeGuessRows=0 (scan all rows) to work.

from this page: http://www.connectionstrings.com/excel

In windows 7 this key is at: [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel]

TypeGuessRows has a range of 0 for all or 1-16 for number of rows to scan. Set as appropriate for your application.

Community
  • 1
  • 1
William Salzman
  • 6,396
  • 2
  • 33
  • 49
  • This is not a metdata issue. This is an issue with the ACE Excel Connector which senses the datatype of a column based on the first 8 row values. My first 8 rows has numeric values followed by alphanumeric values in between. So this is not a case of metadata crash... – Justin Samuel Nov 29 '12 at 06:30
  • Thanks a lot Williams! It did guide me...I just found a workaround and posted the same. Please check. – Justin Samuel Nov 29 '12 at 17:54
0

I found a temporary workaround to the problem. Check posting it here if somebody finds its useful someday...
Solution: 1) In the ConnectionString used IMEX = 1
2) Keep the First Row as Header = FALSE
3) Now use the Data Flow task to import the data from Excel to Sql Data, but only after eliminating the first row(which is Header row) using Conditional Split.

This solution ensures that even if there is no alphanumeric value within the first 8 datarows, the header being alphanumeric the JET/ACE connector will sense the datatype as STRING - DT_STR. This solves the issue of NULLS inserted in between.

For details on issue with Excel as source and possible solution using IMEX=1...please refer:
URL 1: http://microsoft-ssis.blogspot.in/2011/06/mixed-data-types-in-excel-column.html

URL 2: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/1b9020ec-616c-42e2-99c0-18f1258ff5db

Thanks,

Justin Samuel.

Justin Samuel
  • 1,063
  • 4
  • 16
  • 30