Problems importing CSV into a table
I am trying to import a CSV file into a table in order to do some queries. Eventually, I will automate this with SSIS to run it daily. Unfortunately, however, I am getting an error.
I ran the task import data. Then selected my flat file. In the "Advanced" section, I have set all types based on the types in the database (this flat file will replace the table, so the types must be the same). And I actually checked that the first row contains the column names.
The type of my first column is Int in the DB, so in the datasource 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. Conversion data for column "BLTO-NUM" "state return value 2 and status text" The value could not be converted due to potential data loss. ". (SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. "Output Column" BLTO-NUM "" (10) "failed because error code 0xC0209084 occurred and the error string is located on" Output Column "" BLTO-NUM "" (10) "indicates an error. An error occurred in the specified object of the specified component. Prior to this, error messages may appear with more information about the failure. (SQL Server Import and Export Wizard)
Any ideas as to what might be causing this? The field remains numeric.
Thanks Eric
source to share
I would first cast the data into a stage and then change the data types with a simple statement operator. So, for example, you create a table with a bunch of fields varchar(100)
. This will be your destination stage. Not the most efficient, but it will be populated for a minute. Then execute the INSERT INTO
c statement on SELECT
the target target table. This is the Transform part of ETL. CAST
all your fields to the data type and data length described in the second target table
Here's an example, although not relevant, I hope will 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]
source to share