March 30, 2008 at 7:50 pm
Hi all,
I thought it should be very easy by replacing dts with ssis package.
before having new ssis package in sql server 2005, I think it is easy for me to manually import txt data from management studio and meanwhile I can save it into ssis package, here is my problem.
Our original sql server 2000 database was created with COLLATE
Chinese_Taiwan_Stroke_CI_AS by someone, though we never store any Chinese
character in database. In sql 2000 I never encounter any problem when we
import data from any flat files to any tables with this collation in the database.
Now after I upgrade this database from 2000 to 2005, I encountered a problem
when
I tried to import a flat data file(exact same data import to sql server 2000 ) to sql server 2005 data table with this COLLATE Chinese_Taiwan_Stroke_CI_AS.
(of course when I imported the txt data, I selected locale: Chinese(Taiwan)
The error message is as follow:
Validating (Error)
Messages
• Error 0xc00470b6: Data Flow Task: The LocaleID 1028 is not installed on
this system.
(SQL Server Import and Export Wizard)
• Error 0xc004706b: Data Flow Task: "component "Source - WorldMedExport_txt"
(1)" failed validation and returned validation status "VS_ISBROKEN".
(SQL Server Import and Export Wizard)
• Error 0xc004700c: Data Flow Task: One or more component failed validation.
(SQL Server Import and Export Wizard)
Error 0xc0024107: Data Flow Task: There were errors during task validation.
(SQL Server Import and Export Wizard)
It seems that I have install localeID 1028, can you tell how to get this
Locale for sql 2005?
Thank you.
Betty
March 30, 2008 at 10:49 pm
Hi all,
I finally make it work, but I still need you help to explain something for me.
instead of using destination of import to be microsoft OLE DB provider for sql server and to a table I created before importing
I chose sql native client as destination, make sql server 2005 create a table automatically. if finally imported data to the destiantion table.(and use locale English (United States))
But when I take a look at the table script, it totally different, woder how should I deal with it. It seems that the auot created table cannot hold data I want.
here is table I created and wanted data to be imported in this table but unsuccessful:
CREATE TABLE [dbo].[TIS_WorldMed_DataEase](
[AgentCode] [char](8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[InsuredSurname] [char](20) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
[InsuredFirstname] [char](20) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
[InsuredBirthday] [datetime] NOT NULL,
[Street] [char](40) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[City] [char](25) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[State] [char](2) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Zip] [char](15) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Email] [char](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[DayPhone] [char](15) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[EveningPhone] [char](15) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[InsuredArrivalDate] [datetime] NULL,
[PassportNumber] [char](15) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[PassportCountry] [char](2) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[School] [varchar](30) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Visa] [char](15) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[CurrentExpDate] [datetime] NOT NULL,
[PlanChosen] [char](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
[EffectiveDate] [datetime] NOT NULL,
[SpouseSurname] [char](20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[SpouseFirstname] [char](20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[SpouseBirthday] [datetime] NULL,
[SpouseArrivalDate] [datetime] NULL,
[SpouseEffDate] [datetime] NULL,
[SpouseExpDate] [datetime] NULL,
[Child1Surname] [char](20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Child1Firstname] [char](20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Child1Birthday] [datetime] NULL,
[Child1ArrivalDate] [datetime] NULL,
[Child1EffDate] [datetime] NULL,
[Child1ExpDate] [datetime] NULL,
[Child2Surname] [char](20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Child2Firstname] [char](20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Child2Birthday] [datetime] NULL,
[Child2ArrivalDate] [datetime] NULL,
[Child2EffDate] [datetime] NULL,
[Child2ExpDate] [datetime] NULL,
[Child3Surname] [char](20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Child3Firstname] [char](20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Child3Birthday] [datetime] NULL,
[Child3ArrivalDate] [datetime] NULL,
[Child3EffDate] [datetime] NULL,
[Child3ExpDate] [datetime] NULL,
[total] [int] NOT NULL,
[AMDoption] [varchar](5) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[inoroutbound] [varchar](5) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[CertificateNumber] [char](7) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
[NotRenewable] [char](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Reissue] [char](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Deductible] [char](5) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL CONSTRAINT [DF_TIS_WorldMed_DataEase_Deductible] DEFAULT (''),
[Rider2Select] [char](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL CONSTRAINT [DF_TIS_WorldMed_DataEase_Rider2Select] DEFAULT (''),
[TripDestination] [varchar](100) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL CONSTRAINT [DF_TIS_WorldMed_DataEase_TripDestination] DEFAULT (''),
[Country] [char](2) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Province] [char](25) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
The following is the table auto created by sql server 2005 during import, import data successfully, but it seems that table is completely different from what I created, why?
CREATE TABLE [dbo].[WorldMedExport](
[AgentCode] [bigint] NULL,
[InsuredSurname] [nvarchar](12) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[InsuredFirstname] [nvarchar](11) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[InsuredBirthDay] [datetime] NULL,
[Street] [nvarchar](40) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[City] [nvarchar](18) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[State] [nvarchar](13) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Zip] [nvarchar](6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Email] [nvarchar](30) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[DayPhone] [nvarchar](14) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[EveningPhone] [nvarchar](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[InsuredArrivalDateInsured] [datetime] NULL,
[PassportNumber] [nvarchar](9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[PassportCountry] [nvarchar](2) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[School] [nvarchar](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Visa] [nvarchar](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[CurrentExpDate] [datetime] NULL,
[PlanChosen] [nvarchar](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[EffectiveDate] [datetime] NULL,
[SpouseSurname] [nvarchar](8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[SpouseFirstname] [nvarchar](11) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[SpouseBirthday] [datetime] NULL,
[SpouseArrivalDate] [datetime] NULL,
[SpouseEffDate] [datetime] NULL,
[SpouseExpDate] [datetime] NULL,
[Child1Surname] [nvarchar](7) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Child1Firstname] [nvarchar](10) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Child1Birthday] [datetime] NULL,
[Child1ArrivalDate] [datetime] NULL,
[Child1EffDate] [datetime] NULL,
[Child1ExpDate] [datetime] NULL,
[Child2Surname] [nvarchar](7) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Child2Firstname] [nvarchar](11) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Child2Birthday] [datetime] NULL,
[Child2ArrivalDate] [datetime] NULL,
[Child2EffDate] [datetime] NULL,
[Child2ExpDate] [datetime] NULL,
[Child3Surname] [nvarchar](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Child3Firstname] [nvarchar](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Child3Birthday] [nvarchar](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Child3ArrivalDate] [nvarchar](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Child3EffDate] [nvarchar](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Child3ExpDate] [nvarchar](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Total] [real] NULL,
[AMDOption] [nvarchar](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[inoroutbound] [nvarchar](15) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[CertificateNumber] [smallint] NULL,
[NonRenewable] [nvarchar](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Reissue] [nvarchar](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Deductible] [smallint] NULL,
[Rider2Select] [nvarchar](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[TripDestination] [nvarchar](26) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Province] [nvarchar](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[HomeCountry ] [nvarchar](2) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply