The Import Wizard

  • TomThomson

    SSC Guru

    Points: 104773

    Koen Verbeeck (11/6/2014)


    TomThomson (11/6/2014)


    Good question.

    It raises an issue about the quality of this wizard: why on earth doesn't it read metadata for the target table and set lengths accordingly? It shouldn't be a permissions issue, because if I can write into a table I'd better be allowed to discover the column widths. So this wizard seems poorly thought out.

    There is such a feature in the Advanced section. There you can click a button and it will scan the flat file and determine the column widths.

    Not exactly a great solution...

    That would be OK if the idea was to create a table which could hold teh data in the flat file. If I'm importing into a pre-existing table it would be better to use the widths determined by that table, and then truncating or failing (for both variable length column types and fixed length types) and padding or failing (only for fixed length types) would be options that made sense; with width determined by the flat file those options can't be provided because neither truncation nor padding would be in teh import code, they would be in an implicit conversion, which can not be made to fail on pad nor to succeed on truncate.

    Tom

  • paul.goldstraw

    SSCrazy

    Points: 2566

    As I recall part of the problem is it depends where in the file the longer records are. My recollection is that the first few records (I don't know how many exactly) form the basis for the source sizes, so if the longer values are at the top, the problem doesn't occur. This can cause a real headache for troubleshooting as some imports will appear to work and others won't, for no apparent reason.

  • Ed Wagner

    SSC Guru

    Points: 286982

    paul.goldstraw (11/7/2014)


    As I recall part of the problem is it depends where in the file the longer records are. My recollection is that the first few records (I don't know how many exactly) form the basis for the source sizes, so if the longer values are at the top, the problem doesn't occur. This can cause a real headache for troubleshooting as some imports will appear to work and others won't, for no apparent reason.

    That problem goes back a long way. I never use the wizard because I find BULK INSERT to be both reliable and repeatable.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720371

    TomThomson (11/6/2014)


    Good question.

    It raises an issue about the quality of this wizard: why on earth doesn't it read metadata for the target table and set lengths accordingly? It shouldn't be a permissions issue, because if I can write into a table I'd better be allowed to discover the column widths. So this wizard seems poorly thought out.

    Indeed

  • Hany Helmy

    SSChampion

    Points: 13488

    My 1st guess was correct, good to know somthing new, as I never used flat file as a data source b4 in this wizard, thanx 4 the question.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    TomThomson (11/6/2014)


    Good question.

    It raises an issue about the quality of this wizard: why on earth doesn't it read metadata for the target table and set lengths accordingly? It shouldn't be a permissions issue, because if I can write into a table I'd better be allowed to discover the column widths. So this wizard seems poorly thought out.

    I agree. We shouldn't have to keep changing that field. In fact, what's wrong with just keeping the default at some humungous value? Isn't it just a max value allowed limitation?

  • Rich Mechaber

    SSChampion

    Points: 10935

    TomThomson (11/6/2014)


    Good question.

    It raises an issue about the quality of this wizard: why on earth doesn't it read metadata for the target table and set lengths accordingly? It shouldn't be a permissions issue, because if I can write into a table I'd better be allowed to discover the column widths. So this wizard seems poorly thought out.

    And don't get me started with the problems I've had where SQL Server reads metadata from a portion of my Excel or flat files when running an OPENROWSET query against them. The not infrequent result is a type conversion error or truncation issue further down the file.

    Thankfully, IMEX=1 (Excel) and MaxScanRows=0 (flat files, more info. here) force SQL Server to scan the entire file. Then I can at least pull everything into a staging table and run data checks with T-SQL.

    Rich

  • SQL_Hunt

    SSC-Dedicated

    Points: 33450

    Nice Question, Steve. Good One. Thanks.

    Thanks.

Viewing 8 posts - 16 through 23 (of 23 total)

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