November 5, 2014 at 9:32 pm
Comments posted to this topic are about the item The Import Wizard
November 6, 2014 at 12:52 am
I didn't notice at first why the import would fail. After all, 150 characters fit perfectly in a 200 character-wide column.
Then I remember the default for the flat file connection manager.
Great question!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 6, 2014 at 2:46 am
Just took a blind guess at this one because I've never used the wizard--I'd import a flat file using BULK INSERT and then spend hours reading through the error tables trying to figure out why it didn't work! 🙂
November 6, 2014 at 3:26 am
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.
Tom
November 6, 2014 at 3:32 am
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...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 6, 2014 at 3:36 am
Nice question, it reminded me when i used this wizard 1st time for flat file :-).
Thanks for sharing.
November 6, 2014 at 3:47 am
twin.devil (11/6/2014)
Nice question, it reminded me when i used this wizard 1st time for flat file :-).Thanks for sharing.
The problem is that only the wizard has this issue, but SSIS itself as well (because they use the same mechanisms behind the scenes). Annoying... 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 6, 2014 at 3:59 am
Koen Verbeeck (11/6/2014)
twin.devil (11/6/2014)
Nice question, it reminded me when i used this wizard 1st time for flat file :-).Thanks for sharing.
The problem is that only the wizard has this issue, but SSIS itself as well (because they use the same mechanisms behind the scenes). Annoying... 😀
it would be off topic but anyways, when i saved the wizard package 1st time, i found the existence of SSIS. 😀
so thanks for creating this wizard :hehe:
November 6, 2014 at 4:36 am
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. I wonder if it's to minimise memory used to help speed up the import.
The target is selected after the source is defined but I can't think of a reason why it couldn't recalculate the metadata once the target is selected.
Nice Q Steve, had to read it a few times before the lightbulb illuminated!
November 6, 2014 at 5:24 am
+1
Thanks for the question.
---------------
Mel. 😎
November 6, 2014 at 6:08 am
Koen Verbeeck (11/6/2014)
I didn't notice at first why the import would fail. After all, 150 characters fit perfectly in a 200 character-wide column.Then I remember the default for the flat file connection manager.
Great question!
Same here. I had to read it through a couple times.
November 6, 2014 at 7:43 am
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.
Good point, Tom. I agree, it should work that way. That sounds like a good idea for a Microsoft Connect suggestion.
Nice question, Steve. Thanks.
November 6, 2014 at 8:07 am
Koen Verbeeck (11/6/2014)
I didn't notice at first why the import would fail. After all, 150 characters fit perfectly in a 200 character-wide column.Then I remember the default for the flat file connection manager.
Great question!
+1
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 6, 2014 at 11:12 am
Excellent question, I do not use ssis for flat files much so I was one who learned something about this today.
Not all gray hairs are Dinosaurs!
November 6, 2014 at 1:40 pm
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
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply