Question about Data Conversion Transformation

Question

Question about Data Conversion Transformation

preity

SSC Enthusiast

Points: 116
More actions
April 19, 2012 at 10:45 am

#149198

Hello,
I'm using Data Conversion Tranformation to convert nvarchar values to datetime and redirecting error output. I noticed that dates like "03/15/0228", which is an invalid date in SQL Server, gets past the transformation without being redirected eventually failing at destination. I wanted to confirm if this this is a known issue or am I missing some advanced setting.
Thanks,
P

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Which SSIS data type are you converting the string to?

See section "Mapping Integration Services Data Types to Database Data Types" on Integration Services Data Types to ensure you are using the correct data type in SSIS.

Note that even after choosing the data type in the article, the range of acceptable values for SSIS data types may not squarely overlay the SQL Server data types...mainly because SSIS data types are based on .NET data types, which are different from SQL Server data types.

You may need to drop into a Script Component to do your work based on rules you manually implement.

If you're on SQL Server 2008 you could also consider using the DATETIME2 type in your SQL Server table, 03/15/0228 is a valid DATETIME2.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

preity SSC Enthusiast Points: 116 More actions · Answer 2

Thanks for the prompt response.

I'm converting it to DT_DBTIMESTAMP.

I used a script component to get it to work, but just wanted to find out if there was some workaround.

--P

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

preity (4/20/2012)
Thanks for the prompt response.
I'm converting it to DT_DBTIMESTAMP.
I used a script component to get it to work, but just wanted to find out if there was some workaround.
--P

When you say "to get it to work" what do you mean? Are you throwing those invalid dates away in the Script Component?

I don't think there is anything you can do short of using a DATETIME2 to accept the data from the Destination Component, else manually handle it in Script, or possibly in a Derived Column using an expression to handle the data as needed.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

preity SSC Enthusiast Points: 116 More actions · Answer 4

The script component is used to redirect the rows to another table. The problem with changing the datatype on a column is it will have a cascading effect and will require major changes.

Thanks,

P