Unanswered: n00b: best way to import flat file with BIDS
I'm totally new to SQL Server 2k5 and need to do something rather basic: import some CSV files into tables. I'm getting translation errors and would like to know what's the best way to cast the strings before inserts.
I'm doing the import in BI Development Studio.
Created connection managers to csv files
created SQL server destinations pointing to the tables
connected them directly with a dataflow path
Ran the packadge: one import went just fine, the other one complains about conversion errors like "Conversion DT_STR and DT_I4 not supported"
Both tables have the same kind of fields (varchar, float, datetime, int)
I looked at converting the data using a transformation but am somewhat confused of which one to use.
What's the best way to transform the data before insert: derived column, import column or data conversion? Or something else I overlooked?