none
convert data type DT_STR to DT_I8 and DT_STR to DT_DATE

Answers

  • Just to add a little on to this, the values do have to be convertible. For example, you can't cast an empty string to a number or a date. If you have empty values, you can use a Derived Column transform to check for an empty string, and cast to NULL.

     

     

    Code Snippet

    [YourColumnA]=="" ? NULL(DT_I8) : (DT_I8)[YourColumnA]

    [YourColumnB]=="" ? NULL(DT_DATE) : (DT_DATE)[YourColumnB]

     

     

     

    Monday, September 01, 2008 8:59 PM
    Moderator

All replies

  • Hi,

     

    i am using SSIS to convert data types.i have been trying this for long 2 weeks but i didnt get success.i did data conversion transformation and derived column transformation also but no use.still i am worry with this.tell me any body how to convert this

     

    thanks

    venki

    Thursday, August 28, 2008 6:37 AM
  • Hi Venki,

     

    You ought to be able to convert those easily using a Derived Column transform.

     

    DT_STR to DT_I8:

    Create a new column in a Derived Column transform, and use this as your expression: (DT_STR, 20, 1252)[YOUR_DT_I8_COLUMN_NAME_HERE]

     

    DT_STR to DT_DATE:

    The trick here is getting the string in the right format.  What has worked for me is the SQL standard date format of YYYY-MM-DD, so you simply need to transform your string column into that format before trying to cast it to a date type in a Derived Column transform.

     

    Hope that helps...

    Friday, August 29, 2008 11:32 PM
    Moderator
  • Just to add a little on to this, the values do have to be convertible. For example, you can't cast an empty string to a number or a date. If you have empty values, you can use a Derived Column transform to check for an empty string, and cast to NULL.

     

     

    Code Snippet

    [YourColumnA]=="" ? NULL(DT_I8) : (DT_I8)[YourColumnA]

    [YourColumnB]=="" ? NULL(DT_DATE) : (DT_DATE)[YourColumnB]

     

     

     

    Monday, September 01, 2008 8:59 PM
    Moderator
  • can you give me a more specific example?  I have a text file with a date in a text format 08/01/2008 but I need to convert it to a date format 2008/08/01 in my Data Transformation Service package.....thanks!

     

    Tuesday, September 09, 2008 5:09 PM
  • Use this as your expression:  ([TEXT_DATE] is used to represent your "date in a text format" column)

     

    Code Snippet
    ISNULL([TEXT_DATE]) ? NULL(DT_DATE) : (DT_DATE)(SUBSTRING([TEXT_DATE], 7, 4) + "-" + SUBSTRING([TEXT_DATE], 1, 2) + "-" + SUBSTRING([TEXT_DATE], 4, 2))

     

     

     

    Tuesday, September 09, 2008 5:22 PM
    Moderator
  • maybe I should have been more specific....I am trying to use DTS to bring a text file into a SQL table and I need the field that has the date as a text string to be transformed to a date value inthe SQL table.....so, I should do my transformation on this field as an Active X script and use this code?

     

    Tuesday, September 09, 2008 5:33 PM
  • Sorry - the above answer is intended for an SSIS Derived Column Transform expression.  If you're looking for assistance with DTS, I'd suggest starting another thread with that in the title so that those guys (not me) who know DTS will look at it.  If you don't do that, I'd think that since this is an SSIS forum, they'd assume it was SSIS-related.

     

    Tuesday, September 09, 2008 5:44 PM
    Moderator
  • my apologies!  don't know how I got here when I shoulda been there!  Smile

     

    Tuesday, September 09, 2008 5:47 PM