asked
SSIS 2005 Flat File Connection Manager
Hi ya'll! I am reading several flat files, using the flat file connection manager as the source, into an SSIS package in order to load them into a table. 2 of these files contain date columns. When I configure the columns on the advanced tab I use date{DT_DATE]. The date column in file 1 always contains a date and loads without issue. The date column in file 2 will contain blanks or a date and is always erroring out with a "Cannot Convert" issue. This error happens within the Flat file manager reading the file, it does not reach the destination task. I can always read it as a string, but I was hoping there was something I could do to fix the error instead of using an alternative. And Ideas? Thanks!
You could use a derived column transformation to check whether the field is empty or null, and return a NULL value of type DT_DATE. Your expression will look something like this: ISNULL(field) || field == "" ? NULL(DT_DATE) : (DT_DATE)field
Yeah, if the data are not directly convertible into the DT_Date, then the data flow will fail. In that case as @Martin Schoo... suggested, ou can do the data type check and conversion in derived column transformation. But in that case you have to set the data type of the flat file column to a DT_STR or DT_WSTR so it can be red without error.
