December 8, 2010 at 3:02 am
My company does a lot of loading of fixed-width flat files into SQL Server. Typically this entails the manual creation of a SQL "target" table - which can be cumbersome when said table is particularly wide (think 100+ columns).
The SSIS flat-file source does a good enough job inferring the fixed-width file schema (eg. field length, inferred data type) but the OLE-DB source destination does it's thing in setting all intended columns to 50 CHAR data types / field lengths - which rarely does the trick.
Anyone have a good work-around for this issue?
Peace. 🙂
- Savage
December 9, 2010 at 3:14 am
Anyone seen this issue?
December 9, 2010 at 9:42 am
Just so you're not left in the wind...
Yes, I've seen this issue regularly. Unfortunately, I don't have an easy fix for you. It falls under the 'grunt work' category that if there's enough of, I'll talk to my management about taking on a junior developer to handle the majority of. It's a lot cheaper then having me or a peer do it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 9, 2010 at 5:45 pm
Craig Farrell (12/9/2010)
Just so you're not left in the wind...Yes, I've seen this issue regularly. Unfortunately, I don't have an easy fix for you. It falls under the 'grunt work' category that if there's enough of, I'll talk to my management about taking on a junior developer to handle the majority of. It's a lot cheaper then having me or a peer do it.
Thanks Craig. Unfortunately, we're a small company and even remedial tasks of this nature end-up on pretty much mine or my (small) team's radar daily. No one to delegate it downward to 🙂
December 10, 2010 at 9:17 am
Hi, I have seen the same situation. If you have consistency of your OLE DB sources, you can make a one time edit to the OLE DB connection manager for all text (string) fields. Change the Data type to WSTR and Size 4000. 4000 is the max, you can size them according to each field if you want too.
Bill
December 11, 2010 at 9:05 am
wcm (12/10/2010)
Hi, I have seen the same situation. If you have consistency of your OLE DB sources, you can make a one time edit to the OLE DB connection manager for all text (string) fields. Change the Data type to WSTR and Size 4000. 4000 is the max, you can size them according to each field if you want too.Bill
That's not a bad solution, Bill. It won't quite get us to where the file format is truly passed into SQL, but at least it will allow us to quickly load and interrogate the data within SQL.
Thanks much!!
December 11, 2010 at 10:11 am
wcm (12/10/2010)
Hi, I have seen the same situation. If you have consistency of your OLE DB sources, you can make a one time edit to the OLE DB connection manager for all text (string) fields. Change the Data type to WSTR and Size 4000. 4000 is the max, you can size them according to each field if you want too.Bill
One more ? here Bill - where within SSIS do I set this value such that by default when I choose the OLE DB destination control and SSIS seeks to build a new table it will default to the larger WSTR 4000 column instead of the default STR 50 value?
December 13, 2010 at 7:38 am
1. Add a Data Flow Task to the Control Flow.
a)dbl-Click to enter the Data Flow Tab
2. Add a Flat File Source to the Data Flow Tab
a)Identify the Flat File Source in the Flat File Source Editor Dialog
3. Now That you have the Connection manager for the for the flat file created:
a)edit the Flat File Conn Manager
b)View the Advance Properties and you will see "Data Type" property and it's default is
String [DT_STR] and "OutputColumnWidth" is set to 50.
c) To change one column you can highlight the column in the middle list box, and make the change
d) you can also do multiplt selects to do several columns at once.
My first reply jumped ahead of this step. my appologies.
You need to use WSTR in case you have other transformations to do in SSIS on those text fields.
After this import you should then be able to connect to your OLE Database Destination.
I hope this makes more sense.
Bill
December 13, 2010 at 5:17 pm
Excellent. Thank you Sir!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply