• JustMarie (2/18/2009)


    OK - here's the short answer on how I did this.

    Create a Derived Column transformation, create a new column, and put this in the expression field. Use your own field name, obviously.

    (DT_DATE)(SUBSTRING([field],1,4) + "-" + SUBSTRING([field],5,2) + "-" + SUBSTRING([field],7,2))

    Personally I name the derived column as CONV_[field] just so I know it's a converted field and what field I converted. Use whatever naming convention you like.

    Use the derived column in your destination mapping.

    Longer answer.

    Make sure you have your source coming in as a [DT_STR] data type.

    Even longer answer.

    If you're setting this up for the first time and creating the table, here's the way I found it most convenient to get things correct in the long run.

    When creating your flat file data connection set the data type to [DT_DBTIMESTAMP] for all the fields that have the YYYYMMDD format. After you get all the fields properly formatted create an OLE DB destination and connect the two. Make sure you have your OLE DB connection manager in place. Select the connection manager and next to the field for 'Name of the table or the view' click on the New button. You'll get a window with the code to create the table.

    Personally, I copy this code and bring it over to the Management Studio and put it in a query window. Put in your schema, desired table name, add the values for your decimal fields, etc. Run the query to create the table.

    Now go back and edit the flat file connection manager. Change all the date fields to [DT_STR] and change the output column width value to match the input column width, as the value will default to 50 when you change the data type.

    Disconnect the source from the destination and then put your transformations in place. Convert the date values as above and when you finally do connect the destination change your mapping to the converted/derived column.

    Hope this helps.

    I just had to do this same function by stripping the YYYYMMDD value off a file name to use as a database value. Thank you so much for this solution. It worked perfectly!