Having problems importing CSV into table

I'm trying to import a CSV file into a table in order to do some queries. Eventually I'll automate this with SSIS to run daily. Unfortunately though, I'm getting an error.

I've run the task import data. Then chose my flat file, Under advanced I set all the types based on the types in the DB (this flat file will replace a table, so types should be the same). And I did check that the first row contains column names.

The type of my first column in Int in the DB, so in the data source, I tried DT_Numeric and DT_UI8. However, when I do the import, I get the following errors:

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column ""BLTO-NUM"" returned status value 2 and status text "The value could not be converted because of a potential loss of data.". (SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column ""BLTO-NUM"" (10)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column ""BLTO-NUM"" (10)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)

Any ideas as to what could be causing this? The field stays numeric.

Thanks, Eric

I would bring the data into a stage first and then change the data types with a simple cast statement. So for example you create a table with a bunch of varchar(100) fields. This will be your stage destination. Not the most efficient but it will only be populated for a minute. Then do an INSERT INTO statement with a SELECT into your destination target table. This is the Transform portion of ETL. CAST all your fields to the data type and data length described in your second target table

Here's an example although not relevant, hopefully should help describe my prose.

INSERT INTO [Staging].[EDITestCaseData]
           ([FileID]
           ,[ImportRecordID]
           ,[TestCaseID]
           ,[TestID]
           ,[TransmissionID]
           ,[GroupID]
           ,[SetID]
           ,[LoopID]
           ,[FileType]
           ,[FilePopulation]
           ,[DataDescription]
           ,[InterchangeReceiverID]
           ,[InterchangeDate]
           ,[InterchangeTime]
           ,[InterchangeControlNumber]
           ,[ApplicationSendersCode]
           ,[ApplicationReceiversCode]
           ,[FunctionalGroupDate]
           ,[FunctionalGroupTime]
           ,[FunctionalGroupControlNumber]
           ,[TransactionSetControlNumber]
           ,[SegmentDate]
           ,[SegmentTime]
           ,[MaintenanceTypeCode]
           ,[MaintenanceReasonCode]
           ,[EmploymentStatuscode]
           ,[RecipientID]
           ,[Site]
           ,[CaseIDNumber]
           ,[MedicaidBegin]
           ,[MedicaidEnd]
           ,[RecipientLastName]
           ,[RecipientFirstName]
           ,[TelephoneNumber]
           ,[CorrectedRecipientLastName]
           ,[CorrectedRecipientFirstName]
           ,[CaseAddress1]
           ,[CaseAddress2]
           ,[CaseCity]
           ,[CaseState]
           ,[CaseZip]
           ,[RecipientBirthdate]
           ,[RecipientGenderCode]
           ,[RaceEthnicityCode]
           ,[SpanishLanguage]
           ,[UseOfLanguageIndicator]
           ,[OldRecipientLastName]
           ,[OldRecipientFirstName]
           ,[OldRecipientBirthdate]
           ,[OldRecipientGenderCode]
           ,[CaseLastName]
           ,[CaseFirstName]
           ,[ResponsiblePersonTelephoneNumber]
           ,[InsuranceLineCode]
           ,[PlanCoverageDescr]
           ,[RetroactiveFlag]
           ,[BenefitBeginDate]
           ,[BenefitEndDate])
 SELECT
       CAST([FileID] as int)
      ,CAST([ImportRecordID] as int)
      ,CAST([TestCaseID] as varchar(50))
      ,CAST([TestID] as varchar(10))
      ,null as [TransmissionID]
      ,null as [GroupID]
      ,null as [SetID]
      ,null as [LoopID]
      ,CAST([FileType] as varchar(50))
      ,CAST([FilePopulation] as varchar(50))
      ,CAST(REPLACE([DataDescription], '"','') as varchar(5000)) as DataDescription
      ,CAST([InterchangeReceiverID] as varchar(15))
      ,CAST([InterchangeDate] as varchar(6))
      ,CAST([InterchangeTime] as varchar(4))
      ,CAST([InterchangeControlNumber] as varchar(9))
      ,CAST([ApplicationSendersCode] as varchar(15))
      ,CAST([ApplicationReceiversCode] as varchar(15))
      ,CAST([FunctionalGroupDate] as varchar(8))
      ,CAST([FunctionalGroupTime] as varchar(6))
      ,CAST([FunctionalGroupControlNumber] as varchar(9))
      ,CAST([TransactionSetControlNumber] as varchar(9))
      ,CAST([SegmentDate] as varchar(8))
      ,CAST([SegmentTime] as varchar(6))
      ,CAST([MaintenanceTypeCode] as varchar(3))
      ,CAST([MaintenanceReasonCode] as varchar(3))
      ,CAST([EmploymentStatusCode] as varchar(50))
      ,CAST([RecipientID] as varchar(50))
      ,CAST([OldSite] as varchar(50)) as Site
      ,CAST([CaseIDNumber] as varchar(50))
      ,CAST([MedicaidBegin] as DATE)
      ,CAST([MedicaidEnd] as DATE)
      ,CAST([RecipientLastName] as varchar(50))
      ,CAST([RecipientFirstName] as varchar(50))
      ,CAST([TelephoneNumber] as varchar(50))
      ,CAST([CorrectedRecipientLastName] as varchar(50))
      ,CAST([CorrectedRecipientFirstName] as varchar(50))
      ,CAST([CaseAddress1] as varchar(50))
      ,CAST([CaseAddress2] as varchar(50))
      ,CAST([CaseCity] as varchar(50))
      ,CAST([CaseState] as varchar(50))
      ,CAST([CaseZip] as varchar(50))
      ,CAST([RecipientBirthdate] as varchar(50))
      ,CAST([RecipientGenderCode] as varchar(50))
      ,CAST([RaceEthnicityCode] as varchar(50))
      ,CAST([SpanishLanguage] as varchar(50))
      ,CAST([UseOfLanguageIndicator] as varchar(50))
      ,CAST([OldRecipientLastName] as varchar(50))
      ,CAST([OldRecipientFirstName] as varchar(50))
      ,CAST([OldRecipientBirthdate] as varchar(50))
      ,CAST([OldRecipientGenderCode] as varchar(50))
      ,CAST([CaseLastName] as varchar(50))
      ,CAST([CaseFirstName] as varchar(50))
      ,CAST([ResponsiblePersonTelephoneNumber] as varchar(50))
      ,CAST([InsuranceLineCode] as varchar(50))
      ,CAST([PlanCoverageDescr] as varchar(50))
      ,CAST([RetroactiveFlag] as varchar(50))
      ,CAST([BenefitBeginDate] as DATE)
      ,CAST([BenefitEndDate] as DATE)
  FROM [GenesisStaging].[Staging].[EDITestCaseData2]