bcp TO SSIS
-
I tried to search this forum for an answer and I apologize in advance if my question has been answered 1000 times before.
With that out of the way, I have a legacy system that produces bcp extract files & format files for loading into SQL Server. These tables have in excess of 300 columns each. I just discovered that bcp is missing rows in the flat files when importing, so I am looking at ssis. What I can't get around is the DT_STR 50 defaults for all the columns in the flat file import wizard.
Is there a simple way to get the correct mappings without typing them all in?
I can reverse the process and do an extract to a flat file and get the database column mappings to the destination flat file. I can even copy the meta data to the clipboard but can't figure out how to get it into the source flat file meta data. Is there a way to do that?
Restated: Is there a way to get the correct column mappings when importing a flat file without typing in each one?
Thanks,
DaveK
-
In the advanced properties of the flat file connection manager, there is an option to "Suggest Column Types".
If you have enough memory, tell it to read the max number of rows to get a big enough sample to have the correct data types.
That should do it for you.
Dan
-
I tried that, the suggestions are all off compared to the database no better than DT_STR 50. I still get hundreds of validation and overflow errors...
On column 74 of 367 typing in from the DB extract metadata ...aaaaahh!!!!!
-
I just discovered that bcp is missing rows in the flat files when importing, ...
Ummm.... why don't you share what the error message was (if there was one), show us the BCP log output (you did create one, didn't you?), and tell us both why you think BCP is missing rows and how you discovered it
...... unless you really, really feel like reinventing the wheel
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I've found BCP is still the easiest way to import fixed width flat files (particulary wide tables). I normally create the .fmt file in Excel then copy to a text file. I've never seen it skip rows before, the only way I can think of this happening is if you have a bad row terminator - which will still be a problem in SSIS. Can you id the rows and see what the terminators are, then compare to your termination settings?
I prefer bcp as I have not found a great way to modify the field lengths or data types easily in SSIS. The suggest types only reads a small subset of the data and typically old data will have issues with datatype constraints (like datetime, numeric ...). When using SSIS on flat files I usually bring the data into staging tables with default string lengths, then move/transform data into better tables with good structures, then truncate the import/staging tables.
Good luck
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply