error output is bringing too many rows with it

I've got a Derived Columns component as part of a data flow. On this I've set the error output for my columns to Redirect Row in all cases. I've set a data watcher on the error output and then ran the package.

There are several rows which I'm expecting to fail - about 3 of them. These fail but there are also another 697 which seem to have no problem. So I fixed one of the problem columns in the source data and then re-ran the package. I only updated one row in the source so this row no longer appeared in the error output, but neither did several hundred of the other rows.

Is it possible that the error output has been tripped for that one row but for some reason it sends several hundred more rows? The ids on these additional rows follow on from the erroneous row, and when I fix that row the rows following it no longer appear in the error output.

The Derived Column transform should giuve your error handling at a row level. One row that fails should not impact the following rows. This just sounds wrong, so there is either something unexpected you've missed when looking at what rows shoudl fail, or there is a bug. Can you reproduce this behaviour on a simple set of data that could be used to log a bug? Perhaps you could send me a sample package?|||

Darren - I've managed to reproduce this with a simple package which reads data from a file. Sent to you via email.

Cheers,

Nick

|||no longer a problem once upgraded to SP2. Thanks Darren.sql

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.

Error Message "Key Sequence columns are not supported at the case level"

Hi group,
I was trying to build a sequence clustering model in MSAS.
I could deploy the model. Then while building the model, I end up getting an error with message "Key Sequence columns are not supported at the case level".

Can some one tell me what it means and how i can avoid it?

Thanks in advance to any kind of help here.
The Microsoft_Sequence_Clustering algorithm requires the sequence
information to be modeled as a nested table so you can only have a nested table column with Content=Key Sequence (right-click on the column in the Mining Structure tab and look at the Properties pane).

In the above case, you will have a case level key that uniquely identifies a sequence set (modelled as multiple nested table rows) and you'll need to mark that with Content=Key and IsKey=True.|||Thanks for your suggestion Raman.
I have made the change that you have suggested and proceeded to the processing step.

Hmm, the processing fails with error msg:
"OLE DB or ODBC error: A column has been specified more than once in the order by list. Columns in the order by list must be unique.; 42000."

The "Key sequence" column in the nested table has been used twice in a query. Query attached in the end for reference.
What could be the cause for this? / How do i make AnSer stop generating this wrong query?
(How do you like my new short name to MSAS? AnSer. Sounds like 'answer') :)

FYI.. the "Key Sequence" column in the nested table has a FK relationship with the "Key" column of the case table.

The processing fails because it runs the query:
SELECT
[nestedTable].[col1]
,[nestedTable].[col2]
,[nestedTable].[col3]
FROM [nestedTable]
ORDER BY [nestedTable].[col1]
,[nestedTable].[col1]
--col1 is a sequence key and has FK relation with the case table.|||The "Key Sequence" is the key of the nested table, while the foreign key
should be the column you used to join case table
and nested table. These 2 columns should be 2 seperate columns in the nested
table. The following is an example:

SeqClustCase -- Case table
UserId (Key)
A1
A2

SeqClustNested -- Nested table
UserId (foreign key)
Order(Key sequence)
Sequence (sequence data)

Please try fix your data source view and mining structure according to the
above example.

Thanks,|||

Actually the foreign key column is implicitly included as part of the bindings for the nested table when you use BI Dev Studio and you don't need to specify it as a column in the nested table.

Can you please post your mining structure and model definitions?