Error on Date Data During Insert

tampa_dba


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...):

1. Using ETL I created a Container in which I created a Package
2. Created a Flat File Connection
3. Created a Flat File Source
4. Edited the columns on the Flat File Connection
1. database timestamp [DT_DBTIMESTAMP]
2. NOTE: corresponding columns on destination table in SQL Server Management Studio are of type datetime
   
5. Created an OLE DB Destination
6. Executed 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)

Phil Brammer


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

Phil Brammer

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.

tampa_dba

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.

tampa_dba


Locale ID is English (United States)

Phil Brammer

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

tampa_dba


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...

Phil Brammer

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.

tampa_dba


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'

Phil Brammer

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.

tampa_dba


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.