locked
get date from filename RRS feed

  • Question

  • Hello,
    using the foreach loop container I am looping through the .xls files in a directory.
    The excel files are as follows:

    Account data 1st December 2015.xls
    Account data 02nd December 2015.xls
    Account data 3rd December 2015.xls
    ...
    Account data 17th December 2015.xls
    ...
    Account data 23rd December 2015.xls
    ...

    using the foreach loop container I get the filename...
    using the script component, how can I getthe date and assign to a date variable?

    The idea is that when I get the date I can then use the date to have as the date column for the data when inserted into the database

    Thanks


    • Edited by arkiboys Friday, January 15, 2016 8:17 AM
    Friday, January 15, 2016 8:02 AM

Answers

  • Hi arkiboys,

    In a Script Task or Derived Column Transformation

    You will need to use the FINDSTRING or a Vb/C# equivalent for each textual month search and make it a number, then the same for the day, and month too (a quite lengthy expression) until you form a proper [ISO] date. Then you assign it to the variable.

    Searching online perhaps may help to build the exact expression.

    How to assign values to variables explained there: http://social.technet.microsoft.com/wiki/contents/articles/22194.use-ssis-variables-and-parameters-in-a-script-task.aspx


    Arthur

    MyBlog


    Twitter

    • Proposed as answer by Charlie Liao Monday, January 25, 2016 8:35 AM
    • Marked as answer by Charlie Liao Tuesday, January 26, 2016 9:30 AM
    Friday, January 15, 2016 7:59 PM
    • Proposed as answer by Charlie Liao Monday, January 25, 2016 8:35 AM
    • Marked as answer by Charlie Liao Tuesday, January 26, 2016 9:30 AM
    Friday, January 15, 2016 10:10 PM

All replies

  • Hi arkiboys,

    In a Script Task or Derived Column Transformation

    You will need to use the FINDSTRING or a Vb/C# equivalent for each textual month search and make it a number, then the same for the day, and month too (a quite lengthy expression) until you form a proper [ISO] date. Then you assign it to the variable.

    Searching online perhaps may help to build the exact expression.

    How to assign values to variables explained there: http://social.technet.microsoft.com/wiki/contents/articles/22194.use-ssis-variables-and-parameters-in-a-script-task.aspx


    Arthur

    MyBlog


    Twitter

    • Proposed as answer by Charlie Liao Monday, January 25, 2016 8:35 AM
    • Marked as answer by Charlie Liao Tuesday, January 26, 2016 9:30 AM
    Friday, January 15, 2016 7:59 PM
    • Proposed as answer by Charlie Liao Monday, January 25, 2016 8:35 AM
    • Marked as answer by Charlie Liao Tuesday, January 26, 2016 9:30 AM
    Friday, January 15, 2016 10:10 PM