Hi All, Need some advise and help. I have a table which has date columns defined as Int, as part of transformation process I need to populate fields into staging and do some data manipulation on them. The issue that I'm facing now is the metadata for the source column is Int(Datefield), when I get it through DB connector the datatype is assigned as DT_I4, I ausing a derived column and trying to convert this into Datetime using (DT_DBDate)[Col_Name]. There are no blank spaces or Null data in the source, for sure. I even tried converting the datatype to string and then to date (DT_DBDate)(DT_STR,20,1252)[Col_Name]. But still I get an error at persorfimg a type cast. I can do this transformation using SQL but still want to find as to why is this happening. The expression is > TRIM((DT_STR,20,1252)[Col_Name]) == "" || ISNULL([Col_Name])? NULL(DT_DBDATE): (DT_DBDATE)[Col_Name] another expression which i tried is > TRIM((DT_STR,20,1252)[Col_Name]) == "" || ISNULL([Col_Name])? NULL(DT_DBDATE): (DT_DBDATE)(DT_STR,20,1252)[Col_Name]. Please Help....
Is the data actually being stored as an int in the underlying table? Or just being picked up as an int in the data source's metadata? In the first case, you might be able to fix the problem by doing some maths in a derived column (depending on what the integer actually represents). In the latter case, you may be able to resolve it by using the advanced editor on the data source and changing the metadata there
You cannot convert integer data type directly to date type in SSIS. First you need to build a string and than convert it into an integer (unless you will use a `Script Component` and write a `C#` or `
VB.NET` code for the conversion. When converting from a string to `DT_DBDATE`, then the string has to be in format `yyyy-mm-dd`. So in general there are two approaches.. Construct the required string by mathematical operations: `(DT_DBDATE)((DT_WSTR,4)( [Col_Name] / 10000) + ''-'' + (DT_WSTR,2)( ([Col_Name] - [Col_Name]/10000*10000) / 100 ) + ''-'' + (DT_WSTR,2)( [Col_Name] - [Col_Name] / 100 * 100))` Or by by substrings from string representing the integer: `(DT_DBDATE)(LEFT((DT_WSTR,8) [Col_Name],4) + ''-'' + SUBSTRING( (DT_WSTR,8)[Col_Name] ,5, 2) + ''-'' + RIGHT((DT_WSTR,8)[Col_Name],2))` Of course, if you would like NULL Handling, add the condition for NULL. You can also see MSDN: [Cast (SSIS Expression)] :