July 14, 2015 at 11:51 pm
We are importing data from CSV files into SQL tables, where every column matches.
To do this we are using flat file connection managers. This requires manually inputting each field name and its data type. We have over 150 new tables to be added, with 30-50 fields per table. The csv file layouts match the SQL database tables field to field.
Is there any way to have the Connection Managers be created automatically - to copy the layout of the SQL tables? it would save a lot of development time.
(Suggest types option does not do enough - firstly some of the data columns are nulls so the datatype cannot be predicted, and also the fields names are not automatically added through Suggest Type - so we still need to type each one in manually.)
July 15, 2015 at 2:01 am
mberman (7/14/2015)
We are importing data from CSV files into SQL tables, where every column matches.To do this we are using flat file connection managers. This requires manually inputting each field name and its data type. We have over 150 new tables to be added, with 30-50 fields per table. The csv file layouts match the SQL database tables field to field.
Is there any way to have the Connection Managers be created automatically - to copy the layout of the SQL tables? it would save a lot of development time.
(Suggest types option does not do enough - firstly some of the data columns are nulls so the datatype cannot be predicted, and also the fields names are not automatically added through Suggest Type - so we still need to type each one in manually.)
Perhaps with BIML script. But if you're looking for a quick and easy solution, you're out of luck.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 15, 2015 at 6:12 pm
Agree with Phil here - Biml is the way to go. It'll take some time to build it out, but once the Biml code is in your library you'll be surprised how much it'll come in handy.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply