Bulk insert CSV with as last column datatype bit

1k views Asked by At

I´ve created a package in SSIS that eventually has to bulk insert a certain amount of csv files in our database. It works perfectly for all csv files except for one. This csv file has to be inserted in the following table:

USE [CSDW_Base] GO

/****** Object:  Table [dbo].[CSDW_CSV_Diensten]    Script Date: 06/16/2015 09:45:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CSDW_CSV_Diensten](
    [Dienst] [nvarchar](50) NULL,
    [Dienstsoortcode] [nvarchar](5) NULL,
    [Van] [datetime] NULL,
    [Tot] [datetime] NULL,
    [Weekend] [bit] NULL,
    [Feestdag] [bit] NULL
) ON [PRIMARY]

GO

As you can see, the last column is a [bit]. When I try to run the package it gives the following error: Error: Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 6 (Feestdag).

For the bulk insert I use this query:

BULK INSERT InTableName FROM 'K:\USER\MyName\csv package\Csv bestanden' WITH (FIRSTROW = 2, FIELDTERMINATOR = ';' ,ROWTERMINATOR = ' \r\n')

I'm pretty sure it has something to do with a 'invisible character' or something like that because if I change the table and the CSV in such a way that [Tot] [datetime] is the last column, nothings wrong.

Can you please help me figure out why my package thinks i'm trying to insert more than just a '1' or '0' in a Bit column? And also, can you tell me how to fix it :)

Thanks.

0

There are 0 answers