-1

I am exporting a flat file into a database that has year column (int data type). Flat file has string date(yyyy). Is there a way to do so using derived column in ssis or anywhere in ssis?

apariyar
  • 35
  • 2
  • 11
  • Are you trying to parse the year out of string column which is formatted as a complete date? i.e. '2014-08-15' – Mark Wojciechowicz Aug 15 '14 at 13:54
  • no I M NOT TRYING TO PARSE. what I am trying to do is load data from flat file with dob(like 1989 with string data type) to a database that has dob column but has int data type. – apariyar Aug 15 '14 at 16:09

1 Answers1

1

There are some ways to achieve the same goal.

In your Flat file source >Right click and go to Show advance Editor and Input and ouput properties>Change your datatype to varchar Drag the Derived column after the source flat file and change varchar to int OR You can do it in Data Conversion transformation as well.

Maverick
  • 1,167
  • 1
  • 8
  • 16
  • I am not sure what you mean by change to varchar datatype. If I m not mistaken we don't have varchar datatype in input and output properties. We have Unicode string , string and text stream [dt_text] I don't think u meant any of thse. Besides, we have to make sure external column data type and output column datatype are same. – apariyar Aug 15 '14 at 16:17
  • It is same as DT_STR (string) in the output properties. – Maverick Aug 15 '14 at 16:59
  • Yes I did that before, but I still get error like this. [Derived Column [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Derived Column" failed because error code 0xC0049064 occurred, and the error row disposition on "Derived Column.Outputs[Derived Column Output].Columns[Year]" specifies failure on error. ... in my derived column I have this expression->(DT_UI4)Year – apariyar Aug 15 '14 at 17:07
  • Try to see what datatype is coming from source in Derived column by doing the same go to advance editor of Derived column if not then you can use data conversion transformation. – Maverick Aug 15 '14 at 17:12
  • well it is DT_STR. let me try using data conversion – apariyar Aug 15 '14 at 17:17
  • wat do u suggest to change data type to . I tried four-bye singed integer, numeric. In both cases, I get this error: " [Data Conversion [2]] Error: Data conversion failed while converting column "Year" (110) to column "Copy of Year" (6). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data." – apariyar Aug 15 '14 at 17:23
  • This means you have some null in the Column that you have to give certain value inorder to proceed. – Maverick Aug 15 '14 at 17:26
  • wouldn't checking "retain null values from the sources as null values in the data flow" take care of it? – apariyar Aug 15 '14 at 17:33
  • yeap there are empty strings.. so this where the error is coming from. I m not sure how to handle this. – apariyar Aug 15 '14 at 18:01
  • Ya, if there are empty record you have to feed with some data so that you can achieve the goal with the above listed procedure. – Maverick Aug 15 '14 at 19:09