I want to use bulk insert to load data from a main frame to SQL server 2005. It is a flat file with no delimiters. over 100 columns. Does anyone have an example of the code how to do this? I assume i will need a FMT file for the column definition. Please don't say to use SSIS. I already know how to do that. Regards Scott

Hey Scott Is this a fixed width file? The Flat file connection allows either delimited, fixed width, or ragged right formats. You can setup the file metadata with a Flat File Source and move on from there. http://msdn.microsoft.com/en-us/library/ms140266.aspx Here is some information on the bulk insert task in SSIS, which you might be able to use. http://msdn.microsoft.com/en-us/library/ms141239.aspx For the bulk insert I think nearly most of the database destination objects support that. Hope this helps! Jon

Drop Table Test Create Table Test ( Company Varchar(50) ,DeliveryAddress Varchar(52) ,City Varchar(20) ,Fill Varchar(Max) ) Bulk Insert Test From 'f:\U2765349_1_UL.fix' With (RowTerminator = '\r' ,FormatFile='f:\data.fmt') Select * From Test --Format File 9.0 4 1 SQLCHAR 0 50 "" 1 [Company] SQL_Latin1_General_CP1_CI_AS 2 SQLCHAR 0 52 "" 2 [DeliveryAddress] SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 0 20 "" 3 [City] SQL_Latin1_General_CP1_CI_AS 4 SQLCHAR 0 630 "\r" 4 [Fill] SQL_Latin1_General_CP1_CI_AS http://www.sqlservercentral.com/Forums/Topic604491-338-1.aspx Thanks Scooby. I found the following code on sqlservercentral and it works great.

Bah! I'm sorry Scott I didn't know you were talking about the old school BCP/Bulk Insert commands. When you said you didn't want to hear "use SSIS" I thought you were asking for a little more detail in an answer. Jon