Import to SQL Server with format file and bulk insert

Hi everyone, I have a tricky problem using a command BULK INSERT

when trying to import data from a text file.

I have found many articles and examples on the Internet about importing using the program BULK INSERT

or BCP

, but they do not help me.

Problem:
I am doing an export from Oracle to a column {#}

delimited <#>

and row delimited text file and import it into SQL Server.

Table (SQL Server):

CREATE TABLE my_DATA
(
ID_PK     NUMERIC(30)  NOT NULL ,
BEGIN_TIME    DATETIME  NULL ,
END_TIME      DATETIME  NULL
);

      

        
        
        
      

    

for Oracle:

CREATE TABLE my_DATA
(
ID_PK     NUMBER(30)  NOT NULL ,
BEGIN_TIME    TIMESTAMP  NULL ,
END_TIME      TIMESTAMP  NULL
);

      

        
        
        
      

    

Delimited file:

ID_PK{#}BEGIN_TIME{#}END_TIME<#>296167{#}01/01/2012 01:30:00.000{#}01/01/2012 02:00:00.000<#>296178{#}01/01/2012 02:00:00.000{#}01/01/2012 02:30:00.000<#>

      

        
        
        
      

    

File format:

9.0                         
3                           
1   SQLNUMERIC  0   19  {#} 1   ID_PK   ""
2   SQLDATETIME 0   8   {#} 2   BEGIN_TIME  ""
3   SQLDATETIME 0   8   <#> 3   END_TIME    ""

      

        
        
        
      

    

So when I used the command:

BULK INSERT my_DATA 
FROM 'D:\my_DATA.txt' 
WITH 
(CODEPAGE = '1251',
 FIELDTERMINATOR = '{#}', 
 FIRSTROW = 2, 
 ROWTERMINATOR = '<#>' );

      

        
        
        
      

    

This works, but when I try to use the format file it doesn't work:

BULK INSERT my_DATA 
FROM 'D:\my_DATA.txt' 
WITH (CODEPAGE = '1251',
      FORMATFILE ='D:\format_file.txt');

      

        
        
        
      

    

Mistake:

Bulk load data conversion failed (type mismatch or invalid character for specified code page) for line 2, column 2 (start_time).

I was looking for this problem, trying to change the date and time to smalldate, trying to change the length to 23 or 24. It won't work. So I am trying to import another table without date, I am using numeric columns and char columns, but faced the same problem with numeric column:

Bulk load data conversion error (type mismatch or invalid character for specified code page) for line 2, column 1 (id_pk).

Table:

CREATE TABLE unit_table
(
id_pk  NUMERIC(30)  NOT NULL ,
name             NVARCHAR(200)  NULL ,
name_full        NVARCHAR(200)  NULL ,
CONSTRAINT  PK_unit_table_2C1 PRIMARY KEY (bule_biz_unit_level_id_pk)
);

      

        
        
        
      

    

file format:

9.0
3
1       SQLNUMERIC    0       1      "{#}"      1     id_pk                              ""
2       SQLNCHAR      0       11     "{#}"      2     name                                              ""
3       SQLNCHAR      0       11     "{#}"      3     name_full                                         ""

      

        
        
        
      

    

The data file only contains 3 lines (the bulk insert command is the same):

ID_PK{#}NAME{#}NAME_FULL<#>1{#}factory{#}factory<#>2{#}station{#}station<#>

      

        
        
        
      

    

I wonder if the file only contains 2 lines:

ID_PK{#}NAME{#}NAME_FULL<#>1{#}factory{#}factory<#>

      

        
        
        
      

    

Bulk insert result:

(0 lines (lines) affected)

I am also trying to make an example http://msdn.microsoft.com/en-us/library/ms178129.aspx but am running into an error:

Unable to bulk load because file "D: \ myTest.txt" could not be read. Operating system error code (null).

And lastly, I tried to use this with a program BCP

and a buggy face too.

Can anyone help me with my problem or give me at least one working example with bulk insert and formatted files.

PS I am using MS SQL Server 2005 updated to the latest version. OS - Windows 7 x64.