Issue with date format check in SSIS

Question

Issue with date format check in SSIS

latitiacasta

SSCommitted

Points: 1982
More actions
September 27, 2016 at 1:46 am

#400777

Hi i have csv file with policystartdate before loading data i need to check if its in YYYY-MM-DD format or not. i wrote expression as
"LEFT((DT_STR,19,1252)PolicyStartDate,8) == (DT_STR,4,1252)DATEPART("yyyy",PolicyStartDate) + RIGHT("0" + (DT_STR,4,1252)DATEPART("mm",PolicyStartDate),2) + RIGHT("0" + (DT_STR,4,1252)DATEPART("dd",PolicyStartDate),2) ? PolicyStartDate : (DT_DATE)0" it always return date "1899-12-30 00:00:00.000". How can i check the format and load it in sql server in same format.
Thanks in advance

Viewing 7 posts - 1 through 6 (of 6 total)

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

Spiff SSCertifiable Points: 7766 More actions · Answer 1

Slightly puzzled here - you are using date functions on PolicyStartDate in your expression. Are you importing the column as a DT_DATE already?

Also are you planning on writing the date to SQL server as a text value or date value? If a date then the format you load to SQL server is irrelevant - it's a date type.

latitiacasta SSCommitted Points: 1982 More actions · Answer 2

yes i want to write as date in sql server. but before that i need to check format yyyy-mm-dd other format then filter that data

Spiff SSCertifiable Points: 7766 More actions · Answer 3

Probably the easiest way to do what you are trying is to just pass the string to a derived column transform and try to cast it e.g. (DT_DATE)PolicyStartDate. If it is a valid date format the cast will work otherwise it will error. Use Configure Error Output to redirect errors to error output so the package will not fail and you can do whatever you need with the row.

If you absolutely need to validate the format then SSIS expressions are rubbish and the best way is to use a Regex's in a script task. Good article on this here.

latitiacasta SSCommitted Points: 1982 More actions · Answer 4

latitiacasta

SSCommitted

Points: 1982

September 27, 2016 at 3:40 am

#1903278

Thanks for your help

Phil Parkin SSC Guru Points: 246695 More actions · Answer 5

latitiacasta (9/27/2016)
yes i want to write as date in sql server. but before that i need to check format yyyy-mm-dd other format then filter that data

Redirecting any errors on INSERT (ie, on the destination component) is also fairly easy to do. This avoids the need for any sort of validation in the package itself.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Phil Parkin SSC Guru Points: 246695 More actions · Answer 6

Spiff (9/27/2016)
--
If you absolutely need to validate the format then SSIS expressions are rubbish and the best way is to use a Regex's in a script task. Good article on this here.

As this would be something which would occur in a data flow, it would be a script component rather than a script task.

And rather than using a regular expression, it would perhaps be more intuitive to use something like DateTime.ParseExact.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.