Post reply

Data Import Excel XML Defaults (SSIS) Which file governs Excel defaults?

  • July 21, 2015 at 4:34 pm

    #298905

    Hi,

    I'm importing a multi tab spreadsheet using Import wizard, which I understand to use the same internals as SSIS. The total number of columns in the spreadsheet will be over 500. The import wizard defaults everything to varchar 255. I understand there is an XML file I can manipulate to change this and they are located

    C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles

    Assuming one of these will control Excel defaults, which one is it? None of the names lend themselves to the Excel as a source. SqlClientToMSSql10 ?????

    John

    SQL 2012 Standard VPS Windows 2012 Server Standard

  • July 22, 2015 at 1:41 am

    #1814021

    execl and access use the JET provider

    Jayanth Kurup[/url]

  • July 22, 2015 at 11:09 am

    #1814166

    Thanks.

    I'm using 2008R2 and I'm changing the XML file JetToSSIS. I'd like the nvarchar to default to 2000 instead of 255. I'm not modifying JetToSQL8 or JetToSQL9 because those are for version 2000 and 2005. There is no JetToSQL10 so it makes sense to modify JetToSSIS. No luck so far.

    John

    SQL 2012 Standard VPS Windows 2012 Server Standard

  • July 22, 2015 at 11:26 am

    #1814170

    Johnny B (7/21/2015)

    Hi,

    I'm importing a multi tab spreadsheet using Import wizard, which I understand to use the same internals as SSIS. The total number of columns in the spreadsheet will be over 500. The import wizard defaults everything to varchar 255. I understand there is an XML file I can manipulate to change this and they are located

    C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles

    Assuming one of these will control Excel defaults, which one is it? None of the names lend themselves to the Excel as a source. SqlClientToMSSql10 ?????

    John

    The import wizard creates an SSIS package. Don't mess with the XML file! Instead, edit the SSIS package that was created.

    Learn how do create SSIS packages. You'll end up needing less time than using the import wizard and then fixing what it didn't do correctly.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • July 22, 2015 at 11:41 am

    #1814174

    Thanks Alvin,

    I've looked at the etiquette page and gather my question is not clear. It's certainly a challenge to articulate. I am looking for documentation telling me which XML file I should modify to change the default import of EXCEL to SQL Server 2008R2. I've modified JetToSSIS with no success, nor am I sure this is the file I should modify.

    I've read Importing Data BOL and simply don't see Microsoft documentation about what XML file to modify when EXCEL values are being mapped to an NVARCHAR 255 datatype. I'm hacking but would like to learn more about Microsoft's documentation for this aspect of SSIS.

    I also invite explicit advice on how to better present this challenge to illicit a more favorable response from the community.

    John

    SQL 2012 Standard VPS Windows 2012 Server Standard

  • July 22, 2015 at 11:52 am

    #1814179

    Johnny B (7/22/2015)

    Thanks Alvin,

    I've looked at the etiquette page and gather my question is not clear. It's certainly a challenge to articulate. I am looking for documentation telling me which XML file I should modify to change the default import of EXCEL to SQL Server 2008R2. I've modified JetToSSIS with no success, nor am I sure this is the file I should modify.

    I've read Importing Data BOL and simply don't see Microsoft documentation about what XML file to modify when EXCEL values are being mapped to an NVARCHAR 255 datatype. I'm hacking but would like to learn more about Microsoft's documentation for this aspect of SSIS.

    I also invite explicit advice on how to better present this challenge to illicit a more favorable response from the community.

    John

    I understood what you're asking. I was just saying that's it's not the approach I would use to get imports to work my way.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • July 22, 2015 at 2:15 pm

    #1814222

    Alvin, I can understand the wisdom in creating the SSIS package even though it's a one time import. Just to have something persistent so the tweaks can be made rather than running the wizard over and over.

    I never did figure out how to change the default target datatype for an excel 'general' field. I manual changed it from 255 to 2000 for each column. Not feeling very savvy. So in conclustion, JetToSSIS does NOT seem to govern defaults for Excel.

    Maybe I'll have more time later to figure it out. In the meantime. Task complete.

    John

    SQL 2012 Standard VPS Windows 2012 Server Standard

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply