CODEWITHSUNDEEP
sql-servert-sqlssis
rmdussa
rmdussa

Reputation: 1557

isdate function in ssis derived component

Is there any way to check Date(like isDate function in TSQL) column in SSIS package derived column expression after extraction from Sourcefile before loading to target dtabase?

Thanks

Upvotes: 2

Views: 13632

Answers (5)

onupdatecascade
onupdatecascade

Reputation: 3366

See also http://www.sqlis.com/sqlis/post/Expression-Date-Functions.aspx > "IsDate workaround" for a technique that can be adapted

Upvotes: 1

Mubarak
Mubarak

Reputation: 1

You can check whether your variable has a date or not using a conditional statement like this: 

testDateVariable?true:false

For example, if date > 2 then it is true (and put the date, or format the date as you wish). If it is false, put null (you replace true with the date format and false with null).

All this is in a drived column in SSIS.

Upvotes: 0

John H
John H

Reputation: 21

I had a similar issue. I had a date/time in a text file, but the field had a dash between the date and the time. I created a derived column to do a replace on the dash:

REPLACE([TimeField], "- ", "")

I then added a convert column to convert the field to a date. I chose to ignore errors. I then added another Derived Column to check if the converted field was NULL, indicating that it could not convert the value. If it was, I had it set to the current date.

Upvotes: 2

HLGEM
HLGEM

Reputation: 96562

There is a a data conversion task you can drop in. Then redirect the rows as needed, either failing the import entirely or redircting the rows that don't work.

Or you could try a conditional split wher eyou cast the field to a date data type and then send the failures along another path (either deleting the records or nulling out the field would be the common action.)

Upvotes: 1

Kevin LaBranche
Kevin LaBranche

Reputation: 21078

there is no built in function but you can run a script task and use vb.net code to check if the column is a date and operate on it as you wish...

Upvotes: 2

Related Questions