i'm using sql server import/export wizard upload several large (>100 gb) flat files sql server. these files delimited, not fixed-width, still have extraneous trailing whitespace within columns:
100,value ,value ,value 1002,value ,value ,value i don't have control on how these files created. unfortunately, whitespace maintained in sql following upload. noticed can append set ansi_padding off; sql query auto-generated wizard, looks changing setting ill-advised. there better means go process? worth doing?
cleaning files in advance not possible, given in total have 10tb of data upload , code take days, possibly weeks, run. imagine running alter column ... ltrim(rtrim([column_name])) going time-intensive well.
the wizard create dtsx file if choose save it. using ssdt, can open package , add derived or transform component trimming while data being imported.
if not want use ssis in way, can update (opposed alter column) trim columns after import.
if choose ssis route, here handy script trim fields: http://microsoft-ssis.blogspot.com/2010/12/do-something-for-all-columns-in-your.html
Comments
Post a Comment