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:

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?

TIA

PeterFor 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.

My MDF Files currupted

My company have server with loads of test databases. last saturday its
hard disk crashed all the partitions were lost . i tried to recover
data from that disk and found my database folder. I took backup on
another harddisk and tried to attache my databases but sql server says
it not avalid database file. i tried to repaire those files with
"recovery for sql server" but no use please help.
Tough thing...
If the database file is corrupt, and you cannot restore from a good backup,
reinstalling the software cannot help. also, re-attaching the database files
will not (cannot) solve the corruption.
Try =>
sp_attach_single_file_db
Attaches a database having only one data file to the current server.
if in suspect state => to reset the suspect status:
http://msdn.microsoft.com/library/de...tabse_494j.asp
once you can reaccess the db, try DBCC CHECKDB:
http://msdn.microsoft.com/library/de..._dbcc_00gy.asp
http://www.experts-exchange.com/Data..._21648997.html
Thanks,
Sree
"khandelwal.deep@.gmail.com" wrote:

> My company have server with loads of test databases. last saturday its
> hard disk crashed all the partitions were lost . i tried to recover
> data from that disk and found my database folder. I took backup on
> another harddisk and tried to attache my databases but sql server says
> it not avalid database file. i tried to repaire those files with
> "recovery for sql server" but no use please help.
>

My MDF Files currupted

My company have server with loads of test databases. last saturday its
hard disk crashed all the partitions were lost . i tried to recover
data from that disk and found my database folder. I took backup on
another harddisk and tried to attache my databases but sql server says
it not avalid database file. i tried to repaire those files with
"recovery for sql server" but no use please help.Tough thing...
If the database file is corrupt, and you cannot restore from a good backup,
reinstalling the software cannot help. also, re-attaching the database files
will not (cannot) solve the corruption.
Try =>
sp_attach_single_file_db
Attaches a database having only one data file to the current server.
if in suspect state => to reset the suspect status:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_494j.asp
once you can reaccess the db, try DBCC CHECKDB:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_00gy.asp
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21648997.html
Thanks,
Sree
"khandelwal.deep@.gmail.com" wrote:
> My company have server with loads of test databases. last saturday its
> hard disk crashed all the partitions were lost . i tried to recover
> data from that disk and found my database folder. I took backup on
> another harddisk and tried to attache my databases but sql server says
> it not avalid database file. i tried to repaire those files with
> "recovery for sql server" but no use please help.
>

My MDF Files currupted

My company have server with loads of test databases. last saturday its
hard disk crashed all the partitions were lost . i tried to recover
data from that disk and found my database folder. I took backup on
another harddisk and tried to attache my databases but sql server says
it not avalid database file. i tried to repaire those files with
"recovery for sql server" but no use please help.Tough thing...
If the database file is corrupt, and you cannot restore from a good backup,
reinstalling the software cannot help. also, re-attaching the database files
will not (cannot) solve the corruption.
Try =>
sp_attach_single_file_db
Attaches a database having only one data file to the current server.
if in suspect state => to reset the suspect status:
tabse_494j.asp" target="_blank">http://msdn.microsoft.com/library/d...
tabse_494j.asp
once you can reaccess the db, try DBCC CHECKDB:
0gy.asp" target="_blank">http://msdn.microsoft.com/library/d...
0gy.asp
[url]http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21648997.html[/ur
l]
Thanks,
Sree
"khandelwal.deep@.gmail.com" wrote:

> My company have server with loads of test databases. last saturday its
> hard disk crashed all the partitions were lost . i tried to recover
> data from that disk and found my database folder. I took backup on
> another harddisk and tried to attache my databases but sql server says
> it not avalid database file. i tried to repaire those files with
> "recovery for sql server" but no use please help.
>