STR to DATETIME - Source column too short.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

STR to DATETIME - Source column too short.

Author

Topic

readysetstop
Posting Yak Master

123 Posts

Posted - 2007-01-03 : 12:24:45

I have the following sample text:


123456    789012    1     Short descriptive text.   01/01/2007
234567    890123    2     Short descriptive text.   01/01/2007
345678    901234    3     Short descriptive text.   01/01/2007
456789    012345    4     Short descriptive text.   01/01/2007
567890    123456    5     Short descriptive text.   01/01/2007

I'm trying to import this file via a DTS data transform to the following table:


CREATE TABLE [AR07] (
	[created] [smalldatetime] NOT NULL CONSTRAINT [DF__AR07_test__creat__22AA2996] DEFAULT (getdate()),
	[submitter] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[provider] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[claims] [int] NULL ,
	[error] [char] (26) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[rpt_date] [datetime] NOT NULL ,
	CONSTRAINT [PK_AR07] PRIMARY KEY  CLUSTERED 
	(
		[submitter],
		[provider],
		[error],
		[rpt_date]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
) ON [PRIMARY]
GO

I'm copying each column individually, starting with source column 1 to destination column submitter. It refuses to import with the error:

DTSTransformCopy: Schema validation failed; see Extended Error information.
TransformCopy 'DTSTransformation__5 validation error: Source column too narrow to contain a valid value of destination column's datatype for column pair 1 (source column 'Col005' (DBTYPE_STR), destination column 'rpt_date' (DBTYPE_DBTIMESTAMP)).

So I tried using a Datetime transform on the last column, and I get:

The number of failing rows exceeds the maximum specified.
TransformDateTimeString 'DTSTransformation__5', column pair 1 (source column 'Col005' (DBTYPE_STR), destination column 'rpt_date' (DBTYPE_DBTIMESTAMP)): Cannot parse input data string beginning at '01/01/2007'

The thing is, this file worked fine a couple of weeks ago. What changed was: 1, the file is now being brought to the server via NDM instead of FTP, and 2, NDM strips trailing spaces off the end of each line. I have since had the file re-sent with the trailing spaces intact, but I still get the same errors.

Any ideas?

readysetstop
Posting Yak Master

123 Posts

Posted - 2007-01-03 : 12:55:18

Update: We were able to get the file to load when we added a leading space to the date field. I would really like to know why that fixed it.

Thanks.

-d.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-03 : 13:56:54

sounds like transform was fixed width as opposed to delimeted?

readysetstop
Posting Yak Master

123 Posts

Posted - 2007-01-04 : 06:54:15

Russel,

That's correct. It was fixed width. What would the difference be if it was delimited?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-04 : 08:36:48

fixed width assumes data to be in a specified position in the source. delimeted lets mssql identify start/end of fields not by position but by delimitng the data with a character.

in your case, mssql was looking where u told it to, but data was off by one character.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources