How to replace a string value with db null?

Question

I have an ssis package that imports a view from one database into different db and places the data into a table. The view has all datatypes set to varchar which I cannot change. Some of these varchars are really datetime values. The view has set all NULL values to N/A and i want to replace the N/A with db null. The table I am importing the data into has the date columns set to datetime rather than varchar and I cannot insert N/A into it. This is my first ssis package and I'm having some issues with getting an expression setup to accomplish this. What would the string replace expression look like to make this happen? Thanks.

Answer 1

I was going about this the wrong way. The soultion is just to use as sql query to update the data rather than an expression.

Answer 2

I haven't tested but would something like this work in a derived column?

REPLACE("N/A", [yourfieldname] , NULL(DT_WSTR, 30) )

(adjust the string length to the current stringlength)