Hi folks,
I guess I'm having a very hard time working how this error can even arise.

here's a table in SQL Server 2005
CREATE TABLE [dbo].[TestSSISForErrors](
[TextColumn10] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[NumericColumnFloat] [float] NOT NULL,
[ColumnWithDt] [datetime] NOT NULL
) ON [PRIMARY]


I've designed a really simple example with a text, float and date column and
I'm experimenting with SSIS to see what happens when I pass in bad data (I'm
trying the various options -> redirect row (after a conversion data flow
task), onError events etc..

basically I want to be able to handle bad data by saying, I know this data
might not be good, I don't want the package to crash.

So I have that table (above) in the SQL Server
and the following sample file designed to break some of the validation of
that table

My Name is Charles|1|12-Jun-2008
Anthea||
This is way to long so will truncate|a|12-Jul-2008
hi|3.14|26-Jan-2008

the package consists of a Flat file data adapter (configured to a flat file
connection manager)
a data flow data conversion task (to convert the strings in the flat file to
the right SQL server types as show above with a redirect row error output to
another text file
a SQL server destination adapter pointing to the database housing
[dbo].[TestSSISForErrors] and to that table in fact.

and the package to bring it in never runs because I ALWAYS get this message

Error 1 Validation error. Import Problematic File: SQL Server Destination
[1311]: The column "ColumnWithDate" can't be inserted because the conversion
between types DT_DBDATE and DT_DBTIMESTAMP is not
supported. TestErrors.dtsx 0 0


2 things
I call the 3rd column in my text file ColumnWithDt within the Text File
manager, but nowhere do I ask for a DT_DBTIMESTAMP,
this field type seems to have arisen all by itself every time I hook up the
Destination database/Table via a SQL Server destination adapter/Connection
manager. And yet as you can see the column type of ColumnWithDt is DateTime
not TimeStamp

my question
where is the DT_DBTIMESTAMP being set, is this a known problem, is there a
workaround?

regards and many thanks in advance,
CharlesA

Hello Charles:

Take out the Data Conversion Transform and hook your Source directly up to
the Destination for this test. In the Destination, change the Data Access
mode from "Table or View - fast load" to "Table or View". This will allow
row-by-row validation of the data. Grab the error output from the Destination
and direct it to a new, clean Text File output. Let BIDS and SSIS figure out
the column meta data for this one; it usually does a pretty good job.

On your Source Adapter, get into the Input and Output properties and make
sure it is REALLY what you want.Try different settings, like DT_DATE instead
of DT_DBDATE.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"CharlesA" wrote:

Hello Charles:

Take out the Data Conversion Transform and hook your Source directly up to
the Destination for this test. In the Destination, change the Data Access
mode from "Table or View - fast load" to "Table or View". This will allow
row-by-row validation of the data. Grab the error output from the Destination
and direct it to a new, clean Text File output. Let BIDS and SSIS figure out
the column meta data for this one; it usually does a pretty good job.

On your Source Adapter, get into the Input and Output properties and make
sure it is REALLY what you want.Try different settings, like DT_DATE instead
of DT_DBDATE.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"CharlesA" wrote:

Hello Charles:

Take out the Data Conversion Transform and hook your Source directly up to
the Destination for this test. In the Destination, change the Data Access
mode from "Table or View - fast load" to "Table or View". This will allow
row-by-row validation of the data. Grab the error output from the Destination
and direct it to a new, clean Text File output. Let BIDS and SSIS figure out
the column meta data for this one; it usually does a pretty good job.

On your Source Adapter, get into the Input and Output properties and make
sure it is REALLY what you want.Try different settings, like DT_DATE instead
of DT_DBDATE.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"CharlesA" wrote:

Hello Charles:

Take out the Data Conversion Transform and hook your Source directly up to
the Destination for this test. In the Destination, change the Data Access
mode from "Table or View - fast load" to "Table or View". This will allow
row-by-row validation of the data. Grab the error output from the Destination
and direct it to a new, clean Text File output. Let BIDS and SSIS figure out
the column meta data for this one; it usually does a pretty good job.

On your Source Adapter, get into the Input and Output properties and make
sure it is REALLY what you want.Try different settings, like DT_DATE instead
of DT_DBDATE.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"CharlesA" wrote:

Hello Charles:

Take out the Data Conversion Transform and hook your Source directly up to
the Destination for this test. In the Destination, change the Data Access
mode from "Table or View - fast load" to "Table or View". This will allow
row-by-row validation of the data. Grab the error output from the Destination
and direct it to a new, clean Text File output. Let BIDS and SSIS figure out
the column meta data for this one; it usually does a pretty good job.

On your Source Adapter, get into the Input and Output properties and make
sure it is REALLY what you want.Try different settings, like DT_DATE instead
of DT_DBDATE.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"CharlesA" wrote:

Hello Charles:

Take out the Data Conversion Transform and hook your Source directly up to
the Destination for this test. In the Destination, change the Data Access
mode from "Table or View - fast load" to "Table or View". This will allow
row-by-row validation of the data. Grab the error output from the Destination
and direct it to a new, clean Text File output. Let BIDS and SSIS figure out
the column meta data for this one; it usually does a pretty good job.

On your Source Adapter, get into the Input and Output properties and make
sure it is REALLY what you want.Try different settings, like DT_DATE instead
of DT_DBDATE.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"CharlesA" wrote:

Hello Charles:

Take out the Data Conversion Transform and hook your Source directly up to
the Destination for this test. In the Destination, change the Data Access
mode from "Table or View - fast load" to "Table or View". This will allow
row-by-row validation of the data. Grab the error output from the Destination
and direct it to a new, clean Text File output. Let BIDS and SSIS figure out
the column meta data for this one; it usually does a pretty good job.

On your Source Adapter, get into the Input and Output properties and make
sure it is REALLY what you want.Try different settings, like DT_DATE instead
of DT_DBDATE.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"CharlesA" wrote:

Hello Charles:

Take out the Data Conversion Transform and hook your Source directly up to
the Destination for this test. In the Destination, change the Data Access
mode from "Table or View - fast load" to "Table or View". This will allow
row-by-row validation of the data. Grab the error output from the Destination
and direct it to a new, clean Text File output. Let BIDS and SSIS figure out
the column meta data for this one; it usually does a pretty good job.

On your Source Adapter, get into the Input and Output properties and make
sure it is REALLY what you want.Try different settings, like DT_DATE instead
of DT_DBDATE.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"CharlesA" wrote:

Hi Todd,
I ALWAYS appreciate it when anyone takes the time to answer a question, so
first off thank you.
I will do that and see where it goes
many thanks again
CharlesA