Error on Date Data During Insert

Hi Folx,
I am new to SQL Server and I am struggling with source data for a table with two db_datetime columns. The data can be inserted using native SQL, but errors when I build a Data Flow Container.

Versions:
Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00

Microsoft SQL Server Management Studio
9.00.1399.00

Error:
[Flat File Source [1]] Error: The "output column "extraction_date" (24)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "extraction_date" (24)" specifies failure on error. An error occurred on the specified object of the specified component.

Source Row:

101|100|2006/12/19 23:50:01.00|2006/12/19 23:50:01.00|||

How I got here (in approximate order...):

Using ETL I created a Container in which I created a PackageCreated a Flat File ConnectionCreated a Flat File SourceEdited the columns on the Flat File Connection
database timestamp [DT_DBTIMESTAMP]NOTE: corresponding columns on destination table in SQL Server Management Studio are of type datetime

Created an OLE DB DestinationExecuted the package which returned the above named error.

I mucked about with data types, Derived Columns, Data Conversion, etc and Googled for solutions without success. One of the errors (which I could not duplicate...) I noticed during my "experimentation" was an out-of-memory condtion.

Questions:
01. What is the proper format for my source data?
02. Could this be a memory issue? If so, how do I diagnose it?

In advance, thanks for your help.

Bill

ps: be kind to me...not only am i an old guy, but i'm a unix guy too...

Native SQL that works:

insert into "transaction"
(transaction_id,
org_id,
extraction_date,
create_date,
transaction_size,
profile_count,
version_count)
values
(101,100,
'2006/12/19 23:50:01',
'2006/12/19 23:50:01',
NULL,
NULL,
NULL)Are you sure the flat file source is mapped correctly, with correct lengths, and with a date/time format correct for DB_DBTIMESTAMP?

There should be other errors right next to the one you posted... Can you grab those as well?

Phil|||I also wonder if it's a locale issue within SSIS, or something... That is with respect to the leading year on your DT_DBTIMESTAMP column. Just throwing that out there.|||All the error messages:

[Flat File Source [1]] Error: Data conversion failed. The data conversion for column "extraction_date" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

NOTE: OutputColumnWidth is zero and "grayed out" in the Flat File Connection Manager Editor

[Flat File Source [1]] Error: The "output column "extraction_date" (24)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "extraction_date" (24)" specifies failure on error. An error occurred on the specified object of the specified component.

[Flat File Source [1]] Error: An error occurred while processing file "C:\work\CAPS\SQL\UNL\unloader\cont100.txt" on data row 1.

[DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.|||
Locale ID is English (United States)|||Have you redirected your error output to a data viewer to be able to inspect the error row(s)?

Have you previewed the data in the flat file connection? Does it all look correct? Can you post a sample row of data?|||
Well Phil, thought I was going to be slick and redirect to a data viewer, but I don't know what I did wrong because now my package done disappeared on me...in other words, the structure no longer shows in the "Solution Explorer". I can see my Control Flow, Data Flow, and Connection Managers, but don't know how to now display/execute the package!

Before that happened, I did open the Flat File Connection Manager Editor and the data looked correct when I selected "Columns" and "Preview".

I will reconstruct after lunch...and if you would like to take this discussion offline, you can send e-mail to tampa_dba@.yahoo.com. If / when this gets resolved, then we can post the solution...

|||The solution explorer is buggy at best.

Find the physical package, and move it to another folder... Then, in solution explorer, you can add existing package. Select the moved package, and you'll be all set again. You'll then be able to delete the "moved" file.|||
Okay, it's back. Is this what you want?

101|100|2006/12/19 23:50:01.00|2006/12/19 23:50:01.00|||

'Name''Data Type''Precision''Scale''Length''Code Page''Sort Key Position''Comparison Flags''Source Component'
'transaction_id''DT_I4''0''0''0''0''0''''Flat File Source'
'org_id''DT_I4''0''0''0''0''0''''Flat File Source'
'extraction_date''DT_DBTIMESTAMP''0''0''0''0''0''''Flat File Source'
'create_date''DT_DBTIMESTAMP''0''0''0''0''0''''Flat File Source'
'transaction_size''DT_I4''0''0''0''0''0''''Flat File Source'
'profile_count''DT_I4''0''0''0''0''0''''Flat File Source'
'version_count''DT_I4''0''0''0''0''0''''Flat File Source'|||SSIS does not like the fraction in your timestamp column. If I drop that, the package works fine. Now I'm trying to figure out why.|||
thanks for your help. dropping the fractional worked. i don't like that it worked because the datatype should support fractional time, but it is not a requirement.