Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2006

    Unanswered: n00b: best way to import flat file with BIDS

    Hi all,

    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.

    Current situation:
    1. Created connection managers to csv files
    2. created SQL server destinations pointing to the tables
    3. connected them directly with a dataflow path
    4. 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?



  2. #2
    Join Date
    May 2004
    For doing this kind of work, I prefer to use bcp.exe over SSIS/DTS. It's a simple command line utility.

    Look for "bcp utility" in BOL.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts