September 27, 2016 at 1:46 am
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
September 27, 2016 at 2:46 am
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.
September 27, 2016 at 2:51 am
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
September 27, 2016 at 3:19 am
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.
September 27, 2016 at 3:40 am
Thanks for your help
September 27, 2016 at 5:50 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 27, 2016 at 5:55 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply