none
Facing issue while working on a project to import data from an Excel worksheet using SSIS RRS feed

  • Question

  • Hi All,

    I am working on a project to import data from an Excel worksheet using SSIS. While working on the project I realized that the SSIS Excel Connection manager determines the data type and length of columns from the worksheet on the basis of the first eight rows of data. By default it is taking data type as double-precision float [DT_R8], I want to change it to Unicode string [DT_WSTR]. I am trying to change the data type from the Advance Editor for Excel Source but it is not allowing me to do and the information will be reset by SSIS Excel Connection manager automatically.

    I have also gone through a post where it was advised to change the registry value i.e. Changing the value for TypeGuessRows registry key to 0 from its default value of 8, but it also did not worked :(.

    Registry Path for 64- bit machine:- HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel

    Please help.

    Thanks
    Ashutosh

    Monday, September 16, 2013 3:46 PM

Answers

  • I think IMEX=1 will only help for mixed datatypes in a column. If the first 8 rows are all numbers then it won't change to string with imex=1, even if the 9th row is a string:

    Changing TypeGuessRows wil only change the number or rows to be checked. If all the checked rows are number then it won't change to string.

    What you could try is to include the header row (if there is one) and set IMEX=1. Then you only need to filter the first row with a conditional split.


    Some more ssis+excel info.


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Monday, September 16, 2013 5:46 PM
    Moderator

All replies

  • Make the connection string to Excel include IMEX=1, this may help overcome this issue.

    Arthur My Blog

    Monday, September 16, 2013 5:34 PM
    Moderator
  • I think IMEX=1 will only help for mixed datatypes in a column. If the first 8 rows are all numbers then it won't change to string with imex=1, even if the 9th row is a string:

    Changing TypeGuessRows wil only change the number or rows to be checked. If all the checked rows are number then it won't change to string.

    What you could try is to include the header row (if there is one) and set IMEX=1. Then you only need to filter the first row with a conditional split.


    Some more ssis+excel info.


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Monday, September 16, 2013 5:46 PM
    Moderator